Chapter 4

Creating Your Database

In This Chapter

Familiarizing yourself with the Oracle environment

Configuring an Instance

Using the Database Configuration Assistant to create databases

Post database creation checkup

Creating a database takes a lot of work. Thankfully, a graphical tool called the Database Configuration Assistant (DBCA) helps you point and click your way to victory. However, as its name implies, it only assists. Just like any software wizard-type tool, it can’t cover every option; it can’t explain everything. It does cover up some of the ugly syntax and other required activities (like creating directories and setting permissions) commonly forgotten by someone new to Oracle. It truly is a wonderful tool . . . as long as you know what options to use and what values are appropriate for the questions that it asks you.

With that said, this chapter goes over some of the details necessary to make the right decisions up front when creating a database. Doing so saves you from having to go back and do things twice . . . or even three times. In addition, when you understand why you make certain choices, it helps you create a robust and scalable database that serves you for a long time to come.

Feeling at Home in Your Environment

You should get familiar with a few things before working in your Oracle environment:

Oracle software owner

Oracle version

Oracle base

Oracle home

Oracle SID (instance/database name)

Path

Knowing how to find and work with these, you will better be able to manage not only your database but also databases and Oracle installations on other machines.

If Oracle was properly installed, these items should be relatively similar across most installations. Furthermore, if they’re not similar, understanding what they are and how to find their values makes it easier for you to adapt.

Some slight differences exist between Oracle installations on Windows versus a Linux/UNIX environment. We point out some of those differences as well.

Finding the Oracle software owner

The Oracle software owner is a user on the operating system.

Linux/UNIX

On Linux/UNIX, you typically create a new user to own the installation files. Most commonly, this user is called oracle.

In addition, you create two OS groups:

oinstall should be the user’s primary group. This group will contain any users whom you would like to allow the ability to install and patch the Oracle software.

dba contains any users whom you would like to have the power to manage the database in its entirety. Be very careful who you put into these groups — they could wreak havoc on your system and/or have access to all your data.

Windows

Windows has gone a long way to simplify running complex software on their system, and Oracle developed its software to play along. Installing Oracle on Windows only requires that the user be a member of the Local Administrators group on the machine where Oracle is installed.

Consider these tips, however, which include more creation:

You don’t have to create a user specifically to own the software in Windows, but we do it anyway because Oracle runs on Windows through a series of services. This way you can start those services as a specific owner.

If you use the Windows task scheduler, consider using the Oracle software account to run the jobs. Jobs are easier for people to identify when they’re owned by a named account.

In Windows, you may sometimes want to map a drive for Oracle to use. It’s easier if you assign it to a central Oracle management account so it isn’t removed by someone else or forgotten about if passwords change.

You don’t have to create any groups on Windows, but during the installation it creates a group on its own called ORA_DBA. This group behaves much the same way as the dba group on Linux/UNIX, so be careful who you add to it.

Oracle versions

This book is about Oracle 12c, but you may have to deal with environments that have multiple versions of Oracle installed. This issue with multiple versions is especially evident when you’re upgrading your database from one release to the next. You may also encounter it when you’re testing new releases against existing applications.

When you upgrade a database to the same machine, you install the new version of Oracle in parallel with the existing one. It’s important to know how to change the environments around and tell which one is active. You find out how to do so on both Windows and Linux/UNIX in the later section “Setting your environment with oraenv.”

Getting to home base

On systems where Oracle is installed, an important part of managing the Oracle installation is understanding environment variables. Environment variables tell

The OS what software to run

Oracle where to store certain files

Oracle what database you want to connect to

The four most important variables are

ORACLE_BASE

ORACLE_HOME

ORACLE_SID

PATH

ORACLE_BASE

ORACLE_BASE is the top directory where all Oracle files on the machine are going to exist. If you have multiple versions of Oracle on the same machine, the ORACLE_BASE is likely the same.

Unless you have extraordinary circumstances and want everything to stay separate, we recommend having your ORACLE_BASE be the same for all installations.

Here are a few common ORACLE_BASE settings:

