//
you're reading...
Administration

Manually creating an Oracle Database and EM DBconsole

This practice is performed with Oracle Database 10g R2 on Windows XP SP2 installed on VMware workstation with 1GB of memory. However, this practice is performed on Oracle Database 10g, the same step is also valid for Oracle Database 11g manual creation of database. I’ll point out if any modification is needed so you can use following steps to create database and configure DBconsole on Oracle 10g as well as 11g Database.

This practice assumes you have installed oracle database software binary through OUI that means you choose option to install Software Only.

Now let’s Start.

DATABASE CREATION

You have two ways to create your database:

  • Using Database Configuration Assistant [DBCA]
  • Using Command Line CRAETE DATABASE Command

I am going to use second option to create a Single Instance Database called PROD. Followings are the steps, which I used to create database.

Step 1: Set your OS variable [e.g. ORACLE_SID, ORACLE_HOME, and PATH]

This practice assumes that you have already installed oracle binaries using OUI. When we installed binaries through OUI; it adds the ORACLE_HOME, ORACLE_SID entry to windows registry and ORACLE_HOME\bin entry to PATH variable.

To set ORACLE_SID (name of the database you are going to create), use

  C:\>set ORACLE_SID=PROD

Step 2: Create Initialization File [init.ora]
Before starting an oracle instance we have to create an initialization file which is read by instance when it starts and used to configure features and parameter available to it. I used the following parameter to create init.ora file using text editor:

This initialization file is used for Oracle Database 10g:

# this is the directory where database will write audit records if
# AUDIT_TRAIL parameter is set to OS.
# Default = ORACLE_HOME\rdbms\audit
  audit_file_dest=E:\oracle\product\10.2.0\admin\prod\adump

# an alert.log file puts by oracle in this location
  background_dump_dest=E:\oracle\product\10.2.0\admin\prod\bdump

  compatible=10.2.0.1.0

# these files contains the critical information about database
  control_files= ("E:\oracle\product\10.2.0\oradata\prod\control01.ctl",
                 "E:\oracle\product\10.2.0\oradata\prod\control02.ctl",
                 "E:\oracle\product\10.2.0\oradata\prod\control03.ctl")

# set the standard size of db block
  db_block_size=8192

# maximum number of block read by oracle during full table scan
  db_file_multiblock_read_count=16

  db_name=prod

# default location of FRA
  db_recovery_file_dest=E:\oracle\product\10.2.0\flash_recovery_area

# space used by FRA
  db_recovery_file_dest_size=2048M

# number of open cursors a single session can have
  open_cursors=300
  job_queue_processes=10

# maximum size of SGA
  sga_target=300M

# size of total PGA memory allocation
  pga_aggregate_target=100M

# maximum number of OS process that can connect to instance
  processes=150

# checks whether the instance uses any password file for
# authentication and how many
# database can use the password file
  remote_login_passwordfile=EXCLUSIVE

# where SQL trace file will be sent
  user_dump_dest=E:\oracle\product\10.2.0\admin\prod\udump

# specify tablespace that manages UNDO segments
  undo_management=AUTO
  undo_tablespace=UNDOTBS1

Following init.ora file I used for Oracle Database 11g:

# this is the directory where database will write audit records if
# AUDIT_TRAIL parameter is set to OS.
# Default = ORACLE_HOME\rdbms\audit
  audit_file_dest=E:\oracle\product\10.2.0\admin\prod\adump

  compatible=11.2.0.0.0

# these files contains the critical information about database
  control_files= ("E:\app\ether\oradata\prod\control01.ctl",
                  "E:\app\ether\oradata\prod\control02.ctl",
                  "E:\app\ether\oradata\prod\control03.ctl")

# set the standard size of db block
  db_block_size=8192

# maximum number of block read by oracle during full table scan
  db_file_multiblock_read_count=16

  db_name=prod

# default location of FRA
  db_recovery_file_dest=E:\app\ether\flash_recovery_area

# space used by FRA
  db_recovery_file_dest_size=2048M

