ch05.htm

Teach yourself Oracle8 in 21 day

db_file_multiblock_read_count       integer 8
delayed_logging_block_cleanouts     boolean TRUE
hash_multiblock_io_count            integer 8
log_block_checksum                  boolean FALSE
SVRMGR>
</FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>In this example, I requested the value of all of the parameters that had the word
<I>block</I> in them. As you can see, I got back a number of parameters with <I>block</I>
in their names.</P>
<P>This is useful for determining the default values of all the Oracle parameters
on your system. By using the command <TT>SHOW PARAMETERS</TT> with no qualifiers,
you will see a listing of all the Oracle tunable parameters (see Listing 5.2).</P>
<P><B>INPUT:</B></P>
<P><FONT COLOR="#000077"><B>Listing 5.2. See a listing of the Oracle tunable parameters.</B></FONT></P>
<PRE><FONT COLOR="#0066FF">D:\&gt;svrmgr30

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production 

SVRMGR&gt; connect internal
Password:
Connected.
<TT>SVRMGR&gt; show parameters</TT></FONT></PRE>
<P><B>OUTPUT:</B></P>
<PRE><FONT COLOR="#0066FF">NAME                                TYPE    VALUE
----------------------------------  ------  -----------------------------
O7_DICTIONARY_ACCESSIBILITY         boolean TRUE
allow_partial_sn_results            boolean FALSE
always_anti_join                    string  NESTED_LOOPS
aq_tm_processes                     integer 0
arch_io_slaves                      integer 0
audit_trail                         string  NONE
.
. Parameters Omitted due to length
.
timed_os_statistics                 string  off
timed_statistics                    boolean FALSE
transaction_auditing                boolean TRUE
transactions                        integer 66
transactions_per_rollback_segment   integer 11
user_dump_dest                      string  %RDBMS80%\trace
utl_file_dir                        string
SVRMGR&gt;
</FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>This represents all the Oracle tunable parameters. Throughout this book, you will
see most of these parameters again, and a full listing of all these parameters appears
in Appendix B, &quot;Oracle Tuning Parameters.&quot;</P>
<P>When this book shows an operation done with a SQL statement, it is being done
via Server Manager. Server Manager provides the flexibility to use SQL commands with
all the available options. These commands can be put into a script file, with the
extension <TT>.SQL</TT> and can be run from within Server Manager. Simply put an
<TT>@</TT> before the filename of the SQL script and it will be run. It is not necessary
to add the <TT>.SQL</TT> extension to the name when running it. For example, to run
a SQL script called <TT>build.sql</TT> in the <TT>c:\database</TT> directory, you
can use the syntax shown in Listing 5.3 within Server Manager.</P>
<P><B>Input/Output:</B></P>
<P><FONT COLOR="#000077"><B>Listing 5.3. Using Server Manager to invoke a SQL script.</B></FONT></P>
<PRE><FONT COLOR="#0066FF">D:\&gt;svrmgr30

Oracle Server Manager Release 3.0.3.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production


SVRMGR&gt; connect internal
Password:
Connected.
SVRMGR&gt; @c:\database\build
...
... Data Returned
...
<TT>SVRMGR&gt;</TT> </FONT></PRE>
<P>As you will see throughout the book, there are certain operations that I prefer
to do within a SQL script. By scripting these operations, I have certain advantages,
including the following:

<UL>
	<LI>A permanent record of exactly how the operation was done.
	<P>
	<LI>A template for future work. After I have scripted a database build, I can modify
	that script for use on other databases.
	<P>
	<LI>This script can be used to re-create the database in the event of some sort of
	failure.
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>By keeping your build scripts, you
	will be able to quickly rebuild your database in the event of a catastrophic failure.
	
<HR>


</BLOCKQUOTE>