/opt/oracle

/u01/app/oracle

/app/oracle

Oracle documentation uses /u01/app/oracle in most examples, so we stick with that here.

Keep the following advice in mind when setting ORACLE_BASE:

Don’t install anything else under ORACLE_BASE.

Choose a mount point that’s not used for any other major OS or other third-party software.

The final directory in the ORACLE_BASE should be oracle.

When you create your database, Oracle creates a series of directories underneath the ORACLE_BASE and uses them for management, logging, and troubleshooting.

ORACLE_HOME

ORACLE_HOME is where you have Oracle installed. Not only that, but it tells your session which Oracle installation you want to use.

If you have multiple Oracle installations on the same machine, set this variable to the location of the one that you want to work with.

Typically, ORACLE_HOME values contain the major release number of the Oracle version installed in the directory. It’s created as a subdirectory off ORACLE_BASE. For example:

/u01/app/oracle/product/12.1.0

/u01/app/oracle/product/11.2.0

/opt/oracle/product/9.2.0

$ORACLE_BASE/product/12.1.0

The last example shows how you should use your ORACLE_BASE to define your ORACLE_HOME.

ORACLE_SID

ORACLE_SID is simply set to the name of the database that you want to connect to. If the database doesn’t exist, set it to the name of the database you’re about to create.

Limit your ORACLE_SID to 8 characters beginning with a letter. Also, on some operating systems, ORACLE_SID is case sensitive. We recommend sticking with lowercase.

You can change the ORACLE_SID within your session if you’re moving around to different databases. Just be very careful and note which database you’re connecting to. I’d be lying if I said the authors of this book have never made that mistake.

PATH

The PATH variable is typically already set for all sessions on the system. However, when you’re using Oracle, you have to add to the path. You simply have to remember to put ORACLE_HOME/bin in front of your path.

ORACLE_HOME/bin is where the Oracle binaries are located. It contains tools such as the DBCA, SQL*Plus, and Data Pump.

By putting ORACLE_HOME/bin in front of your path, you can execute these tools without always having to

Be in the ORACLE_HOME/bin directory.

Type the full path every time to want to launch a tool.

The OS checks your PATH locations sequentially to find the tool you’re trying to launch. By putting your ORACLE_HOME/bin first, you guarantee not launching some other software package that has a tool with the same name as one of your Oracle tools.

Setting your environment with oraenv

All the environment settings are stored in your OS user profile on Linux/UNIX. That way, the appropriate parameters are configured every time you log in to the system to use the database.

If you’re constantly switching your environment to connect to different databases and different Oracle versions, it might suit you to create a script where you name your various environments and then run the script and input your choice.

Oracle provides a script to change the environment on Linux/UNIX installations: oraenv. (Windows has no such handy little script.) You simply run the script, and it asks what database you want to connect to. Then it sets the rest of your environment accordingly.

This output asks whether you want to set the environment for the dev12c database. That happens to be the first database created on the machine by default. We override the default by choosing prod12c and it set the environment accordingly.

[oracle@classroom ~]$ oraenv

ORACLE_SID = [dev12c] ? prod12c

The Oracle base for ORACLE_HOME=

/u01/app/oracle/product/12.1.0/db_1 is /u01/app/oracle

In Windows, all the environment settings are also set in the registry. You can override them by setting variables from the DOS command line or by setting system-level environment variables. Of course, if you’re lucky enough to have only one environment and one database on your machine, you only have to mess with this once, when setting Oracle up. For most people, that doesn’t seem to be the case.

Configuring an Instance

Certain files in the database can completely change the way your database behaves. They can influence everything from performance and tuning as well as troubleshooting. Maintaining and configuring these files are a major component of database administration.

Using PFILE and SPFILES

These are the files that set up your database operating environment:

PFILE

SPFILES

In Chapter 2, we talk a bit about PFILE and SPFILES. In this section, we go through many of the common parameters you find in these files. The parameter file is the first file read when you start your database; the parameters within it configure how your database operates.

