Oracle Database Creation: A Manual Approach

So here are we back on track again. After taking a short break from the regular course on installation, let us continue with the database creation section.

We have already gone through the Oracle Installation Part in the previous blog. If you have missed it. Kindly go through these links:

Installation Guide Using OUI- Graphical way

Installation guide Using Silent Installation: Command-line Way

Done with the Oracle Software Installation the next steps comes up as creating the database, configuring listeners and tnsnames and other network and security options.

Lets start with the Database Creation Part and then follow the others.

Well first and foremost question that must have aroused in your mind is that why go on manually creating the database rather than using the DBCA which is the best and the most easy way on creating and configuring the database. Well the answer lies within the past blogs only. As I said DBCA can be best while accessing the GUI and while the parameters can also be set in responseFile during the SILENT installation, its always a good practice to create the Database Manually after the software installation as it gives more control over the parameters than the other tools.

In the real scenario, the database needs to be customized according to the environment and the customer needs. Here manually creating the DB can give you edge over the DBCA. Also being a DBA you need to know how and what are the essentials for the DB creation.

Done with all the blah blah.. Lets get straight to the point of DB Creation.

The most important part for the DB instance creation is PFILE or Server Parameter File. This file defines all the instance related parameters like the control file names with destination, destination to store various logs and trace files, instance name, DB name, Memory Related parameters like SGA and PGA, buffer sizes and cache sizes. Well better seen than just reading. Here is a sample PFILE for starting an Oracle 10g instance.

//**************************************************************************

*.aq_tm_processes=1
*.archive_lag_target=900
*.background_dump_dest='/oracle/admin/prtp/bdump'
*.compatible='10.2.0.2.0'
*.control_files='/oradata1/prtp/control-file/control01.ctl','/oradata2/prtp/control-file/control02.ctl','/oradata3/prtp/control-file/control03.ctl'
*.core_dump_dest='/oracle/admin/prtp/cdump'
*.cursor_sharing='SIMILAR'
*.cursor_space_for_time=TRUE
*.db_block_size=8192
*.db_cache_size=4838860800
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_flashback_retention_target=0
*.db_name='prtp'
*.event='10511 trace name context forever, level 2'
*.fast_start_mttr_target=600
*.instance_name='prtp'
*.java_pool_size=152428800
*.job_queue_processes=10
*.large_pool_size=652428800
*.log_archive_dest='/archive/archive-logs/'
*.log_archive_format='arc_%t_%s_%r.arc'
*.open_cursors=5000*.open_links_per_instance=30
*.open_links=30
*.pga_aggregate_target=22619430400
*.processes=3250
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=FALSE
*.sessions=3500
*.sga_max_size=24294967296
*.sga_target=24294967296
*.shared_pool_size=719430400
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=12000
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/prtp/udump'
//**************************************************************************

Please note this is just sample file and the parameter values may differ depending upon the user requirements.

You need to store this file at the location: $ORACLE_HOME/dbs/initprtp.ora

While creating the spfile it should be kept in mind that the file name should be created and saved with the instance name in the format: init<ORACLE_SID>.ora. This is mandatory because on the instance STARTUP the Oracle looks for this file in order to start the instance. If not you can find errors during the startup. So its good practice to follow the above recommendation also it becomes easy to manage when there are more than one instance and databases running on the same server (not a good practice in PRODUCTION environment but most the time it is found in the TEST setups).

Once you create the pfile you can start the database in NOMOUNT mode. Note that you have not created the database just the instance hence database cannot be started in MOUNT mode as of now.

bash$ export ORACLE_SID=prtp

bash$ sqlplus "/as sysdba"

sql> STARTUP NOMOUNT;

ORACLE instance started.
 

Total System Global Area 24294967296 bytes
Fixed Size                  1267716 bytes
Variable Size             704645116 bytes
Database Buffers          855638016 bytes
Redo Buffers               15507456 bytes

Once the instance is started you can now create the database. Sample script for the database creation is given below.

//************************

//Database Creation Script
//************************
CREATE DATABASE prtp ARCHIVELOG
MAXLOGFILES 40
MAXLOGMEMBERS 4
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2000
DATAFILE '/oradata1/prtp/datafiles/system01.dbf' SIZE 1024M
SYSAUX DATAFILE '/oradata1/prtp/datafiles/sysaux01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
LOGFILE
GROUP 1 ('/oradata1/prtp/redologs/redo01_1.log','/oradata2/prtp/redologs/redo01_2.log') SIZE 512M,
GROUP 2 ('/oradata1/prtp/redologs/redo02_1.log','/oradata2/prtp/redologs/redo02_2.log') SIZE 512M,
GROUP 3 ('/oradata1/prtp/redologs/redo03_1.log','/oradata2/prtp/redologs/redo03_2.log') SIZE 512M,
GROUP 4 ('/oradata1/prtp/redologs/redo04_1.log','/oradata2/prtp/redologs/redo04_2.log') SIZE 512M,
GROUP 5 ('/oradata1/prtp/redologs/redo05_1.log','/oradata2/prtp/redologs/redo05_2.log') SIZE 512M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/oradata3/prtp/datafiles/undotbs01.dbf' SIZE 6144M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
default temporary tablespace temp tempfile '/oradata4/prtp/datafiles/temp01.dbf' SIZE 6144M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
character set UTF8
national character set UTF8;