<P>Throughout this book, you will see numerous examples in which Server Manager is
needed for some operations.</P>
<H2><FONT COLOR="#000077"><B>Managing a Bootstrap Instance Under NT</B></FONT></H2>
<P>With the NT operating system, the Oracle bootstrap instance must be created before
the database can be created. Because it is a service, the instance is a little different
from the Oracle instance on other operating systems. A service called <TT>OracleService</TT>SID
(where SID is the system identifier) is created when the instance is created. This
service or instance service is used to bootstrap the more traditional Oracle instance.
Because the Oracle server is a service under NT, you cannot connect to Oracle or
create the database without this service's being started. The instance is created
via the NT Instance Manager. This utility should not be confused with the Enterprise
Manager's Instance Manager; they are separate utilities.</P>
<H3><FONT COLOR="#000077"><B>Creating an Instance with the Oracle Database Assistant</B></FONT></H3>
<P>When you invoke the Database Assistant from the Oracle for Windows NT workgroup,
you will see the Database Assistant Welcome screen. This will remain until the Database
Assistant has loaded. After the Database Assistant has loaded, you will be prompted
with the initial choice as to the function you want to perform (see Figure 5.14).</P>
<P>You have the choice of creating or deleting a database. For the sake of example,
let's create a database. To do so, perform the following steps:

<DL>
	<DD><B>1.</B> You can perform either a typical or a custom database creation, as
	shown in Figure 5.15. A typical database creation creates the standard starter database,
	whereas the custom installation lets you change parameters such as instance and datafile
	names. In this example, let's perform the typical database creation. The custom database
	creation is shown oon Day 6, &quot;Administering Databases and Datafiles.&quot;
</DL>

<P><A NAME="14"></A><A HREF="14.htm"><B>Figure 5.14.</B></A></P>
<P><I>Database Assistant.</I></P>
<P><A NAME="15"></A><A HREF="15.htm"><B>Figure 5.15.</B></A></P>
<P><I>Performing a typical database creation with Database Assistant.</I></P>

<DL>
	<DD><B>2.</B> As shown in Figure 5.16, you will be asked whether you want to add
	replication support to this database. If you will be using advanced replication,
	select the Advanced Replication button. This adds the extra stored-procedure packages
	used by advanced replication. Replication is covered in detail on Day 18, &quot;Administering
	Oracle Replication.&quot;
	<P><B>3. </B>You will be asked whether you can take advantage of the timesaving feature
	of copying database files from your CD-ROM rather than creating them, as shown in
	Figure 5.17. If you do not have the CD-ROM available, you need to create the files.
</DL>

<P><A NAME="16"></A><A HREF="16.htm"><B>Figure 5.16.</B></A></P>
<P><I>Select Advanced Replication if you want to add replication support to this
database.</I></P>
<P><A NAME="17"></A><A HREF="17.htm"><B>Figure 5.17.</B></A></P>
<P><I>Create new database files using Database Assistant.</I></P>

<DL>
	<DD><B>4.</B> Click the Finish button; the database will be created for you. As usual,
	you have one last chance to change your mind, as shown in Figure 5.18. If the directory
	looks correct, click Yes; the database will be created.
</DL>

<P><A NAME="18"></A><A HREF="18.htm"><B>Figure 5.18.</B></A></P>
<P><I>Confirm that you want to proceed with the database-creation process.</I></P>

<DL>
	<DD><B>5.</B> One final screen asks for input and informs you that the database named
	<TT>oracle</TT> will be created with the SID <TT>orcl</TT>. If this is suitable,
	click OK (see Figure 5.19).
</DL>

