Bikle.com

Ajax Demo:

Plain HTML
(no Ajax here):

of URLs:

OracleInstall.html Dan's Oracle Install Demo History of this file: 07-03-2000 bikle Creation 03-14-2001 bikle Enhanced discussion of directory structure 03-16-2001 bikle Fixed some typos 03-17-2001 bikle Added some Linux content 03-18-2001 bikle Enhanced section on rc scripts 03-18-2001 bikle Added thoughts about installing from existing tree 04-07-2001 bikle Fixed typos, enhanced sentences 04-07-2001 bikle Derived OracleInstall.html from OracleInstall.txt 04-20-2001 bikle Enhanced discussion about the init.ora in the section about installing from an existing tree. 12-07-2001 bikle Replaced oradoc.photo.net with otn.oracle.com/docs/ 12-07-2001 bikle Added a title with bold tags and fixed some typos 12-07-2001 bikle Added Introduction ----------------------------------------------------------------------------- Introduction ------------- Dan refers to this page often. It contains many pieces of useful syntax he uses whenever he is given the task of creating a new database. The structure of this page is simple until it passes the point of generic database creation. After that, the reader could easily get bogged down in the discussion about how Dan tries moving a bunch of data from a source database to the newly created database. But this is the stuff of real DBA life. If the networks ever replaced the show "Cops" with "DBAs", the producer would be well advised to study my struggles with the Oracle Import utility below. Summary ------- Installation of Oracle Software -Obtaining the Software (cdrom image) -Obtaining the Installation Documentation -UNIX Preparation -Disk Drive Preparation -File System and Directory Naming Conventions -Creation of the Oracle Account -Adding Users to Privileged Groups -NIS Notes -Directory Structure -Env Variables -Preparation of the Installation Display (X Windows on a PC) -Running the installer -Rc start scripts (Oracle start scripts triggered by machine boot) -Installing from an existing tree -Demo of EXPORT/IMPORT Details ------- Installation of Oracle Software ------------------------------- Obtaining the Software (cdrom image) ------------------------------------ We may be able to obtain the software from technet.oracle.com. This site requires that we register. Usually technet.oracle.com only contains newer versions of Oracle software. If we want an older copy of Oracle software, we may need to obtain it from Oracle Support. Obtaining the Installation Documentation We obtain the documentation from technet.oracle.com. This site requires that we register. It's usually a good idea to download as much of the documentation as possible (in the form of .pdf files) and place it in a shared drive accessible from the entire network. If we have access to the internet, a large amount of generic Oracle documentation (in the form of .html files rather than .pdf files) is available here: http://otn.oracle.com/docs/ UNIX Preparation ---------------- To prepare UNIX for Oracle installation we need to enable some System V parameters related to semmaphores and shared memory. Some values for these parameters which will work on most machines are listed below: On Solaris, we'd have something like this (placed in /etc/system): set shmsys:shminfo_shmmax=2097152000 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=10 set semsys:seminfo_semmns=2000 set semsys:seminfo_semmni=100 set semsys:seminfo_semmsl=200 On Linux, we use the /sbin/sysctl utility to set various System V parameters: bash# /sbin/sysctl -a|grep sem bash# /sbin/sysctl -a|grep shm kernel.shmall = 4194304 kernel.shmmax = 38123123 bash# /sbin/sysctl -w kernel.shmmax = 38123124 error: 'kernel.shmmax' must be of the form name=value error: Malformed setting '=' error: '38123124' must be of the form name=value bash# /sbin/sysctl -w kernel.shmmax=38123124 kernel.shmmax = 38123124 bash# /sbin/sysctl -a|grep shm kernel.shmall = 4194304 kernel.shmmax = 38123124 bash# The above screen dump was generated after consulting with an experienced Linux geek. The Oracle documentation offers this advice: LinuxKernel.txt HP is a little like Linux in that we are free to use a utility (named SAM) to change the values of the System V parameters. On HP, we'd have something like this: shmmax=2097152000 shmmin=1 shmmni=100 shmseg=10 semmns=2000 semmni=100 semmsl=200 Descriptions of the parameters are listed below: shmmax determines the maximum size of a shared memory segment shmmin determines an unknown attribute; check google.com for more info shmmni determines the number of shared memory identifiers shmseg determines the number of shared memory segments per process semmns determines the Maximum number of Semaphores semmni determines the number of Semaphore identifiers semmsl determines the number of maximum number of semaphores allowed per set See this url for more discussion: http://lab.dce.harvard.edu/extension/cscie256/manuals/Shared_mem_and_Semeplhores.htm In addition to the system V parameters we need to make sure that UNIX is patched to a level which is deemed supportable by Oracle Support. Information about required or suggested UNIX patches is usually placed in the release notes which come with the software. On hp/ux we may see which patches are installed by using the shell command: /usr/sbin/swinstall -p On solaris we get information about installed patches from the "showrev -p" command. Disk Drive Preparation ---------------------- The main idea behind disk drive preparation for Oracle is to provide file systems for the three types of files. First, we need to provide a file system for the Oracle software. This may be a general purpose file system normally used to hold a large number of small files none of which support large amounts of I/O. Second, we need to provide a file system (or several file systems) for the Oracle data files. This second type of file system needs to be optimized for supporting a small number of large files (sized between 100mb and 2000mb) which support large amounts of random I/O equally divided between reads and writes. The best physical structure underlying this second type of file system, would be a logical volume made from stripes across multiple disk drives where each stripe is 8k or 16k or 32k or 64k wide. A good mirroring strategy would be to use disk drives to mirror individual disk drives rather than using stripes to mirror stripes. Third, we need to provide a file system (or several file systems) for the Oracle redolog files. This third type of file system needs to be optimized for a small number of medium sized files (sized at about 10mb) which support large amounts of sequential writes. Logical volumes built from 64k wide stripes across several disk drives works well for this. If we wish to trade off some performance to maximize uptime, we would use disk drives to mirror individual disk drives in this configuration. When we are finished creating the file systems they should look something like this: barker:/etc% df -k Filesystem 1k-blocks Used Available Use% Mounted on /dev/md0 496677 51006 420027 11% / /dev/md1 3024560 11536 2859380 0% /var /dev/md2 4957772 785560 3920368 17% /usr /dev/md3 105013488 1022340 98656772 1% /oracle /dev/sdd1 61218756 2036292 56072684 4% /u01 /dev/sdc1 34977412 30120 33170508 0% /u02 /dev/sde1 34977412 30120 33170508 0% /u03 /dev/sdf1 34977412 30120 33170508 0% /u04 spock:/home 141557760 106955752 34522488 76% /home barker:/etc% Env Variables ------------- Early in our effort to install Oracle, we need to set our env variables. Perhaps the most important variable is ORACLE_SID. It is wise to create a file in /oracle named such that ORACLE_SID is in the file name. For example: /oracle/.BVWW An example of how .BVWW might look is displayed below: bvww.txt Once the above file has been created, we also find it convenient to have another file which may be used to spawn our favorite interactive shell with the proper env variables in it. For example, if we like working with the csh as our interactive shell, we'd write this file: bvwwcsh.txt Notice that the above script is very simple. Remember to make it executable. It might be a good idea to leave .BVWW un-executable so we avoid the scenario of a neophyte running .BVWW and then thinking that env variables have been set in the current working shell. It would be better that the neophyte receive an error: "permission denied." File System and Directory Naming Conventions --------------------------------------------- The naming convention we should use for file systems used by Oracle is simple: /uxy When we look at the filesystem example above we see five file systems have been created for Oracle: /oracle, /u01, /u02, /u03, /u04 If we need to add another file system to the above set we would adhere to the naming convention by naming the file system /u05. The naming conventions we should use for directories used by Oracle are explained below. Oracle processes use environment variables and init.ora parameters to keep track of where software files and configuration files reside. Naming conventions are mostly a convenience for the Oracle DBA. With this in mind, we rely upon a table to illustrate directory naming conventions. The three column table below describes directory naming conventions and how they relate to environment variables and init.ora parameters: Env Var Directory Description ------------------------------------------------------------------------------------------------ $ORACLE_BASE /oracle/app/oracle Anything related to Oracle goes under this dir Notice how ORACLE_HOME is derived from ORACLE_BASE: $ORACLE_HOME /oracle/app/oracle/product/8.1.6 Version 8.1.6 goes under this dir $ORACLE_HOME /oracle/app/oracle/product/8.1.7 Version 8.1.7 goes under this dir $ORACLE_SID BVWW This identifies a database Some DBAs believe that ORACLE_HOME is best derived from ORACLE_BASE and ORACLE_SID (to minimize broken dependencies on ORACLE_HOME after upgrades): $ORACLE_HOME /oracle/app/oracle/product/BVWW Location of BVWW software If we derive ORACLE_HOME from ORACLE_BASE and ORACLE_SID, it would be a good idea to make ORACLE_HOME to be a symbolic link such that when we look at it, it tells us two things: -The version of Oracle corresponding to this ORACLE_HOME -The ORACLE_SID corresponding to this ORACLE_HOME This means that the software would reside in a directory named something like: /oracle/app/oracle/product/8.1.6. Once the software is placed there, we'd issue the following shell command to create a virtual directory corresponding to $ORACLE_HOME: cd /oracle/app/oracle/product/; ln -s 8.1.6 BVWW Notice how the directories below are derived from a combination of ORACLE_BASE and ORACLE_SID: Init.ora Parameter Directory Description ------------------------------------------------------------------------------------------------- audit_file_dest /oracle/app/oracle/admin/BVWW/udump Oracle places some audit log files here background_dump_dest /oracle/app/oracle/admin/BVWW/bdump Oracle places some log files here core_dump_dest /oracle/app/oracle/admin/BVWW/bdump Oracle places some core files here It's possible we'd want to break the naming convention for log_archive_dest_1 so that we could specify directories in a file system which have been setup to support files which receive heavy sequential write activity: Init.ora Parameter Directory Description ------------------------------------------------------------------------------------------------- log_archive_dest_1 /oracle/app/oracle/admin/BVWW/arch Oracle places archived redo logs here (The parameter appears like this in the init.ora: log_archive_dest_1 = "location=/oracle/data/BVWW/arch") For example: Init.ora Parameter Directory Description ------------------------------------------------------------------------------------------------- log_archive_dest_1 /u04/data/BVWW/arch Oracle places archived redo logs here We round out the discussion about directory naming conventions for parameters with this last entry: Init.ora Parameter Directory Description ------------------------------------------------------------------------------------------------- user_dump_dest /oracle/app/oracle/admin/BVWW/udump Oracle places developer's trace files here Next, we discuss the naming convention for Oracle control files: Init.ora Parameter Directory Description ------------------------------------------------------------------------------------------------- control_files /u02/data/BVWW/control01.ctl This is an example of a control file Notice how the above table entry makes use of the /uxy naming convention coupled with the ORACLE_SID. Also notice how the control file makes use of a .ctl suffix. Next, we discuss the naming convention for Oracle data files: File Type Directory Description ------------------------------------------------------------------------------------------------- data files /u02/data/BVWW/system01.dbf This is an example of a data file Next, we discuss the naming convention for Oracle redolog files: File Type Directory Description ------------------------------------------------------------------------------------------------- redolog files /u02/data/BVWW/redoBVWW01.log This is an example of a redolog file We finish the discussion about file naming conventions by noting that control files, data files, and redolog files are all named the same way but with different suffixes. When we have finished deciding on the names of our directories we will need, we would issue a series of shell commands which would look something like this: /bin/mkdir /oracle/bin /oracle/dba /oracle/exp /oracle/doc /oracle/logs /bin/mkdir /oracle/sql /oracle/tmp /oracle/bik /bin/mkdir /oracle/app /oracle/app/oracle /oracle/app/oracle/product /bin/mkdir /oracle/app/oracle/product/8.1.7 cd /oracle/app/oracle/product/; ln -s 8.1.7 BVWW /bin/mkdir /oracle/app/oracle/admin/ /oracle/app/oracle/admin/BVWW/ /bin/mkdir /oracle/app/oracle/admin/BVWW/audit /bin/mkdir /oracle/app/oracle/admin/BVWW/bdump /bin/mkdir /oracle/app/oracle/admin/BVWW/cdump /bin/mkdir /oracle/app/oracle/admin/BVWW/udump /bin/mkdir /oracle/app/oracle/admin/BVWW/arch /bin/mkdir /oracle/app/oracle/admin/BVWW/create /bin/mkdir /oracle/app/oracle/admin/BVWW/logs /bin/mkdir /oracle/app/oracle/admin/BVWW/pfile /bin/mkdir /u01/data/ /u01/data/BVWW/ /bin/mkdir /u02/data/ /u02/data/BVWW/ /bin/mkdir /u03/data/ /u03/data/BVWW/ /bin/mkdir /u04/data/ /u04/data/BVWW/ After we create the oracle account (which is described below) we issue the following chown commands. If the account already exists, run the commands now: /bin/chown oracle /oracle/bin /oracle/dba /oracle/exp /oracle/doc /oracle/logs /bin/chown oracle /oracle/sql /oracle/tmp /oracle/bik /bin/chown oracle /oracle /oraclel/app /oracle/app/oracle /oracle/app/oracle/product /bin/chown oracle /oracle/app/oracle/product/8.1.7 /bin/chown oracle /oracle/app/oracle/admin/ /oracle/app/oracle/admin/BVWW/ /bin/chown oracle /oracle/app/oracle/admin/BVWW/audit /bin/chown oracle /oracle/app/oracle/admin/BVWW/bdump /bin/chown oracle /oracle/app/oracle/admin/BVWW/cdump /bin/chown oracle /oracle/app/oracle/admin/BVWW/udump /bin/chown oracle /oracle/app/oracle/admin/BVWW/arch /bin/chown oracle /oracle/app/oracle/admin/BVWW/create /bin/chown oracle /oracle/app/oracle/admin/BVWW/logs /bin/chown oracle /oracle/app/oracle/admin/BVWW/pfile /bin/chown oracle /u01/data/ /u01/data/BVWW/ /bin/chown oracle /u02/data/ /u02/data/BVWW/ /bin/chown oracle /u03/data/ /u03/data/BVWW/ /bin/chown oracle /u04/data/ /u04/data/BVWW/ Creation of the Oracle Account ------------------------------ Creation of the Oracle Account is a simple task. First we create a group for the Oracle account. Typically the group is named "dba". On HPUX we should use SAM to create the group; on Solaris we could use vi to directly edit /etc/group (Solaris provides a utility named /usr/sbin/groupadd for those so inclined). A copy of /etc/group is displayed below: root::0:root other::1:root,hpdb bin::2:root,bin sys::3:root,uucp adm::4:root,adm daemon::5:root,daemon mail::6:root lp::7:root,lp tty::10: nuucp::11:nuucp users::20:root nogroup:*:-2: opcgrp::77: dba::101: bv1to1::102: After the group is created; we are free to create the Oracle account. Typically the name of the Oracle account is "oracle". On HPUX we should use SAM to create the Oracle account. On Solaris (and most other UNIX variants) we could use vipw to directly edit the /etc/passwd file to create the Oracle account entry. For those on Solaris who prefer to add entries to /etc/passwd via a utility, they should use the /usr/sbin/useradd command (which is considered by some to be more cumbersome than vipw). For brave souls who want to edit the /etc/passwd file by use of a favorite text editor (vi probably), Solaris places no constraint on this action. In fact Solaris provides a utility named "pwconv" which works to ensure that changes made to /etc/passwd by via are transferred in an appropriate way to /etc/shadow. A copy of an HPUX /etc/passwd file is displayed below: root:8rU5eC1BmeYIk:0:3::/:/sbin/sh daemon:*:1:5::/:/sbin/sh bin:*:2:2::/usr/bin:/sbin/sh sys:*:3:3::/: adm:*:4:4::/var/adm:/sbin/sh uucp:*:5:3::/var/spool/uucppublic:/usr/lbin/uucp/uucico lp:*:9:7::/var/spool/lp:/sbin/sh nuucp:*:11:11::/var/spool/uucppublic:/usr/lbin/uucp/uucico hpdb:*:27:1:ALLBASE:/:/sbin/sh nobody:*:-2:-2::/: www:*:30:1::/: opc_op:*:777:77:OpC default operator:/home/opc_op:/usr/bin/ksh oracle:hI40CDK33RZFQ:101:101:,,,:/oracle:/usr/bin/ksh Adding Users to Privileged Groups --------------------------------- Adding a user to the dba group is easy. On HPUX, use SAM. On Solaris, use vi to edit /etc/group. For example if we wanted to add root to the dba group we'd change: dba::101: to dba::101:root Then, if we wanted to add hpdb to the group we'd change it to this: dba::101:root,hpdb NIS Notes --------- Some DBAs prefer to have both the Oracle user and Oracle group defined within NIS rather than in the local files /etc/passwd and /etc/group. a good book on NIS is listed below: Managing NFS and NIS Author: Hal Stern Publisher: O'Reilly & Associates, Inc. For the purposes of this paper we will attempt to supply a small amount of information about defining both the Oracle user and Oracle group within NIS. A few simplistic thoughts about NIS are discussed below. A machine running NIS is called an NIS client. If a machine is running NIS, a process named ypbind will appear in a process listing: niles:/oracle% ps -ef|grep yp root 144 1 0 Feb 01 ? 0:00 /usr/lib/netsvc/yp/ypbind niles:/oracle% Also, a command named ypcat will return data rather than an error: niles:/oracle% ypcat group staff:*:10: other:*:20: dudes:*:100:ricardo,dan,root On NIS clients, rather than placing the entries in the files /etc/group and /etc/passwd, the DBA needs to place entries in NIS "maps": passwd group These maps are managed on a single machine called the "Master Server". A sample script for adding an entry to the group map is displayed below; remember, this script must be run on the Master Server: echo 'dbax:*:100:ricardo,dan,root' >> /etc/group cd /var/yp make Notice that the above command set assumes the group map resides in the file /etc/group on the master server; be aware, not all master servers keep their group map in /etc/group. To find out the name of the Master Server, run the command listed below on any NIS client: ypwhich -m At most sites, administration of NIS is tightly controlled by an NIS Administrator. In that case we send a note to the Administrator: sally.txt Directory Structure ------------------- The main top directory for the oracle scripts is /oracle. Below this top directory are the directories: bin/, dba/, exp/, doc/, logs/, misc/, sql/, and tmp/. The bin/ directory is primarily the repository for shell scripts but it would be acceptable to put binary executables there also. The dba/ directory is primarily the repository for some configuration files and crontab files. Also the DBA may put some scripts there but most scripts belong in either bin/ or sql/. The exp/ directory is the repository for Oracle export files. The doc/ directory contains documentation of interest to the DBA (such as the file you are now reading). The misc/ directory contains miscellaneous files which we deem too important to put in the tmp/ directory. The logs/ directory contains logs of activity generated by scripts. The sql/ directory contains sql scripts used for both interactive work and cron jobs. The tmp/ directory contains temporary files which we may want to keep around for a week or so. Preparation of the Installation Display (X Windows on a PC) ----------------------------------------------------------- When Oracle.com brought 8.1.5 to market, they forced users to run the Oracle installer from an X-Windows interface. X-Windows is a standard interface on a UNIX or Linux workstation, so if we are at a UNIX or Linux workstation we should have no interface problems. The problem is, UNIX or Linux workstations are less common than PCs.; most people work at a PC running Windows or NT. In order to interface with the Oracle 8.1.x installer from a PC, we need to install and run X-Windows on the PC. We may download an evaluation PC X server from the following url: http://www.starnet.com Once we have the X server installed on the PC, we click on it to make it active. Now, we follow this recipe to display the Oracle 8.1.x installer interface on the PC: -Start the X server by clicking it's icon. -We make a note of the ip address of the PC (dos command ipconfig tells us this info). -We use a terminal emulator to connect to the the UNIX server which we intend to install Oracle on. -From the UNIX shell we set an environment variable which tells the shell where we are (or more precisely what our ip address is). For example from the C shell: setenv DISPLAY 192.168.255.31:0 -We test the X connection by typing the command "xterm" at the UNIX shell. (an xterm should appear on the PC screen) -We then find and run the UNIX executable which corresponds to Oracle installer. -The installer interface should appear on the PC screen. Running the installer --------------------- Running the installer can be tricky for the first time user. Once we have the interface issues worked out, it's a good idea to complete these tasks before starting the installer. -Login to the Oracle account -Ensure the installation cdrom is mounted (or we have an image copy on disk) -Set our environment variables (for example, run BVWW.csh) -Double check that we have all the directories created and chowned to oracle -cd to the cdrom moint point If we are installing 8.1.x, we look for a shell script named, "runInstaller" which should be located in the top level directory of the cdrom. We will avoid discussion about the runInstaller utility. Oracle has made big changes to it's installer everytime it has come out with a major new release. One trend which they will probably continue is the inclusion of html based documentation with the installation cdrom. With that in mind, we will dodge the topic of interaction with the runInstaller utility and refer the reader to the html documentation on the cdrom. We will discuss the mechanics of getting the cdrom mounted. On Solaris, mounting the cdrom is a point and click affair where the DBA does it from the filemgr utility in Solaris. On HP-UX it is a simple multi-step process. To mount the cdrom we'd assume we need to pick an appropriate option from within SAM; this is not the case. Mounting the cdrom has proven to be a two-step process. First, we run the shell command: /usr/sbin/ioscan -fnkC disk From the resulting output we determine the name of the device file which corresponds to the cdrom. For example, it might be named something like this device file name: /dev/dsk/c3t2d0 Second, after we find the device file name which corresponds the cdrom, we issue a simple mount command. For example we might run a command like the command displayed below: /usr/sbin/mount /dev/dsk/c3t2d0 /cdrom Once we get the cdrom mounted, it might be a good idea to make a copy of it and place the copy on disk as a backup. A standard which works well is to establish a softlink in the Oracle account named cdrom which points to the directory (which should be in a file system with a lot of space) which we deem to be the container of cdrom images. Another obvious idea to try if we are having problems getting the cdrom mounted is to mount it on a windows based system, winzip it into a large .zip file, copy the .zip file to UNIX land and then unzip it. This technique usually works when the target UNIX is Solaris. We've had problems getting this to work on HPUX with Oracle 8.0.5; perhaps the idea would work okay with an HPUX-Oracle 8.1.x cdrom. Now would be a good time to say that we need to create the oratab file. Use the shell commands below (run from root) to create the oratab file: touch /etc/oratab chown oracle /etc/oratab On some UNIX variants (Solaris most certainly) we'd run these commands: touch /var/opt/oracle/oratab chown oracle /var/opt/oracle/oratab The Oracle installer may want to write to the oratab file. Rc start scripts (Oracle start scripts triggered by machine boot) ----------------------------------------------------------------- After the installer is done installing Oracle, we need to setup the machine so that Oracle is automatically started by UNIX when the machine reboots. Some instructions for doing this on HPUX are displayed below. First, we create a file named /etc/rc.config.d/oracle and put this single line in it: START_ORACLE=1 Next, we create a file named /sbin/init.d/oracle and we put this syntax in it: hpinitOracle.txt After the file is created, we issue these shell commands: chown bin /sbin/init.d/oracle chgrp bin /sbin/init.d/oracle chmod 555 /sbin/init.d/oracle cd /sbin/rc2.d/; ln -s /sbin/init.d/oracle S999dbora cd /sbin/rc2.d/; ln -s /sbin/init.d/oracle K999dbora Next, we check /etc/oratab contains a flag to specify Oracle startup upon boot up. For example, if we inspect the entry below we see that it contains a flag set=Y (the flag is in the third colon delimited field) to specify Oracle startup upon boot up: BVWW:/oracle/app/oracle/product/8.1.6:Y When, the machine is rebooted we may check that Oracle has been brought up by checking the process listing or attempting to login to sqlplus. Also the Oracle startup will be logged in /etc/rc.log and should look something like this: rclog.txt When we look at the above log file we see that it complains, "Oracle possibly left running when system went down (system crash?)." This problem is the result of a bug in a shell script distributed by Oracle. The script $ORACLE_HOME/bin/dbshut contains the lines: connect internal shutdown The script should be changed so the lines read: connect internal shutdown immediate Oracle RC scripts on Solaris ----------------------------- Setting up RC scripts on Solaris is simpler than doing it on HPUX. First we place a script named oracle in the directory /etc/init.d: solinit.txt Notice how the above script refers to the directory which acts as what we call the software home (ORA_HOME) rather than any specific ORACLE_HOME. The thought behind this is the assumption that a software home has more permanence than any specific ORACLE_HOME. This may be an invalid assumption; the converse might be true: a particular ORACLE_HOME may have more permanence than any specific software tree. If a particular ORACLE_HOME has more permanence than any specific software tree, we might change the above line which defines ORA_HOME to something like this: ORA_HOME=/oracle/app/oracle/product/B0B This means that if the B0B database gets upgraded every six months yet resides on the same host, then the above script will continue working. We could enhance the script by putting this line in it in an appropriate way: echo "Usage: /etc/init.d/oracle { start | stop }" After we place the above file in /etc/init.d we should chown it to oracle and make it executable: /bin/chown oracle /etc/init.d/oracle /bin/chmod 744 /etc/init.d/oracle We chown it to oracle as a convenience to the DBA; it will function properly if owned by root. Next we issue the shell commands listed below as final steps towards setting up the Oracle rc scripts on Solaris: cd /etc/rc3.d ln -s ../init.d/oracle S9oracle ln -s ../init.d/oracle K9oracle Oracle RC scripts on Linux -------------------------- To install Oracle RC scripts on Linux we follow the instructions written above for Solaris. The only difference is that the directory init.d/ on Linux resides here: /etc/rc.d/init.d/ Installing from an existing tree --------------------------------- When we install from an existing tree, some of the work has been done for us already. Some of the steps we follow are the same or very similar to the steps which have been discussed up to this point. Therefore, we will summarize some of them. Also to further simplify this section we will assume the resulting database will have ORACLE_SID=W12. So, the reader should see "W12" has a variable which should be replaced with the ORACLE_SID of his/her choice. -Create a file named /oracle/.W12 w12env.txt -Create a file named /oracle/W12.csh: w12csh.txt -Create initW12.ora and put it someplace where we can access it later: initw12ora.txt When we are creating the above init.ora file we see that it depends on a directory structure. -Create W12 directory structure Some example mkdir commands are displayed below; run them from oracle account: exmkdir.txt Once we have the directory structure in place, we may now maneuver the init.ora file into place. The Oracle kernel will look for the init.ora in this location: $ORACLE_HOME/dbs/init$ORACLE_SID.ora So that is where I put it. Some DBAs like to put the init.ora here: $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora and then link it to the location where the Oracle kernel is looking for it. In my opinion, this does not buy me much so I avoid doing it. -Create W12 CREATE DATABASE script An example CREATE DATABASE script is displayed below. We should place the script here: /oracle/app/oracle/admin/W12/create/crdbW12.sql: crdbw12.txt After the above script runs successfully, we can create a bunch more database objects. The script below will create objects which are considered to be mostly generic for an 8.1.7 database. Feel free, however, to change their sizes if you are so inclined: genericw12.txt After we run the above script, we are in a good position to run a variety of Oracle supplied scripts which create a huge assortment of necessary objects in the Oracle data dictionary: crddict.txt Demo of EXPORT/IMPORT --------------------- Once the above script has finished running, we have an "empty" database. So obviously we want to put some data in it. If we have a bunch of data in another database we are free to use the Oracle EXPORT/IMPORT utilities to copy the data into our empty database. One recipe we follow is displayed below; keep in mind that both EXPORT and IMPORT support a rich variety of options. These utilities are well suited for copying a subset of objects from one database to another database. If, however, you want to copy the contents of an entire database to another database, EXPORT/IMPORT are not ideally suited for this. To copy an entire database we would be better off to shutdown the source database, copy it's data files to the target host, create a control file if necessary, and then start up both the source database and target database. Many details are involved but that is the gist of it. We digress; let's get back to our discussion of EXPORT/IMPORT. Our recipe to copy some of the tables owned by HOGMGR in the ZX database into the HOGMGR account on the W12 database is displayed below: -Run a FULL-ROWS=NO export on the ZX host -Run a USER-LEVEL-HOGMGR-ROWS=NO export on the ZX host -Run a series of TABLE-LEVEL-ROWS=YES exports on the ZX host -Copy all of the above export files to the W12 host -Query the ZX data dictionary to obtain a list of tablespaces which HOGMGR depends upon -Use grep to obtain useful syntax associated with HOGMGR tablespace creation -Transform the syntax obtained above into W12 appropriate syntax -Create HOGMGR tablespaces using the syntax created above -Run a USER-LEVEL-HOGMGR-ROWS=NO import on the W12 host as a way to locate HOGMGR dependencies -React to any errors generated by above import -Run a series of TABLE-LEVEL-ROWS=YES imports on the W12 host We elaborate on some of the above tasks below: Run a FULL-ROWS=NO export on the ZX host ---------------------------------------- The screen dump below demonstrates how to run a FULL-ROWS=NO export on the ZX host. expzxrn.txt Run a USER-LEVEL-HOGMGR-ROWS=NO export on the ZX host ----------------------------------------------------- The screen dump below demonstrates how to run a USER-LEVEL-HOGMGR-ROWS=NO export on the ZX host: expusrlvlrn.txt Run a series of TABLE-LEVEL-ROWS=YES exports on the ZX host ----------------------------------------------------------- To run a single TABLE-LEVEL-ROWS=YES export on the ZX host we'd issue a command line like this: $ORACLE_HOME/bin/exp USERID=/ BUFFER=123456 FILE=someTable.dmp TABLES=someTable We've built a wrapper script which works with the above command line and compresses the resulting export file "on the fly". ROWS=YES export files can be large; it is a good idea to compress them: exptabwr.txt Once we place the above script in a convenient location we create another script which calls it. The sql syntax below helps us get started: sqlhogmgr.txt Once we run the above syntax we have most of the shell script created. We add a few lines and comments and we are done: expdonescr.txt When we run the above script we end up with a directory full of export files and corresponding export-log files: dirfulwile.txt Copy all of the above export files to the W12 host -------------------------------------------------- This step is simple; use whatever works: ftp, rcp, scp. Query the ZX data dictionary to obtain a list of tablespaces which HOGMGR depends upon ----------------------------------------------------------------------------------------- The syntax below will help us with this step: SELECT SUM(BYTES)/1024/1024 MB , TABLESPACE_NAME FROM DBA_EXTENTS WHERE OWNER='HOGMGR' GROUP BY TABLESPACE_NAME; We demonstrate its use below: demoabove.txt Use grep to obtain useful syntax associated with HOGMGR tablespace creation ---------------------------------------------------------------------------- We demonstrate use of a simple one line grep-sed filter to obtain useful syntax associated with HOGMGR tablespace creation below. Be aware that the technique below works on most UNIX variants but it fails on some Linux versions due to a bug in the Linux implementation of grep (which we may work-around through the addition of a strings filter): grepexp.txt Transform the syntax obtained above into W12 appropriate syntax --------------------------------------------------------------- This is a simple step; we take some of the above commands generated from the grep-sed filter and work with them in a file resulting in the script displayed below: crsome.txt Create HOGMGR tablespaces using the syntax created above --------------------------------------------------------- This is a simple step; we just run the above script. We display a screen dump below: sdCrsomets.txt Run a USER-LEVEL-HOGMGR-ROWS=NO import on the W12 host as a way to locate HOGMGR dependencies --------------------------------------------------------------------------------------------- Running export is usually easy; we just put together a suitable command line, run df to check for disk space, hit return, wait a few minutes (or several hours) and then note the location of the resulting export file. On the other hand, running import requires more knowledge and thought since we are loading objects and data into the database rather than just sucking data out of it. Running import can be tricky. Before you run import, you need to know what objects reside in your export file, and you need to decide where they will reside in the target database and who will own them. Also you need to decide if you want all the objects in the export file to be imported or just a subset of them. Finally, you need to think about dependencies before you run the import so that imported objects will see objects that they depend on while they are loaded. This goes without saying that you want to avoid importing objects which already exist, but we said it anyway. It's for these reasons that the EXPORT/IMPORT utilities are not well suited for copying an entire database. The full database EXPORT usually goes easily; the IMPORT almost always generates a ton of error messages and the DBA needs to analyze and react to every one. If we want to copy an entire database, a better approach is to create a copy database from a hot or cold backup of the datafiles of the source database. For this demo, we are only interested in copying "most" of the objects owned by HOGMGR in the ZX database to the W12 database. By "most" we mean every object owned by HOGMGR except some of the large tables. The EXPORT/IMPORT utilities (in the hands of an experienced DBA) are ideally suited for this task. The main dependencies which come into play during this demo are displayed below: -The tables we want to import will need to see "appropriate" tablespaces -The tables we want to import will need to see a user named HOGMGR -HOGMGR will need write permission on "appropriate" tablespaces The first dependency was probably addressed when we greped CREATE TABLESPACE syntax out of the FULL-ROWS=NO export file (full.ZX.nr.dmp) and used it as a basis to write and then run the script crSomeTspaces.sql. We will find out soon enough. The second and third dependencies will be fulfilled, again, by relying upon grep to pull some syntax out of full.ZX.nr.dmp. The screen dump below demonstrates this idea (again, be aware this will fail on Linux): grepCRusr.txt Using the above syntax, we create a script to fulfill the second and third dependencies: crHog.txt When we run the script we see this: seeCrHog.txt Now we are probably ready to run a USER-LEVEL-HOGMGR-ROWS=NO IMPORT: impHogh.txt Notice how we used "imp help=y" to get our mind to wrap around the many command line options which give IMPORT its rich functionality. We scratch our chin and build a command line and then run it: buildCmd.txt React to any errors generated by above import --------------------------------------------- We sure did get a snoot full of errors. We will do this: -Think about each error -Propose a fix for each error -Implement error fixes Let's think about some of the errors below: e1.txt We got the above error because we forgot to create the OPS$ORACLE account. The syntax below will fix this: e2.txt Notice that we worked around it by giving a user/pass combo to IMPORT for another DBA account (system/manager). The next error which catches our eye is displayed below: e3.txt We could grep the READONLY creation syntax out of the full.ZX.nr.dmp file. The syntax below, however, will fix the above error (and another similar one which appears later): e4.txt The next error to catch our eye is displayed below: e5.txt Any experienced DBA has seen the above error a hundred times. IMPORT is trying to fit a size 15 foot into a size 7 shoe. Anyone with big feet will tell you this problem is tricky to fix but we do have several options. It turns out that the EVENTLOG_IMPORT is not on our list of tables we want to copy from ZX to W12; we lucked out; we can ignore the error. The next error to catch our eye is displayed below: e6.txt Now we are not so lucky; LIVEVCR_EVENT_SUM is a table we want to copy. We will use a technique called "pre-creation" to fix the problem. The script below will pre-create LIVEVCR_EVENT_SUM should we choose to run it before we run IMPORT: e7.txt The next three errors to catch our eye ared displayed below: e8.txt Reacting to an index creation error is easy. We only need to note that the index failed during creation, use our mouse and keyboard to craft an index creation script, and then run the index creation script at an appropriate time after all the import attempts have finished. Unlike tables, indexes do not need to be filled with data. For the above errors, we write the script below which has small values for the storage parameters: e9.txt The next error to catch our eye is displayed below: e10.txt Our reaction to it is the creation of the script below: e11.txt The next error to catch our eye is displayed below: e12.txt Our reaction to it is displayed below: e13.txt The next errors to catch our eye are displayed below: e14.txt Our reaction to it is displayed below: e15.txt The next error to catch our eye is displayed below: e16.txt The cause of the above error is not immediately obvious. We try to gain a clue from the "oerr" utility: e17.txt Reading the above message prompts us to write the one line script below: ALTER TABLE PLAYBACK_EVENT_SUM ENABLE CONSTRAINT PLAYBACK_EVENT_SUM_PK; Now that we have all of our scripts written, we are in a position to run them. The screen dump below displays our efforts to run the above scripts: allem.txt As we can see, the script had some problems. We issue some commands to fix them: trygin.txt We are in a good state now; we have fixed all of the problems encountered by IMPORT. We are at the point now where we could issue a series of table-level-rows=y IMPORTs. Run a series of TABLE-LEVEL-ROWS=YES imports on the W12 host ------------------------------------------------------------- When we list the export files corresponding to the tables we want to import we see this: hogexpfilist.txt To ease some of the effort of importing all of these export files, we will craft a script which takes Oracle username, table name, and export file name on the command line and imports the data from the export file into the table owned by the Oracle username. A script which does this is displayed below: imptabFromZ.txt Now that the above script is created, we are free to call it from another script. We build the other script with the help of a simple one line perl script: /bin/ls -1 *Z |\ perl -ne 'print("imptabFromZ ", (@a=split /\./,$_)[0]," ",$a[1]," ",$_)' The result is displayed below: runImps.txt Before we run the above script we need to give a short discussion about dependencies and constraints. Oracle gives us the ability to create dependencies between data in the database using Oracle objects aptly named "constraints". For example, we might want to use a constraint that enforces a rule which says that if an address exists, it must belong to a person or a company. This works great during normal database operation. It may cause us problems though, if we populate a database from export files. For example we might want to import an addresses table before we import a persons table. For this reason, it is a good idea to disable constraints before importing data into a long list of tables. The syntax for turning off constraints may be obtained by pushing the hogmgr.ZX.nr.dmp through a grep-sed filter: grpSedhog.txt Notice how we use the strings command above to work around the Linux-grep bug. The resulting syntax is displayed below after we place it in a sql script: disableConstraints.txt After we run the above sql script twice, we run our script runImps.sh: runImpsrun.txt As we can see the above script generated many errors. It turns out we should have added a couple of parameters to each of the import commands: CONSTRAINTS=N COMMIT=Y We add the parameters (with the intent that we will create the constraints after the import by hand somehow) and now the import script imptabFromZ looks like this: impttabFromZ.txt Now that we have fixed a bug in imptabFromZ, we need to put the database back into the state at which it existed before we ran the script runImps.sh. We need to truncate all the tables owned by hogmgr. The SQL plus commands below will help us generate the appropriate syntax: SET HEADING OFF LINESIZE 77 SELECT 'TRUNCATE TABLE '||TABLE_NAME||';' FROM TABS; When we run the above command we end up with the script below: truncHog.txt When we run the above script we see the following: trunchogout.txt As we can see, the script generated several ORA-08103 errors. The last error, for example, is on the table ZONE_EVENT_DEF. When we look for the table in user_tables we see this: usrtableslook.txt These ORA-08103 errors are an indication of a bad thing and I've never seen a situation like this: some table names appear in the USER_TABLES dictionary view, but when we attempt to access the tables, the Oracle kernel tells us the tables do not exist. It appears that we have some sort of corruption in the data dictionary. Some of the tables are not fully visible to Oracle kernel. We have decided on this course of action: -Drop the HOGMGR user (which will also vaporize all of its tables and other objects) -Issue commands to create the HOGMGR user with the same privileges it had before -Use the knowledge we had gained before to pre-create a variety of HOGMGR's objects -Attempt a ROWS=N CONSTRAINTS=N INDEXES=N IMPORT of hogmgr.ZX.nr.dmp: imp USERID=/ BUFFER=654321 FILE=hogmgr.ZX.nr.dmp FROMUSER=hogmgr TOUSER=hogmgr \ ROWS=N CONSTRAINTS=N INDEXES=N LOG=hogmgr.ZX.nr.imp.log -React to any errors from the IMPORT -Attempt to run our script runImps.sh -React to any errors from the run of runImps.sh A screen dump of the implementation of this plan is displayed below: notherplan.txt Careful inspection of the above output reveals that the IMPORT utility is serving up some unexpected behavior. The first error which catches our eye is displayed below: ee1.txt Notice here that IMPORT is trying to create an index named LIVEVCR_EVENT_SUM_PK. It should not be doing this, we told it via the command line option INDEXES=N that it should not create any indexes. Perhaps IMPORT is getting confused by the fact that Oracle is issuing an error related to the kernel's inability to expand the size of data file 23. We decide to partially test this out by expanding the size of the CLIENT_LOGS tablespace and reattempting the import: reattmpt.txt Inspection of the above test results does confirm that we have encountered a bug in IMPORT. This is an easy bug to work-around but it requires that we re-run the table level exports on the source database. We are assuming of course that EXPORT does not have the same bug as IMPORT. We show a screen dump below of our test of this assumption: tstExpasmp.txt Inspection of the above output confirms that our assumption that EXPORT will honor the INDEXES=N command line option, is a valid assumption. So, here is our current plan: -Drop the HOGMGR user -Export HOGMGR'S tables with the INDEXES=N command line option -Export HOGMGR'S other objects such as views and sequences -Use UNIX text processing techniques to generate a table pre-creation script -Create HOGMGR user -Pre create HOGMGR tables -Import HOGMGR's tables and other objects such as views and sequences -Use UNIX text processing techniques to generate an index creation script -Run the index creation script -Compare counts of objects between the two databases We paste a screen dump below: pastesdb.txt We have a very good match between the counts of objects on both the source and target databases; we are done. End of file