The above script will create the database in archive log mode. Keep in mind that from Oracle 10g it is mandatory to specify atleast one redo log group , SYSTEM , SYSAUX and UNDO tablespace. It is a good practice always to specify a default TEMP tablespace because otherwise the SYSTEM tablespace will be used for heavy sorting operations causing performance issues.

You need to keep in mind that although DB can work with one CONTROL file and REDO LOG Group its always good practice to create mirror multiplexed copies of these files on different locations to be on safer side in case of disasters.

Once you create the database, You need to create the internal objects that are required by Oracle for its functioning so you need to run these scripts for the objects creation one by one.

sql> @?/rdbms/admin/catalog.sql
sql> @?/rdbms/admin/catproc.sql
sql> conn system/manager
sql> @?/sqlplus/admin/pupbld.sql

Volllaaa!!!! You are done with the database creation. Now start with creation of your own tablespaces and users to operate the database as per your requirement.

Syntax for Tablespace Creation:

create tablespace <tablespace_name> datafile '<path & filename.dbf>' size <size>m segment space management auto;

Syntax to Create Profile for TEST User
 
      CREATE PROFILE PRO_TEST    LIMIT
   SESSIONS_PER_USER          3500    
   CPU_PER_SESSION            UNLIMITED
   CPU_PER_CALL               UNLIMITED
   FAILED_LOGIN_ATTEMPTS      UNLIMITED ;


Syntax to Create User TEST

      CREATE USER TEST IDENTIFIED BY TEST
   DEFAULT TABLESPACE PRTP_DATA
   TEMPORARY TABLESPACE TMP
   PROFILE PRO_TEST
   ACCOUNT UNLOCK;

Grant Permissions to User TEST user

      GRANT CONNECT TO TEST;
   GRANT RESOURCE TO TEST;
   ALTER USER TEST DEFAULT ROLE ALL;
   GRANT CREATE SESSION TO TEST;
   GRANT UNLIMITED TABLESPACE TO TEST;

Note these are just examples, you can alter the grants as per your requirements.

Done with this you need to create the SPFILE which is the binary version of PFILE and restart your database. Now what is difference between SPFILE and PFILE. Well in simple terms PFILE is static while SPFILE is dynamic. By this I mean that you need not restart your Oracle Instance each and every time to make any changes in the database parameters using spfile to apply them but in terms of PFILE you need to rebounce the instance in order to apply the changes. In production environment it is not feasible to restart the DB instance so frequently so its a good practice to start the DB instance with SPFILE before you go LIVE.

Relax no one is gonna cut your throat for not doing this but missing to do this shows that you are not a good DBA. So better keep in mind this thing because I have many of my colleagues who inspite of years of experience forget to do this and it only comes into scene when we face any issues in production and the parameter values cannot be changed dynamically.

So how to make SPFILE from PFILE. Simple follow the below steps.

bash$ sqlplus "/as sysdba"

sql> create spfile from pfile;

     File Created.

sql> exit

bash$ cd ORACLE_HOME/dbs

Here at this location you will check that the file with name spfileprtp.ora will be created. Well this is the spfile of your currently running pfile.

Now shutdown the instance and point the instance to the spfile you have created.

bash$ sqlplus "/as sysdba"

sql> shu immediate

Database dismounted

Database Closed

sql>exit

bash$ cd  $ORACLE_HOME/dbs

cp initprtp.ora initprtp.ora_bak 

Its always a good practice to create the backups of all the files you make changes so that reverting the changes back is much more easier.

bash$ vi initprtp.ora

spfile='/oracle/ora10g/dbs/spfileprtp.ora'

Save the above changes in the pfile and start the instance.

bash$ sqlplus "/as sysdba"

sql> STARTUP

ORACLE instance started.
 

Total System Global Area 24294967296 bytes
Fixed Size                  1267716 bytes
Variable Size             704645116 bytes
Database Buffers          855638016 bytes
Redo Buffers               15507456 bytes
Database Mounted.
Database Opened.

sql> show paramater spfile
NAME                   TYPE          VALUE
---------------------- -------------------------------- -----------------------
spfile                 string        /oracle/ora10g/dbs/spfileprtp.ora
 
Now the database has started with the SPFILE;

Well this is done and now you are ready to hit the roads with your newly created database.

We will have another session for configuring LISTERNER.ORA,TNSNAMES.ORA and SQLNET.ORA files.

Hope this helps. Your comments are welcome!!!!!!!!!!!!!!!!