First, take a look at an example of a PFILE and some of the commonly set parameters:

*.audit_file_dest='/u01/app/oracle/admin/dev12c/adump'

*.audit_trail='db'

*.compatible='12.0.0.0.0'

*.control_files='/u01/app/oracle/oradata/dev12c/control01.ctl',

                 '/u02/app/oracle/oradata/dev12c/control02.ctl',

                 '/u03/app/oracle/oradata/dev12c/control03.ctl'

*.db_block_size=8192

*.db_domain='lfg.com'

*.db_name='dev12c'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4815m

*.diagnostic_dest='/u01/app/oracle'

*.memory_target=1280m

*.open_cursors=300

*.processes=300

*.undo_tablespace='UNDOTBS1'

The parameters have a * in front of them because you can use the parameter file to set parameters in more than one Oracle instance. In a file that serves multiple Oracle instances, you may see the instance name in front of some of the parameters, denoting that particular parameter only applies to one instance.

Follow these steps to see the parameters that are modified in an existing Oracle database:

1. Log in to SQL*Plus as a SYSDBA.

2. Type create pfile from spfile; (including the semicolon).

The command dumps a text version of your SPFILE.

After you create your PFILE, you want to turn it into an SPFILE. Essentially, you do the reverse of what you did before:

1. Log in to SQL*Plus as a SYSDBA.

2. Type create spfile from pfile; (including the semicolon).

You get a file called spfileORACLE_SID.ora in the same directory as your PFILE, where ORACLE_SID is your instance_name.

Setting parameters in the pfile and spfile

Whether you use PFILES or SPFILES determines how you set your parameters. This next section explains the common parameters in Oracle 12c and how they’re configured in the files themselves.

With a new database, you always start with a PFILE. If you end up wanting to use an SPFILE, you create it from the PFILE (shown at the end of the chapter).

The first thing you need to do is find your PFILE. For whatever reason, despite all the other similarities, Linux/UNIX and Windows store it in different locations.

Find your PFILE on Windows, where ORACLE_SID is your instance name:

ORACLE_HOMEdatabaseinitORACLE_SID.ora

Find your PFILE on Linux/UNIX, where ORACLE_SID is your instance name:

ORACLE_HOME/dbs/initORACLE_SID.ora

These parameters are some of the most commonly customized. Most parameters suit most databases at their default value.

The * means to apply the parameter to all instances that read this file.

audit_file_dest: This parameter tells Oracle where to put auditing information on the file system. All connections to the database as SYSDBA are audited and put into this directory. Furthermore, if you’re auditing other operations in the database, those audit records may be dumped here as well.

audit_trail: This tells Oracle where you want audit records written. Audit records are written to the database or the file system. They can be in text format or XML. Records written to the database are stored in the AUD$ system table. The valid values for this parameter follow:

• db: Normal audit records written to the AUD$ table

• os: Normal audit records written to the audit_file_dest directory

• db_extended: Audit records written to the AUD$ table in extended format, including SQLTEXT and bind variable values

• xml: XML-formatted normal audit records written to the database

• xml, extended: Normal auditing and includes all columns of the audit trail, including SqlText and SqlBind values in XML format to the database

compatible: Set it to force the database to behave like a version earlier than Oracle 12c. In Oracle 12c, you can set it back as far as 10.0.0. However, it can be set back only before the database is created or before upgrading from an earlier version. After you migrate this parameter to 12.1.0 and open the database, you can no longer go back. The parameter is useful for testing before an upgrade is complete. Most of the time you find it set on the latest version for your software. If you try using a feature from a database version later than what you’ve configured, it results in an Oracle error.

control_files: Just what is says. It tells the instance where to look for the control files during the startup phase. If the instance doesn’t find even one of them, you can’t mount your database. Notice in the parameters listing that the control files are spread across three different mount points.

  db_block_size: This parameter is really the only one you can’t easily change without recreating the database, so choose it carefully. It tells the database what block size you want your Oracle blocks to be formatted on disk. We discuss block size in the upcoming “Taking the DBCA steps” section.

