Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle


18 July 2014

Manually create an ASM database

To create a database manually under ASM, it is actually a little easier than with a non asm database - mainly because you don't need to worry about paths to datafiles etc.

Create a minimal init.ora:

control_files = (+DATADG, +FRADG)

undo_management = auto
db_name = CLEAN01
db_block_size = 8192

DB_CREATE_FILE_DEST = +DATADG
DB_RECOVERY_FILE_DEST = +FRADG
DB_RECOVERY_FILE_DEST_SIZE = 10G

Set the Oracle SID:

export ORACLE_SID=CLEAN01

Start the instance and create an spfile from the pfile

sqlplus / as sysdba
create spfile from pfile;
startup nomount

Create the database with a minimal create database statement

create database clean01
  logfile   group 1 ('+DATADG') size 100M,
            group 2 ('+DATADG') size 100M,
            group 3 ('+DATADG') size 100M
  character set          utf8
  national character set utf8;

Create the catalog etc:

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

As System (not SYS) run the following:

@?/sqlplus/admin/pupbld.sql

Create the temporary tablespace

create temporary tablespace temp tempfile '+DATADG' size 500M;

Create the Users Tablespace

CREATE TABLESPACE users DATAFILE '+DATADG' 
SIZE 50M
autoextend on 
maxsize 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Move the SPFile into ASM

create pfile from spfile;
create spfile='+DATADG/CLEAN01/spfileCLEAN01.ora' from pfile;
shutdown immediate;

Now create a file called initAPEX01.ora in the ORACLE_HOME/dbs directory, and put the following contents in it:

spfile='+DATADG/CLEAN01/spfileCLEAN01.ora'

Remember to remove the SPFile from the dbs directory or it will not use the ASM spfile!

Add the Database to /etc/oratab

CLEAN01:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y
blog comments powered by Disqus