How to create database manually on linux

Create a necessary directories for admin, oradata and recovery destination.

mkdir -p /u01/app/oracle/admin/prod/adump

mkdir -p /u01/app/oracle/oradata/prod/

mkdir -p /u01/app/oracle/flash_recovery_area/prod

mkdir -p /u01/app/oracle/diag

After 11g your diagnostic location should be in Oracle base directory.

create a script called db_create.sql with the below

I am going to create a database called prod.

CREATE DATABASE "prod"

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/prod/redo1.log' SIZE 10M,

GROUP 2 '/u01/app/oracle/oradata/prod/redo2.log' SIZE 10M,

GROUP 3 '/u01/app/oracle/oradata/prod/redo3.log' SIZE 10M

DATAFILE

'/u01/app/oracle/oradata/prod/system.dbf' size 1000m,

'/u01/app/oracle/oradata/prod/usr04.dbf' size 100m

sysaux datafile '/u01/app/oracle/oradata/prod/sysaux.dbf' size 100m

undo tablespace undotbs

datafile '/u01/app/oracle/oradata/prod/undo.dbf' size 50m

CHARACTER SET US7ASCII

;

Set the environment. 

$export ORACLE_SID=prod.

Create a init file in ORACLE_HOME/dbs directory. Your init file name should be initprod.ora

prod.__db_cache_size=88080384

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=62914560

prod.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/prod/adump'

*.compatible='11.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl','/u01/app/oracle/oradata/prod/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='prod'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/prod'

*.db_recovery_file_dest_size=2147483648

*.diagnostic_dest='/u01/app/oracle/diag'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=202375168

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=609222656

*.undo_management='AUTO'

*.undo_tablespace='undotbs'

Once your init file is created, Start the database in  nomount stage.

Once the instance is started, Run the db_create.sql script.

SQL>@db_create.sql;

Database created.

After your database creation, Your database will come up in default log mode, which is noarchivelog mode.

for make your database in archivelog mode, shutdown your database and start in mount state to change the database to archive log mode.

once the database came up in mount mode,

SQL> alter database archivelog;

SQL> alter database open;

Post installation we should run the below script.

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

SQL> @?/rdbms/admin/catblock.sql;

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

Run this pupbld.sql script as system user.

SQL> conn system/manager

connected

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

Now your database is ready to use.

Happy Learning..!!

Cheers..!!