Legacy

Oracle 11g (11.2.0.x) Create Database: Windows

Overview

What follows are the steps for the creation of standard database for an enterprise environment. It is a standard practice to create your database in two major steps:

Create the core database as shown in these instructions.
Create custom components required by the business mission (tablespaces, settings, schema etc.).

In this way, you can ensure your core database is structurally sound and issue free. Think of it like building a house. The steps outlined here are the foundation.

Ideally you only want to build your database with what will actually be used. This streamlines routine Oracle patches and makes your database more secure. Removing unused components at a later date usually requires running scripts and various manual actions. Also, the removal of some components may require downtime. If creating a database as shown, this generally means to disable: Application Express, Multimedia and Oracle Text (unless they are used).

In the example below the database name DB01 is used. Change as required for your environment. You may wish to review the Configuration Considerations below BEFORE you create your database.

Warning: In Oracle 11g R2 and later the DEFAULT profile has been changed in a way that requires passwords to be reset after 180 days. Make sure to change this if this is something you don't want! This does not seem to be documented very well and has the potential to cause catastrophic mission downtime if not addressed. See Configuration Considerations below to change.

Prerequisites

In 11g and later you must first create and configure the Listener before creating a database.

Procedure

Run the Database Configuration Assistant (DBCA)
Welcome
Next
Operations
(x) Create a Database
Database Templates
(x) Custom Database
Database Identification
Global Database Name: DB01
SID: DB01
Management Options
[x] Configure Enterprise Manager
(x) Configure Database Control for local management.
----
[x] Enable automatic maintenance tasks.
Database Credentials
(x) Use the Same Administrator Password for All Accounts
Database File Locations
Storage Type: File System
(x) Use the Database File Locations from Template
Recovery Configuration
Setting a few File Location Variables may save you some time and increase the reliability of your DB properties.
Ex: {ORADATA1} = O:\oradata

[x] Specify Flash Recover Area
    Flash Recovery Area:      c:\oradata\DB01\fbr
    Flash Recovery Area Size: 4977 (set to default or greater)
[x] Enable Archiving
    Archive Log File Format:  {DB_NAME}_%T_%S_%r.arc
    Archive Log Destination 1 c:\oradata\DB01\arc
    Archive Log Destination 2 x:\oradata\DB01\arc

Database Content

   [x] Enterprise Manager Repository
   [x] Oracle Database Extensions for .NET (if used)

   Standard Database Components
      [x] Oracle JVM
      [x] XML DB (This is required for many internal Oracle components.)

Initialization Parameters

* Memory
  (x) Typical
      Memory (SGA and PGA): 1024
      For larger databases 2048+ are common.
  [x] Use Automatic Memory Management
* Sizing
  Block Size: 8192 Bytes
  If Data Warehousing consider using a larger size if appropriate.
* Processes: 150
  Adjust to meet your required processes/sessions.  Example: 400
* Character Sets
  Use the default
* Connection Mode
  (x) Dedicated Server Mode

Security Settings
Keep the enhanced 11g default security settings (recommended)
Automatic Maintenance Tasks
Enable automatic maintenance tasks.

Database Storage
For Control, Redo and Archive files create as shown.
If not on SAN/RAID multiplex over JBOD (Just a Bunch of Disks).


* Control File Locations
  c:\oradata\DB01
  c:\oradata\DB01\ctl
  x:\oradata\DB01\ctl

* Tablespaces (Always use Locally Managed Tablespaces.) \\
  Use BFTS for all tablespaces.

  Location                           Size (small | medium-large)
  --------------------------------   ---------------------------
  c:\oradata\DB01\dbf\sysaux01.dbf   1024 mb | 2 gb
  c:\oradata\DB01\dbf\system01.dbf   1024 mb | 2 gb
  c:\oradata\DB01\dbf\temp01.dbf      256 mb | 5 gb
  c:\oradata\DB01\dbf\undotbs01.dbf   256 mb | 5 gb
  c:\oradata\DB01\dbf\users01.dbf      25 mb

* Redo Log Groups (minimum 4 groups with two members each)

  File Name   Location              Size
  ----------  -------------------  ------
  redo1a.rdo  c:\oradata\DB01\rdo  100 mb
  redo2a.rdo  c:\oradata\DB01\rdo  100 mb
  redo3a.rdo  c:\oradata\DB01\rdo  100 mb
  redo4a.rdo  c:\oradata\DB01\rdo  100 mb

  File Name   Location             Size
  ----------  -------------------  ------
  redo1b.rdo  x:\oradata\DB01\rdo  100 mb
  redo2b.rdo  x:\oradata\DB01\rdo  100 mb
  redo3b.rdo  x:\oradata\DB01\rdo  100 mb
  redo4b.rdo  x:\oradata\DB01\rdo  100 mb

Creation Options

Create Database
   If this is the first database on system:
   ->  Save as a Database Template
       Name: Standard

   Process runs...