# With Oracle Database 11g R1, this parameter contains the value of
# ADR base directory. Automatic Diagnostic Repository (ADR) home
# directory which is <diagnostic_dest>/diag/rdbms//
# contains the all diagnostic information e.g. alert logs, trace file
# core files and incident files. for that particular instance.
# so you don't have to specify background_dump_dest, user_dump_dest
# and core_ddump_dest parameter.
  DIAGNOSTIC_DEST=E:\app\ether

# number of open cursors a single session can have
  open_cursors=300
  job_queue_processes=10

# size of total memory allocation
  memory_target=600M
  memory_max_target=600M

# maximum number of OS process that can connect to instance
  processes=150

# checks whether the instance uses any password file for
# authentication and how many database can use the password file
  remote_login_passwordfile=EXCLUSIVE

# specify tablespace that manages UNDO segments
  undo_management=AUTO
  undo_tablespace=UNDOTBS1

NOTE:

  • DB_NAME is the only mandatory parameter.
  • Oracle Database 11g introduced new feature called Automatic Memory Management (AMM). If AMM is enabled using MEMORY_TARGET and MEMORY_ MAX_TARGET parameter, Oracle automatically manages SGA and PGA components for you. But if you want you can still use SGA_TARGET, PGA_AGGREGATE_TARGET in initialization file.

Step 3: Create Required Directories.
Make sure all the directories referenced in init.ora file must be created before proceeding further.

Step 4: Choose a Database Authentication Method
There are two authentication methods we can use:

  • Password file
  • OS authentication

If you want to use Password Authentication, you can create password file using the ORAPWD utility.
For using OS authentication user must be belonged to a user account that is a member of member of OS user group. In addition to it, you must add the following entry to sqlnet.ora file in ORACLE_HOME\NETWORK\admin.

  SQLNET.AUTHENTICATION_SERVICES=(NTS)

I used OS authentication method.

Step 5: Create an instance
On windows platform we have to manually create an instance it is not already exist. oradim used to create an instance by creating a new windows service. To create an instance use the following command:

  oradim -NEW -SID -STARTMODE MANUAL –PFILE

  C:\>oradim -NEW -SID prod -STARTMODE MANUAL -PFILE E:\oracle\product\10.2.0\db_1\database\initprod.ora
  Instance created.

This command only creates the instance. If you want to use AUTO start mode that means instance will be start automatically when the computer restarts, use the following command after completing all the steps mentioned in this practice.

  oradim -EDIT -SID sid -STARTMODE AUTO [-PFILE filename | -SPFILE]

Use -SPFILE argument if you want the instance to read an SPFILE after instance restart.

Step 6: Connect to instance and create spfile
Login to SQL* Plus using SYSDBA privilege.

  C:\>sqlplus /nolog

  SQL>CONNECT / AS SYSDBA
  Connected to an idle instance.

Now create spfile. Using Server Parameter file (spfile), we can change parameter with ALTER SYSTEM command while the instance is running whereas instance must be shut down and restarted before it takes effect the changes are made to the PFILE. To create spfile from pfile use the following:

  SQL>CREATE SPFILE FROM PFILE;
  File created.

Since I’m using the default location of PFILE [On Windows, the default directory is ORACLE_HOME\database and on UNIX, ORACLE_HOME/dbs directory], I don’t have to specify the location.

Step 7: Create Database
Start the database in NOMOUNT Mode and issue the CREATE DATABASE command. Since we don’t have database created yet, we use NOMOUNT mode to start the instance. When started in this mode, the parameter file is read and background processes and memory structures are initiated but they are not attached to database.

  SQL>STARTUP NOMOUNT

  ORACLE instance started.
  Total System Global Area 314572800 bytes
  Fixed Size               1247696 bytes
  Variable Size            96470576 bytes
  Database Buffers         213909504 bytes
  Redo Buffers             2945024 bytes

The following script I used to create PROD database on Oracle 10g.