db_domain: If you want your network domain to be part of your database name for identification purposes, fill in the domain name here. This won’t be your actual database name, but an alias to identify it from other databases with the same name that might exist in another domain.

  db_name: The database name. Choose this name carefully. Although you can change it, doing so is a pain. The name can be up to eight alphanumeric characters. Avoid the urge to use special characters other than #, $, and _.

db_recovery_file_dest: This sets what’s known as the Fast Recovery Area. The area can hold files such as

• Backups

• Archive log files

• Control files

• Redo log files

db_recovery_file_dest_size: This determines how much space is dedicated to your Flash Recovery Area. If it fills up, you get an error message and the database could come to a halt — especially if you’re storing archive log files here. If archive log files can’t be written, redo log files can’t be overwritten. User sessions hang until the situation is resolved.

diagnostic_dest: This location is known as the Automatic Diagnostic Repository (ADR) home. It contains files that Oracle support may use to resolve issues with your database. This parameter was new in 11g. You can use a new tool called ADRCI to access the files in this directory. It contains

• Trace files

• Core files

• Alert logs

• Incident files

memory_target: This parameter sets the memory that the Oracle instance is allowed to use for all System Global Area (SGA) and program global area activities described in Chapter 2. It doesn’t include memory consumed by server and user processes.

open_cursors: Limits the number of open SQL cursors a session can have.

processes: Limits the number of OS users’ processes that can connect to the instance.

undo_tablespace: This parameter tells the instance to which tables it will write its transaction undo. It must be an undo type tablespace.

Creating Your Oracle Database

You can create a database one of four ways:

Manually with SQL commands: If you’re on an ancient release like Oracle 8i, we recommend manual SQL commands; the DBCA wasn’t as good back then. However, with Oracle 9i and up, it has really become a robust and useful tool. Furthermore, with more features being added to the database, the manual method isn’t a laundry list of scripts. Back in the day, you had to run only an SQL command and two scripts. Not anymore. There are upwards of a dozen creation scripts depending on what features you want to install.

  With the graphical tool called Database Configuration Assistant (DBCA): We recommend Database Configuration Assistant (DBCA) to make your Oracle database. This recommendation is especially important for beginners. If you use DBCA to create the database, you don’t have to make the PFILE; the DBCA creates it for you. You may want to alter your setting later, however.

A combination of SQL commands and DBCA: Even old-timers like us prefer DBCA or SQL and DBCA. Using SQL to create the database gives you control over every aspect of the creation, but it also leaves open a lot of areas for mistakes and accidental omissions.

Cloning an existing database: This book doesn’t cover the topic because it’s a more advanced topic for, uh, smarties?

Using the Database Configuration Assistant (DBCA) to Create Databases

Launch the Database Configuration Assistant (that’s right; you’re in charge) from the command line of the operating system where the database resides.

The DBCA walkthrough in this chapter chooses the Advanced and Custom Database option (versus General Purpose or Data Warehouse options). This option is for when you really want to get your hands dirty and have complete control. We like this option for a few reasons:

You don’t have to install the features that you aren’t going to use. They just take up more space and give you more things to manage.

You can specify a lot more options that the other templates don’t allow.

Customizing isn’t that hard. You’re reading the book, right? It’ll be easy.

The only drawback to the Custom Database method is the time it takes while creating the data files. How much time? We’ve seen it take anywhere from 2 to 30 minutes. It depends on

The number of CPUs your system has

What features you select

Taking database control

Oracle Enterprise Manager is an option you can choose during database creation. Don’t get too attached to this invaluable resource. Take some time to get to know the basic SQL commands for managing your database. I’ve seen Enterprise Manager crash where the only thing left was a blinking SQL prompt. A well-rounded DBA knows how to manage her database both ways.

---

---

Taking the DBCA steps

If you’re ever unsure about an option on the DBCA screen, click the Help button. It does a good job of explaining what each item does.

One of the things we have noticed in Oracle 12c is that Oracle has done a good job of speeding up the Help function. In past versions, it took forever to load. Also, in 12c, the look and feel has changed a lot to be more consistent with the Oracle Universal Installer.

