Creating database using manual method

How to create database using manual method:

Note: Make sure database software is installed before proceeding. use the link  http://orcl11gdba.blogspot.in/2015/06/db-installation-gui.html  to install DB software

Steps involving creation of database are:

1. Set the environment variables

2. Choose an instance and create a pfile for it.

3. Prepare database creation script and execute it.

4. After creation to build dba views RUN scripts in $ORACLE_HOME/rdbms/admin

Step-1: set the below environment variables in bash_pfofile/.cshrc  file

EXPORT ORACLE_SID=database_1

EXPORT ORACLE_HOME=/u01/oracle/app/product/11.2.0.4/dbhome_1

EXPORT PATH=${PATH}:$ORACLE_HOME/bin

echo $ORACLE_SID

echo $ORACLE_HOME

echo $PATH

Step-2: Creating pfile : i choose my database name and instance name as database_1 creating pfile with following parameters.

Create the pfile in dbs location($ORACLE_HOME/dbs/) with init$ORACLE_SID.ora

vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

*.audit_trail='db'

*.compatible='11.2.0'   ---- if it is another version  then mention it in place of 11.2.0

*.control_files='/u01/oracle/app/oradata/pavan/database_1/control.ctl','/u01/oracle/app/oradata/pavan/database_1/flash_recovery_area/database_1/control2.sql'

*.db_block_size=8192

*.db_domain=''

*.db_name='database_1'

*.db_recovery_file_dest='/u01/oracle/app/oradata/pavan/database_1/flash_recovery_area'

*.db_recovery_file_dest_size=5368709120

*.DB_UNIQUE_NAME='database_1'

*.diagnostic_dest='/u01/oracle/app/oradata/pavan/database_1'

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

*.LOG_ARCHIVE_FORMAT='arch_%t_%s_%r.arc'

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.open_cursors=300

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=500m

:wq!

Step-3: Prepare database creation script: Before executing the database creation script make sure all directories are created.

mkdir -p /u01/oracle/app/oradata/pavan/database_1

mkdir -p /u01/oracle/app/oradata/pavan/database_1/flash_recovery_area/

SQL> STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

SQL> CREATE DATABASE database_1

USER SYS IDENTIFIED BY welcome123

USER SYSTEM IDENTIFIED BY welcome123

   LOGFILE GROUP 1 ('/u01/oracle/app/oradata/pavan/database_1/redo01.log') SIZE 100M,

           GROUP 2 ('/u01/oracle/app/oradata/pavan/database_1/redo02.log') SIZE 100M,

           GROUP 3 ('/u01/oracle/app/oradata/pavan/database_1/redo03.log') SIZE 100M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/oracle/app/oradata/pavan/database_1/system01.dbf' SIZE 325M

   SYSAUX DATAFILE '/u01/oracle/app/oradata/pavan/database_1/sysaux01.dbf' SIZE 325M

   DEFAULT TABLESPACE users

      DATAFILE '/u01/oracle/app/oradata/pavan/database_1/users01.dbf'

      SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/oracle/app/oradata/pavan/database_1/temp01.dbf'

      SIZE 20M

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/oracle/app/oradata/pavan/database_1/undotbs01.dbf'

      SIZE 200M AUTOEXTEND ON MAXSIZE UNLIMITED;

/

Step-4: Creating DBA views:  After creating database perform the below command in sql:

os}~ sqlplus / as sysdba

SQL> SHUTDOWN

SQL>STARTUP

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

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

conn system/wecome123

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

SQL> SELECT COUNT (1) FROM TAB;

SQL>SELECT NAME FROM V$DATABASE;

SQL>SELECT STATUS FROM V$INSTANCE;