CREATE DATABASE PROD
  USER sys IDENTIFIED BY mysysdb
  USER system IDENTIFIED BY mysysdb
    maxlogfiles 16
    maxlogmembers 3
    maxdatafiles 100
    maxinstances 1
    maxloghistory 1
    character set US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE 'E:\oracle\product\10.2.0\oradata\prod\system01.dbf'
    SIZE 700m REUSE
    SYSAUX DATAFILE 'E:\oracle\product\10.2.0\oradata\prod\sysaux01.dbf'
    SIZE 700m REUSE
    DEFAULT TABLESPACE users DATAFILE 'E:\oracle\product\10.2.0\oradata\prod\users01.dbf'
    SIZE 500m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    UNDO TABLESPACE undotbs1 DATAFILE 'E:\oracle\product\10.2.0\oradata\prod\undotbs01.dbf'
    SIZE 800m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'E:\oracle\product\10.2.0\oradata\prod\temp01.dbf'
    SIZE 400m REUSE
    LOGFILE GROUP 1
    ('E:\oracle\product\10.2.0\oradata\redo01a.log',
    'E:\oracle\product\10.2.0\oradata\redo01b.log') SIZE 100m,
    GROUP 2
    ('E:\oracle\product\10.2.0\oradata\redo02a.log',
    'E:\oracle\product\10.2.0\oradata\redo02b.log' ) SIZE 100m,
    GROUP 3
    ('E:\oracle\product\10.2.0\flash_recovery_area\redo03a.log',
    'E:\oracle\product\10.2.0\flash_recovery_area\redo03b.log' ) SIZE 100m;

Description of the CREATE DATABASE Command:

 LINE 1. Name of the database.
 LINE 2-3. Password specified for user account SYS and SYSTEM.
 LINE 4-8. Specify setting for log files, data files etc.
 LINE 9-10. Character sets used by the database.
 LINE 11. System tablespace should be locally managed.
 LINE 12. SYSTEM tablespace created with one datafile of 700M.
 LINE 14. SYSAUX tablespace created with one datafile of 700M.
 LINE 16. Default clause creates and makes USERS tablespace as default tablespace for the database.
 LINE 18. Creates UNDO tablespace used to store undo data for the database if UNDO_MANAGEMENT set to AUTO.
 LINE 20. Creates default temporary tablespace for the database.
 LINE 22-30. Create at least three redo log groups with two members in each groups.

Save the script in a file and run from SQL prompt

  SQL>@c:\temp\create.sql
  Database created.

The following script I used to create PROD database on Oracle 11g.

CREATE DATABASE PROD
  USER sys IDENTIFIED BY mysysdb
  USER system IDENTIFIED BY mysysdb
    maxlogfiles 16
    maxlogmembers 3
    maxdatafiles 100
    maxinstances 1
    maxloghistory 1
    character set US7ASCII
    NATIONAL CHARACTER SET AL16UTF16
    EXTENT MANAGEMENT LOCAL
    DATAFILE 'E:\app\ether\oradata\prod\system01.dbf'
    SIZE 700m REUSE
    SYSAUX DATAFILE 'E:\app\ether\oradata\prod\sysaux01.dbf'
    SIZE 700m REUSE
    DEFAULT TABLESPACE users DATAFILE 'E:\app\ether\oradata\prod\users01.dbf'
    SIZE 500m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    UNDO TABLESPACE undotbs1 DATAFILE 'E:\app\ether\oradata\prod\undotbs01.dbf'
    SIZE 800m REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'E:\app\ether\oradata\prod\temp01.dbf'
    SIZE 400m REUSE
    LOGFILE GROUP 1
    ('E:\app\ether\oradata\redo01a.log',
    'E:\app\ether\oradata\redo01b.log') SIZE 100m,
    GROUP 2
    ('E:\app\ether\oradata\redo02a.log',
    'E:\app\ether\oradata\redo02b.log' ) SIZE 100m,
    GROUP 3
    ('E:\app\ether\flash_recovery_area\redo03a.log',
    'E:\app\ether\flash_recovery_area\redo03b.log' ) SIZE 100m;