The DBCA has a lot of screens with all kinds of information. The following steps take you through creating a database with the Database Configuration Assistant:

1. Log in as the Oracle software owner.

2. Go to a command prompt.

3. Type dbca.

You see a splash screen, as shown in Figure 4-1, and another screen with options.

4. Select the Create a Database option.

You are presented with the option of using a default configuration or an advanced one.

5. Select the Advanced option.

6. Click Next.

You see the output shown in Figure 4-2. Optional database templates are shown:

• General Purpose

• Data Warehouse

• Custom Database

Figure 4-1: The Database Configuration Assistant welcomes you.

The first two include the data files. You supply a few custom settings, and it unzips the database from the Oracle installation directory. Use these options only when you’re new to Oracle and aren’t sure what to do with some of the more advanced parameters. You should select Custom here; we go over all the options in the rest of the steps.

7. Select the Custom Database option.

Click Show Details if you want to see the features, parameters, and files chosen by default for each type of database.

8. Click Next.

A screen asks you to choose the database name.

9. Fill in these fields:

• Global Database Name: Your database name with your network domain attached. If you don’t want to attach your network domain, leave this field blank. Doing so just sets the initialization parameter db_domain. It helps uniquely identify your database on the network. For example, you might have a database named prod in two different domains. A global database name allows that without confusing some of the Oracle networking features.

• SID: This is the short name for your database. It equates to your environment variable ORACLE_SID.

Figure 4-2: Database creation options require lots of steps.

10. Click Next.

You’re asked whether you want to manage your database with Oracle Enterprise Manager, as shown in Figure 4-3. (You can read more about Oracle Enterprise Manager in Chapter 13.)

You can configure this two ways:

• Configure Enterprise Manager (EM) Database Express: Database Express is a management package that runs locally on the database machine and has many of the features of Cloud Control. However, it controls only one database. We caution you only when configuring Database Express. If you’re setting up Oracle on a machine with limited resources, you’re going to feel Database Express, if you know what we mean. The good news is, in 12c, Database Express is much better in resources usage than prior versions (known then as Database Console).

• Register with Enterprise Manager (EM) Cloud Control: An Oracle software package that typically runs on its own server elsewhere on your network. It can manage many databases, many versions of Oracle, servers, application servers, and even other non-Oracle software, such as Microsoft SQL server, and firewalls. You must have the Grid Control Management Agent installed to get this option.

Figure 4-3: Your database management options.

11. Select the Configure Enterprise Manager (EM) Database Express option.

12. Click Next to continue.

13. Set the database credentials (passwords).

You have two choices here. You can set the passwords individually for the users that are created with the database or set the same password for all users. We ask that you select Use the same administrative password for all accounts to keep things simple for now. Fill in the Password and Confirm Password text boxes.

Here are some good password practices:

• Use different password for each user. If this is a test database, it may not matter that much. Even if you want to keep it simple now and make all the passwords the same, you can easily go back change them later.

• Set the same password for everyone. If this is production, it’s in your best interest to have separate passwords for all the users.

14. Click Next.

You’re asked whether you want to register your database with a listener. If you do, choose the correct listener and click Next.

15. Select an option for how you want to store your files.

The screen has a drop-down list for you to select how you want to store your files:

• File System: All your data files are put into formatted drives attached to your computer.

• Automatic Storage Management (ASM): Don’t choose this now. ASM has some great benefits but isn’t as easy to set up as the DBCA leads you to believe.

16. Select a place to store your files:

• File Locations from Template: This option doesn’t let you make any changes. Oracle chooses where to put the files.

• Common Location: This option activates the grayed-out field. You choose type or browse for a location to store the files.

Separating files across multiple mount points is a best practice for performance and protection. If you create a test database or other nonproduction database, it’s okay to put them all in one place if you have the space.

17. Choose File Locations from Template and click Next.

You’re in the Fast Recovery Area (FRA) configuration. The FRA is a storage area that resides on disk which can house backups, archive logs files, control files, and redo log files.

