Oracle Database Creation Mannually

Mannual DB Creation:


To create the database mannually, Open a terminal and follow the below commands..

#su - oralce

$.  .bash_profile


Here we should create a parameter file on going to the oracle home database location

$cd  $ORACLE_HOME/dbs

$vi  initprod.ora

db_name = "prod"

control_files = ( /u01/app/oracle/oradata/prod/Control01.ctl, /u01/app/oracle/flash_recovery_area/Control02.ctl )

:wq!


Now goto oracle user location.. ( #su  - oracle )

$cd

$pwd


$vi createdb.sql

CREATE DATABASE prod
   
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prod/redo01.log') SIZE 100M,
                      GROUP 2 ('/u01/app/oracle/oradata/prod/redo02.log') SIZE 100M,
                      GROUP 3 ('/u01/app/oracle/oradata/prod/redo03.log') SIZE 100M

   DATAFILE '/u01/app/oracle/oradata/prod/system01.dbf' SIZE 325M REUSE
   
   SYSAUX DATAFILE '/u01/app/oracle/oradata/prod/sysaux01.dbf' SIZE 325M REUSE
   
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/prod/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/u01/app/oracle/oradata/prod/temp01.dbf'
      SIZE 20M REUSE
   
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/prod/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;


:wq!



From the same location, create another file named as postdb.sql


$vi  postdb.sql

spool  post1.log
@?/rdbms/admin/catlog.sql
@?/rdbms/admin/catproc.sql
spool off

alter user system identified by manager account unlock
connect system/manager

spool post2.log
@?/sqlplus/admin/pupbld.sql
spool off


:wq!



Here in the above code

catlog.sql  creates the system default sql objects like Tables,Views,Indexes..etc.

catproc.sql creates the system default pl/sql objects like Procedures, Functions...etc.

And finally  pupbld.sql file creates the default User Profiles.. see in user management concept later..

And spool files post1.log  and post2.log files record the background process while executing the postdb.sql script.

For checking purpose only we take those two files, if any errors occurred whille executing the postdb.sql file..

If you want open those files post1.log and post2.log and check the process what will be taken...

#su - oracle

$ls

$cat post1.sql

$cat post2.sql






Now Open the database in Nomount stage


$export ORACLE_SID=prod

$sqlplus / as sysdba


sql>


sql>startup nomount


sql>@createdb.sql


sql>@postdb.sql



And now Open the database and check the status and files:

Open another Terminal and follow these below specified commands..


[oracle@ram ~]$ export ORACLE_SID=prod
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun May 1 15:50:11 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
prod             OPEN

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

PROD      READ WRITE

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/prod/control01.ctl

/u01/app/oracle/flash_recovery_area/prod/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/prod/redo03.log

/u01/app/oracle/oradata/prod/redo02.log

/u01/app/oracle/oradata/prod/redo01.log

SQL>     

SQL> 

SQL> 

SQL> select name from v$archived_log;

no rows selected

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           2

SQL>