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
Daniel Westermann's Blog 