Oracle 12c (12.1.0.2) Create Database: Windows

<- Legacy

Overview

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

1. Create the core database as shown in these instructions.
2. 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. You may wish to review the Post Database Creation section 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.

Procedure

Run the Database Configuration Assistant (DBCA).

Windows

• Start
  • Oracle - OraDB12Home1
    • Configuration and Migration Tools
      • Database Configuration Assistant

NIX

1. xhost +
2. su - oracle
3. export Display=localhost:5 (your IP or VNC session)
4. dbca

 1. Welcome 
    Next
 2. Database Operation
    (x) Create Database
 3. Database Mode
    (x) Advanced Mode
 4. Database Template
    (x) Custom Database
 5. Database Identification
    Global Database Name: DB01
    SID: DB01
 6. Management Options
    [x] Configure Enterprise (EM) Database Express
        EM Database Express Port: 5500
 7. Database Credentials
    (x) Use the Same Administrative Password for All Accounts
        Password: ********
        Oracle Home User Password: ********
 8. Network Configuration
    [x] Create a New Listener
    Name: LISTENER
    Port: 1521
    Home: C:\app\oracle\product\12.1.0.2\dbhome_1
 9. Storage Locations
    Storage Type: File System
    (x) Use Database File Locations from Template

    [x] Specify Fast Recovery Area
        Fast Recovery Area:      C:\oradata\fbr
        Fast Recovery Area Size: 4815 <use default or greater>
    [x] Enable Archiving
        [Edit Archive Mode Parameters]
        [x] Automatic Archiving
            Archive Log File Format:  {DB_NAME}_%T_%S_%r.arc
            Location 1: C:\oradata\DB01\arc
            Location 2: X:\oradata\DB01\arc
10. Database Options
       Database Components
          [x] Oracle JVM
11. Initialization Parameters
       Memory
          (x) Typical Settings
              Memory Size: 2048
              [x] Use Automatic Memory Management
       Sizing
          Block Size: 8192 Bytes
          Processes: 300
       Character Sets
          (x) Use Default
       Connection Mode
          (x) Dedicated Server Mode
12. Creation Options

    Select: Custom Storage Locations
            ===> Make sure to press [Apply] after each change!  <===

    * Control File Locations
      General
         c:\oradata\DB01\ctl
         x:\oradata\DB01\ctl
      Options
        Maximum Datafiles: nnn 
        (Enter the maximum datafiles this database will need.)

        Maximum Instances: 8
        Maximum  Log History: 1
        Maximum Redo Log Files: 16
        Maximum  Log Members: 3

    * Tablespaces
        For Each where possible:
           [x] Use Locally Managed Tablespace
               Allocation: (x) Automatic  
           [x] Use Bigfile Tablespace

    * Datafiles
        [x] (AUTOEXTEND)
            Increment: 10240 KBytes
        (x) Unlimited

      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

     [x] Create Database
     [x]  Save as a Database Template
          Name: Standard

13.  Summary
     Press [Finish] 

     After about a minute you will get the prompt that your template
     creation completed. Press [OK]

     Process runs...

     There is no longer any need to perform a tail -f on the alert log.
     12c has options to allow you to watch the process in real-time via
     the buttons [Activity Log] and [Alert Log]!

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

Post Database Creation

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;

Deferred Segment Creation

In Oracle 11g and later 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

OEM Database Express

12c Oracle did away with dbconsole and put in its place Oracle Enterprise Manager Database Express. To configure OEM Database Express you simply need to run one of the below commands and specify a port.

 For HTTPS: exec dbms_xdb_config.sethttpsport(5500);
 For HTTP:  exec dbms_xdb_config.sethttpport(8080);

To see what ports have been configured you can run one of the below commands.

 For HTTPS: select dbms_xdb_config.getHttpsPort() from dual;
 For HTTP:  select dbms_xdb.getHttpPort() from dual;

To grant users view only access to see the web UI interface you can grant them the role EM_EXPRESS_BASIC.

 grant EM_EXPRESS_BASIC to <user>;

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 for this.

Password Policy Expiration

In 11g and later 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 12g Services

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

1. OracleService{SID}
2. OracleJobScheduler{SID} (If used)
3. OracleOraDB12gHome1TNSListener

Other services that may be initially active include:

• OracleDBConsole{SID}
• OracleOraDb12g_home1ClrAgent
• OracleRemExecServiceV2
• OracleVSSWriter{SID}

APPENDIX - Delete Database

Run the Database Configuration Assistant (DBCA).

1. Select: (x) Delete Database.
2. Delete Database: Ensure database to delete is selected.
3. Management Options: Select Next accepting defaults.
4. Summary: Confirm this is the database you want deleted.
            Then press Finish
5. Process will run...
6. When completed you will see the message:
   "Database deletion completed"

<- Legacy