You can tail -f the alert log to monitor the process better:
OS> tail -f: C:\app\oracle\diag\rdbms\{DBNAME}\{DBNAME}
             \trace\alert_{DBNAME}.log

Once completed you will get a window that shows the creation status and OEM connection info.
Select Exit

Configuration Considerations

ADR Retention

To collect enough useful logs but not overwhelm disk space set ADR to below values. This will also significantly speed up SQL queries into ADR.

OS> adrci
adrci> show homes

DB
set homepath diag\rdbms\MySID\MySID;
set control (SHORTP_POLICY = 240);
set control (LONGP_POLICY = 1080);

Listener
cd ..
set homepath diag\tnslsnr\MySID\listener;
set control (SHORTP_POLICY = 72);
set control (LONGP_POLICY = 240);

AWR Snapshot Retention

The Automatic Workload Repository (AWR) takes hourly snapshots of key performance indicators. Oracle's predictive analysis of the database can be improved if this is increased from the default (7 days). Consider retaining this information for at least a month. You can use the following command to do this.

BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(43200,60); END;

Enterprise Manager (AKA: OEM, dbconsole)

The default EM session is set to 20 minutes. To meet security requirements set this as required. The example entry below sets the session timeout to 10 minutes.

# Session Timeout
oracle.sysman.eml.maxInactiveTime=10

Deferred Segment Creation

In Oracle 11g you will sporadically get Quota Errors upon INSERT operations and DDL for tables if you do not set Deferred_Segment_Creation to FALSE. This is not the default so you must make this change to avoid these errors.

 ALTER SYSTEM SET deferred_segment_creation=FALSE SCOPE=both;

Flashback Recovery (FBR)

Oracle University training recommends the FBR area be located in a local directory.
Thus the directory indicated above: c:\oradata\DB01\fbr

Memory Sizing

This is more pertinent to Oracle 10g as 11g does not require separate settings for SGA and PGA.

You can use v$sgainfo to determine how affective you SGA settings are:
```
SELECT * FROM v$sgainfo;
```
You can use v$pgastat to determine how affective you PGA settings are:
```
SELECT * FROM v$pgastat;
```

Password Policy

Passwords should be at least eight characters in length and contain combinations of the following:

Numbers (1, 2, 3...)
Upper case letters
Lower case letters
Special characters (!, @, $, %, *, etc.)

Both Oracle and user DBA accounts should reflect this policy. This includes but is not limited to the accounts SYS, SYSTEM and SYSMAN. Ideally use profiles for this.

Password Policy Expiration

In 11g the DEFAULT policy is now set to expire all passwords after 180 days. To turn this off use the below command: ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Redo Logs

The goal here is to have a log switch about every 20-30 minutes. Use the above values if you are not sure then resize them after analyzing the production load.
In the process of applying major patchsets ideally you will need to have at least 4 redo log groups where each member is at least 100 mb.
On some systems where you suspect disk I/O is inadequate you can create just one redo group and one archive log destination later adding a second as disk I/O proves to be acceptable.

Tablespace Sizing

Import operations and routine Oracle patches may require more capacity for TEMP and UNDO tablespaces than the standard data load. 250 mb for TEMP and UNDO should accommodate these operations in most cases. If not, size these accordingly for your environment.
Disk space permitting, enable auto-extend for all tablespaces and set a max size less than the size of your disk or volume.

Undo Retention

You may wish to set UNDO_RETENTION at this point to help avoid getting "snapshot too old" messages. Common values in this instance range from 21600 (6 hours) to 43200 (12 hours).

ALTER SYSTEM SET UNDO_RETENTION=43200 SCOPE=both;

Windows Resource Monitor

On newer Windows OS's you can use the Resource Monitor which can show you real-time vital information on all your Oracle datafiles. To create shortcut to this use:

%windir%\system32\perfmon.exe /res

Windows 11g Services

In Oracle 11g the following core (essential) services will be active for all databases. Start them in this order and shut them down in reverse order:

OracleService{SID}
OracleJobScheduler{SID} (If used)
OracleMTSRecoveryService
OracleOraDb11g_homeTNS{Listener}
OracleDBConsole{SID}

Other services that may be initially active include:

OracleOraDb11g_home1ClrAgent
OracleRemExecService
Oracle {SID} VSS Windows Service

Common Issues (11.2.0.1.0)

ORA-03137 (TTC protocol error) => (below per 1361107.1) In some instances on Windows installs you may get an ORA-03137 (TTC protocol error) which can be caused by the unpublished bug [1010] [] [] [] [] [] [] []. If this is the case you can use this command to fix it (until you can patch database: 11.2.0.3 or greater): ALTER SYSTEM SET "_optim_peek_user_binds" = false SCOPE=both;
To fix problems using the Data Pump REMAP command apply one off patch 8795792.

<- Legacy