<P><A NAME="19"></A><A HREF="19.htm"><B>Figure 5.19.</B></A></P>
<P><I>This dialog identifies the new database's name and SID.</I></P>
<H3><FONT COLOR="#000077"><B>Creating the Instance from the Command Line</B></FONT></H3>
<P>Creating the instance from the command line uses the same program as the GUI version,
but with command-line arguments. The Instance Manager is actually the program named
<TT>ORADIM80.EXE</TT>. To obtain help on the various parameters of <TT>ORADIM80</TT>,
use the command <TT>ORADIM80 -?</TT>.</P>
<H4><FONT COLOR="#000077"><B>Creating the Instance</B></FONT></H4>
<P>To create an instance using <TT>ORADIM80.EXE</TT>, run it with the following arguments:</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -NEW -SID SID -INTPWD password [-MAXUSERS number] 
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">[-STARTMODE AUTO or MANUAL] [-PFILE pfile_name]
</FONT></PRE>
<P>The values for SID and password are mandatory, whereas the values for <TT>MAXUSERS</TT>,
<TT>STARTMODE</TT>, and <TT>PFILE</TT> have defaults. Here is an example of creating
an instance called <TT>dogs</TT>:</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -NEW -SID dogs -INTPWD oracle
</FONT></PRE>
<P>This will create the instance <TT>dogs</TT> with a default <TT>PFILE</TT> of <TT>initDOGS.ora</TT>
and manual startup.</P>
<H3><FONT COLOR="#000077"><B>Starting and Stopping the Instance</B></FONT></H3>
<P>The instance can be started or stopped from the command line through the use of
the following arguments to <TT>ORADIM80.EXE</TT>:</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -STARTUP -SID SID -PFILE pfile_name 
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">[-USRPWD password] -STARTTYPE SRVC or INST
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -SHUTDOWN -SID SID -PFILE pfile_name 
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">[-USRPWD password] -SHUTTYPE SRVC or INST -SHUTMODE a,i,n
</FONT></PRE>
<P>where the shutdown modes are <TT>a</TT>=abort, <TT>i</TT>=immediate, and <TT>n</TT>=normal.</P>
<H3><FONT COLOR="#000077"><B>Modifying the Instance</B></FONT></H3>
<P>At times, it becomes necessary to modify the instance you have created. This can
be done with the NT Instance Manager or with the <TT>ORADIM80.EXE</TT> program. Both
methods use the executable <TT>ORADIM80.EXE</TT>. If you do not provide command-line
options, the graphical tool will be invoked.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The Oracle Database Assistant can
	be used only to create and delete a database, not to modify one. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000077"><B>Modifying an Instance with the GUI</B></FONT></H4>
<P>You can edit an instance by selecting the instance name and then clicking the
Edit button (see Figure 5.20).</P>
<P><A NAME="20"></A><A HREF="20.htm"><B>Figure 5.20.</B></A></P>
<P><I>Select Edit in the Instance Manager.</I></P>
<P>This will take you to the Edit Instance screen. You have several options available
from the Edit screen; from here, you can change the administrative password or the
startup mode by clicking a button or typing a value.</P>
<H3><FONT COLOR="#000077"><B>Editing the Instance with <TT>ORADIM80.EXE</TT></B></FONT></H3>
<P>To edit an instance, use the same command with the <TT>-EDIT</TT> flag like so:</P>
<PRE><FONT COLOR="#0066FF">
ORADIM80 -EDIT -SID SID -INTPWD password [-MAXUSERS number] 
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">[-STARTMODE AUTO or MANUAL] [-PFILE pfile_name]
</FONT></PRE>
<P>The parameters are the same.</P>
<H2><FONT COLOR="#000077"><B>Deleting an Instance Under NT</B></FONT></H2>
<P>It is occasionally necessary to delete an instance under Windows NT. This can
be accomp-lished via the NT Instance Manager or with the <TT>ORADIM80</TT> command,
as demonstrated previously.</P>
<H3><FONT COLOR="#000077"><B>Deleting an Instance</B></FONT></H3>
<P>To delete an instance, select the instance you want to delete and click OK. This
deletes the instance and its services from the system.</P>
<H3><FONT COLOR="#000077"><B>Deleting the Instance with <TT>ORADIM80</TT></B><TT>.EXE</TT></FONT></H3>
<P>Using <TT>ORADIM80.EXE</TT>, you can delete an instance or a service. This is
done using the <TT>-DELETE</TT> option like so:</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -DELETE -SID SID
</FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">ORADIM80 -DELETE -SRVC service
</FONT></PRE>
<H2><FONT COLOR="#000077"><B>Starting Up the Instance</B></FONT></H2>
<P>The Oracle instance can be started manually, automatically, through a graphical
tool, or from the command line. In this section, you will look at all the different
ways the Oracle instance can be started.</P>
<H3><FONT COLOR="#000077"><B>Starting the Instance Automatically</B></FONT></H3>
<P>The Oracle instance can be configured to start automatically when NT Server is
booted. Configure the instance to start this way by specifying the automatic startup
option when the instance is created, as shown in the section called &quot;Creating
an Instance with the Oracle Database Assistant.&quot; After the instance has been
created, you can edit the service <TT>OracleStart</TT>SID by editing the service
properties from the NT Service Manager program.</P>
<P>After you select the service with NT Service Manager, click the Startup button.
Here you can change the startup type from Automatic to Manual, as shown in Figure