18. Choose to configure the FRA.

Doing so simplifies the storage of backups and archive log files. We don’t typically use it for the control or redo files; we manually separate those files ourselves.

19. Determine what FRA space you have available and increase it ­accordingly.

The default value is about 5GB. This might be okay for the archive log files of small databases. However, an FRA of this size fills up very quickly. You can resize the FRA anytime without taking down the ­database.

20. Choose to archive later and click Next.

Archiving adds drain on the system while creating the database. It’s easy enough to enable later on.

---

---

You’re asked what features you want to install on the screen. Depending on what software you installed, not all are available. Luckily, you can add later. Click the Help button if you want a more detailed description:

• Oracle Text: This indexing feature allows custom indexing of large text-type documents. It can index pages of data. It also allows advanced searching against rich media objects.

• Oracle OLAP: This is Oracle’s business intelligence tool.

• Oracle Spatial: This mapping tool is for geospatial mapping.

• Oracle Label Security: Label security is for securing data in a way that gives users levels of access to restricted data.

• Sample Schemas Tab: This is a bunch of test data that you can use for training or trying new features. We usually install this on test and training databases. It includes several schemas with varying degree of complexity.

• Oracle Database Vault: Basically, this option locks down the database to extreme measure, disallowing activities we take for granted in a normal system. It protects your database against your own people, in essence. It significantly creates more management overhead. However, in a system that must remain ultra-secure, it’s the price you pay.

• Oracle JVM: This is the Oracle Java Container for running Java out of the database.

• Oracle Multimedia: This feature extends Oracle’s capabilities to offer better support for multimedia data.

• Oracle Application Express: This is the kind of a development environment that allows you to develop applications in a web-based framework. It runs on top of the database and allows creating hosted applications that can be quite robust.

If you’re testing to get a basic environment up and running, deselect everything. However, if you’re going to work with one or more of these options, install them. Remember that they take space and time during the database creation process — some more so than others.

21. Click Next.

Figure 4-4 shows the screen where you begin choosing the initialization parameters discussed earlier in the chapter. The Memory tab has two options: Typical Settings and Custom Settings.

22. Select the Typical Settings option.

Because we’re talking Oracle 12c, selecting Typical Settings sets the memory target as one large chunk and lets Oracle figure out where everything goes.

23. Click the Sizing tab.

24. Select the block size.

If you get this wrong, your only option is to re-create your database (if the performance problems haven’t gotten you fired).

Figure 4-4: Choosing database initialization parameters.

Keep the following details in mind as you decide on your block size:

• If you’re creating a database that will have many users with smaller quick transactions, go with a block size of 4k.

• If you’re creating a data warehouse-type database with large SQL queries that retrieve heaps of data at once for analysis, choose the largest block size you can. The largest block size you can choose is OS dependent.

• If you’re somewhere in the middle of the first two, go with 8k.

• Make sure the block size is divisible evenly by the OS block size or OS I/O size. You don’t want your OS to read a minimum of 8k but choose a 4k block size. That would waste 4k for every read.

25. Click the Character Set tab.

You can change the character set after creating the database, but it’s time consuming and tedious. Select a character set that will house all the characters that your application may use.

• Database Character Set: For all the standard-language columns in your database. Also encompasses the character set that Oracle messages will display in, and the characters you may use in program code.

• National Character Set: For special datatypes that may house data only used in your applications. For example, what if you work at a primarily English-speaking university and the Greek department wants to create an application to storage indexable, searchable Greek manuscripts? No programming or database message will be displayed in Greek.

• Unicode Character Sets: Select this option if you’re going to support multiple languages.

26. Click the Connection Mode tab.

You have two options:

• Dedicated Server Mode

• Shared Server Mode

We discuss this in Chapter 2. Most current systems use dedicated server connections. In most cases, we recommend starting that way. If memory is constantly running short (while at the same time supporting thousands of users), investigate shared server configuration.