You can create additional tablespace according to your need.

Step 8: Create Data Dictionary Views
Oracle provided basically two important scripts catalog.sql and catproc.sql that must be run right after you have created the database to built data dictionary. These scripts automatically created when we install Oracle binaries. Run following commands as SYS user.

  SQL>@?/rdbms/admin/catalog.sql
  SQL>@?/rdbms/admin/catproc.sql

Run the following command as SYSTEM user account to create product_user_profile table, which is required for SQL* Plus.

  SQL>@?/sqlplus/admin/pupbld.sql

You can run additional scripts to install additional features and option. Some other important scripts that you can use:

  SQL>@?/rdbms/admin/utltkprf.sql
  SQL>@?/rdbms/admin/utlxplan.sql
  SQL>@?/rdbms/admin/utlpwdmg.sql
  SQL>@?/rdbms/admin/utlbstat.sql
  SQL>@?/rdbms/admin/utlestat.sql
  SQL>@?/rdbms/admin/owminst.plb
  SQL>@?/javavm/install/initjvm.sql
  SQL>@?/xdk/admin/initxml.sql
  SQL>@?/rdbms/admin/utldtree.sql
  SQL>@?/rdbms/admin/catblock.sql
  SQL>@?/rdbms/admin/caths.sql
  SQL>@?/xdk/admin/xmlja.sql
  SQL>@?/rdbms/admin/catjava.sql
  SQL>@?/rdbms/admin/catexf.sql

For more Information:
Optional Data dictionary SQL script for Oracle 10g
Optional Data Dictionary SQL script for Oracle 11g

MANUAL CONFIGURATION OF EM DATABASE CONTROL

To configure Enterprise Manager Database Control through command line, we use Enterprise Manager Configuration Assistant (EMCA).

Make sure Listener is configured and tnsnames.ora identifies the service and listener.ora file contains the descriptions of the services or any configuration that is required.

Step 1: Login to SQL* Plus with SYS user and unlock the user account DBSNMP.

C:\>sqlplus SYS as sysdba
Password:

SQL>SELECT username, account_status FROM DBA_USERS;

The user account DBSNMP must be locked and expired.

SQL>ALTER USER DBSNMP IDENTIFIED BY mysysdb_1 ACCOUNT UNLOCK;
User altered.

NOTE:
Step 1 is not mandatory to run. You can start from the Step 2

Step 2: Go to command prompt of windows and issue the following command and wait for 10-15 minutes.

C:\>emca -config dbcontrol db -repos create

STARTED EMCA at 04-Aug-2012 19:55:40
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: prod
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
--------------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME . . . . . . . . . . E:\oracle\product\10.2.0\db_1

Database hostname . . . . . . . . . . . db
Listener port number . . . . . . . . . . . 1521
Database SID . . . . . . . . . . . prod
Email address for notifications . . . . . . . . . .
Outgoing Mail (SMTP) server for notifications . . . . . . . . . .

--------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
04-Aug-2012 19:56:11 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at E:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\prod\emca_2012-08-04_07-55-39-PM.log.
04-Aug-2012 19:56:14 oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
04-Aug-2012 19:59:04 oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
04-Aug-2012 19:59:11 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while). . .
04-Aug-2012 20:00:01 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
04-Aug-2012 20:00:02 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://db:1158/em <<<<<<<<<<< Enterprise Manager configuration completed successfully FINISHED EMCA at 04-Aug-2012 20:00:02

C:\>

Now your Database Control is configured and check the provided URL to access DB Control homepage.

References:
Oracle Database 10g Administrator’s Guide

Oracle Database 11g Administrator’s Guide

Advertisements

Discussion

One thought on “Manually creating an Oracle Database and EM DBconsole

  1. Awesome ….!!!!!!!!!!!!!!!!!!!!! update it frequently…..!!!!!!!!!!!

    Posted by Alok Kumar | August 25, 2012, 21:05

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: