… and ( almost ) finally, create the database

March 28, 2012 — Leave a comment

Before I start with the database creation, there remain some steps to be performed. Because I will place the files inside the ASM instance there is no need to create separate filesystems for datafiles, redologs, control-files. But still I want a custom location for the audit logs, pfile and diagnostic destination. Let’s prepare this:

su -
mkdir /oradata/DB112
chown oracle:dba /oradata/DB112
su - oracle
mkdir -p /oradata/DB112/admin/pfile
mkdir /oradata/DB112/admin/adump

The easiest, and probably the best way for most environments is to go with the defaults. Let’s start by creating a minimal pfile…

su - oracle
echo "*.audit_file_dest='/oradata/DB112/admin/adump'
*.control_files='+DB112_DATA_DG','+DB112_ARCH_DG'
*.db_block_size=8192
*.db_name='DB112'
*.instance_name='DB112'
*.sga_max_size=256M
*.sga_target=256M
*.undo_tablespace='undotbs1'
*.diagnostic_dest=/oradata/DB112/admin" > /oradata/DB112/admin/pfile/initDB112.ora

… check that we can initially start the instance…

sqlplus / as sysdba
startup force nomount pfile=/oradata/DB112/admin/pfile/initDB112.ora

… create the server parameter file:

create spfile='/oradata/DB112/admin/pfile/spfileDB112.ora' from pfile='/oradata/DB112/admin/pfile/initDB112.ora';

… create a link to the server parameter file ( for oracle to find the file )

!ln -s /oradata/DB112/admin/pfile/spfileDB112.ora $ORACLE_HOME/dbs/spfileDB112.ora

… restart without specifying a pfile to see if the spfile gets used:

startup force nomount
show parameter pfile;

… and create the database

create database DB112
logfile
group 1 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 2 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 3 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse
character set utf8
national character set AL16UTF16
datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M extent management local
default temporary tablespace TEMP tempfile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M
sysaux datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M
default tablespace USERS datafile '+DB112_DATA_DG' size 4m reuse autoextend on next 8m maxsize 16M
undo tablespace UNDOTBS1 datafile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M;

… create the dictionary:

@?/rdbms/admin/catalog.sql

… and the plsql stuff:

@?/rdbms/admin/catproc.sql

… create the help tables for sqlplus and populate it:

@?/sqlplus/admin/help/hlpbld.sql helpus.sql
shutdown immediate;
exit;

Done. Database created. What else ? Right, register the database with the Cluster Stack and create a listener:

srvctl add database -d DB112 -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfileDB112.ora
srvctl add listener -l listener_DB112 -p 1521 -o $ORACLE_HOME
touch chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
chmod 770 /opt/oracle/product/base/11.2.0.3/
chmod 770 /opt/oracle/product/base/11.2.0.3/dbs
chmod 770 /opt/oracle/product/base/11.2.0.3/network/
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
srvctl start listener -l listener_DB112
srvctl start database -d DB112

Please notice: The chmods are dirty tricks to avoid CRS-5010 issues. The reason for the CRS-5010 messages is that the cluster stack runs under a different user ( grid in this case ) and is not able to update the files. Maybe there is a fix in the future.

Let’s check the resources and do a reboot to check if all comes up again:

su - grid
crsctl status resource -t
su -
reboot
su - grid
crsctl status resource -t

You should see that the resources are up and running, something like:

+ASM@oracleplayground.fun grid:/home/grid $ crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DB112_ARCH_DG.dg
ONLINE ONLINE oracleplayground
ora.DB112_DATA_DG.dg
ONLINE ONLINE oracleplayground
ora.LISTENER_ASM.lsnr
ONLINE ONLINE oracleplayground
ora.LISTENER_DB112.lsnr
ONLINE ONLINE oracleplayground
ora.asm
ONLINE ONLINE oracleplayground Started
ora.ons
OFFLINE OFFLINE oracleplayground
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE oracleplayground
ora.db112.db
1 ONLINE ONLINE oracleplayground Open
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE oracleplayground

For your convenience, here the complete database creation script:

echo "*.audit_file_dest='/oradata/DB112/admin/adump'
*.control_files='+DB112_DATA_DG','+DB112_ARCH_DG'
*.db_block_size=8192
*.db_name='DB112'
*.instance_name='DB112'
*.sga_max_size=256M
*.sga_target=256M
*.undo_tablespace='undotbs1'
*.diagnostic_dest=/oradata/DB112/admin" > /oradata/DB112/admin/pfile/initDB112.ora
sqlplus / as sysdba
startup force nomount pfile=/oradata/DB112/admin/pfile/initDB112.ora
create spfile='/oradata/DB112/admin/pfile/spfileDB112.ora' from pfile='/oradata/DB112/admin/pfile/initDB112.ora';
!ln -s /oradata/DB112/admin/pfile/spfileDB112.ora $ORACLE_HOME/dbs/spfileDB112.ora
startup force nomount
show parameter pfile;
create database DB112
logfile
group 1 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 2 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 3 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse
character set utf8
national character set AL16UTF16
datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M extent management local
default temporary tablespace TEMP tempfile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M
sysaux datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M
default tablespace USERS datafile '+DB112_DATA_DG' size 4m reuse autoextend on next 8m maxsize 16M
undo tablespace UNDOTBS1 datafile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
shutdown immediate;
exit;
srvctl add database -d DB112 -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfileDB112.ora
srvctl add listener -l listener_DB112 -p 1521 -o $ORACLE_HOME
touch chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
chmod 770 /opt/oracle/product/base/11.2.0.3/
chmod 770 /opt/oracle/product/base/11.2.0.3/dbs
chmod 770 /opt/oracle/product/base/11.2.0.3/network/
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
srvctl start listener -l listener_DB112
srvctl start database -d DB112
Advertisements

No Comments

Be the first to start the conversation!

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