The All Initialization Parameters button lets you adjust all parameters discussed earlier in the chapter (as well as others we didn’t); see Figure 4-5 for the All Initialization Parameters screen. By default, the screen shows only what Oracle considers basic parameters. Oracle considers some parameters advanced. You don’t need them, but if you’re curious, click the Show Advanced Parameters button.

Figure 4-5: The All Initialization Parameters page lets you make adjustments.

27. Breathe.

You’re almost done.

28. Click the Customize Storage Button to make storage adjustments.

Now is a good time to make sure the files spread across multiple mount points. Click each menu: Controlfile, Tablespaces, Datafiles, and Redo Log Groups. Change the directories (on the right) so they’re not all in the same place.

As you can see in Figure 4-6, the screen lists the file and storage objects. The screen currently shows the Controlfile choices.

About file locations: In the past, we’ve encountered problems with some directories not being there when you change where the files are created. Sometimes Oracle complains about permissions.

Make sure the permissions on the directories where you store your files are set for the Oracle user to read and write. For example, if you move a control file to /u01/oradata/dev12c and that directory isn’t there, some systems give an error and the database creation stops.

We create all the directories where files are going to go ahead of time. This might resolve some headaches when you launch the actual database creation.

Figure 4-6: The storage configuration page lists file and storage objects.

29. Click the Close button when you are finished adjusting any files.

30. Decide if you want to save your decisions as a template for future use.

If you think you may create a similar database again, this step is a good idea. You can give it a name and a description.

31. Save everything you did in a set of scripts and decide where you want them.

It’s a good idea to keep these around just in case. Also, if you’re curious about all the scripting work you just avoided by using the DBCA, have a look.

32. Decide whether you want to create the database now.

33. Click Next to go to the summary screen.

34. Click Finish.

A screen shows all the options you just chose and the parameters you set.

The creation status screen appears. A status bar and options also appear. You can watch it go through everything until it’s done.

When the database is complete, a screen shows the details. A Password Management button lets you unlock or change the passwords of the users that were created as part of the options you installed. All users except SYS, SYSTEM, DBSNMP, and SYSMAN are locked.

If you installed the sample schemas, this is a good place to unlock them and reset their passwords.

35. Click Exit then Close to close the DBCA.

Sharing (a) memory

Personally, we think a “typical” memory option is a bit of a misnomer. Nothing is typical about memory settings. It all depends on

The amount of memory you have

The number of databases on the machine

The number of users you’re expecting

The amount of memory your users require

Also, it is quite common to give yourself a starting point and then go from there. Oracle suggests using 40 percent of your memory for the shared and private areas of your database. This is an interesting choice. What if this machine were destined to house ten databases? Hmmm. . . .

Think about how the memory on this machine is going to be shared. These points might help you decide:

Never start with the combination of shared and private memory areas of all your databases on the machine consuming more than half the memory. Therefore, if you have 8GB of memory and will have two databases, both memory_target parameters combined shouldn’t exceed 4GB. This setup gives plenty of room for error.

The reason for this is to make sure you have plenty of room for other processes on the system to run. For each database you add, you may need to go back and resize your memory components to make sure they all fit and there’s enough left-over memory for the day to day operations of the system. This is not a hard and fast rule; we are giving you an example of leaving half the memory available because it is a relatively safe approach. If you know your system and its memory better, you can be more liberal with your memory usage.

If your database is going to be extremely large, figure out how many users will have server sessions at once. Take that number and multiply it by 8MB. Add 2GB for your OS and then add 20 percent more of the available memory. Split what is left over amongst the rest of the databases.

---

---

This still might not be right for you, so Chapter 11 discusses tuning and performance management. Ultimately, the memory you need boils down to monitoring and adjusting. We wanted to give you a starting point. It is one of the most common user questions yet is difficult to quantify without real application environment data.

Doing a Post-Creation Check-Up

When everything is complete, we recommend that you log in to your database for the first time and check everything out.

Look in the directories where the files were supposed to go.

Check your initialization parameters.

  Perform a backup if this is a soon-to-be production database. That way you don’t have to create the entire database again if something goes wrong.