35. Manual database creation without the dbca

Database Creation Manually:

Step 1: Add the global database name and home location in /etc/oratab file manually.

Step 2: Set the environment . oraenv. After adding the database name and home location in oratab file it is possible to create the oracle environment with the new databse.

Step 3: Now you have to create the pfile in the location $ORACLE_HOME/dbs.

Step 4: For that go in the location and copy an already existing pfile into another one changing the name according to the correct naming convention.

Step 5: The go into the pfile and do some editing in it.

i. Change the name of the database from the previous database to the new database using vi editor option of %s find and replace

ii. Change the names for capital letter ones too.

iii. Also change the location of the second control file for good practice purpose.

Step 6: Create this locations (the highlighted ones) by opening another terminal. Do not close the editor of the pfile.

Locations of audit file and control files.

only create the directory don't create the file now.

Step 7: Next step is creating a password file. The command is:

orapwd file=orapwflipkart password=WelcomeBack#321 entries=3 force=y

Password has to be complex here or it will not create the file like this.

Entries = 3 mean that this database will have 3 users with sysdba role. Force = y means create the file by force.

Step 8: Login to the sqlplus with the sys user and password which you just created.

Step 9: Start the database in nomount mode.

We can do that since in nomount state the database reads the spfile or pfile to create the System Global Area.

Step 10: Run this script as a .sql file which will create the data files and redo log files

Database Creation Script:

CREATE DATABASE flipkart
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 1
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/FLIPKART/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/FLIPKART/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/FLIPKART/redo03.log') SIZE 50M
DATAFILE '/u01/app/oracle/oradata/FLIPKART/system01.dbf' SIZE 1G EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/FLIPKART/sysaux01.dbf' SIZE 500M
Default Tablespace USERS DATAFILE '/u01/app/oracle/oradata/FLIPKART/users01.dbf' SIZE 100M
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/FLIPKART/undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/FLIPKART/TEMP01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET UTF8
;

Explanation:

CREATE DATABASE flipkart
This line creates the database
MAXLOGFILES 5
This line denotes there will be 3 redo log file groups in the database
MAXLOGMEMBERS 3
This line denotes there will be maximum 3 members in each log file groups.
MAXDATAFILES 200
This line denotes there will be maximum 200 data files in the database
MAXINSTANCES 1
This line denotes there will be maximum 1 instance in the database
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/FLIPKART/redo01.log') SIZE 50M,
This line denotes the location of the log file of group 1
GROUP 2 ('/u01/app/oracle/oradata/FLIPKART/redo02.log') SIZE 50M,
This line denotes the location of the log file of group 2
GROUP 3 ('/u01/app/oracle/oradata/FLIPKART/redo03.log') SIZE 50M
This line denotes the location of the log file of group 3
DATAFILE '/u01/app/oracle/oradata/FLIPKART/system01.dbf' SIZE 1G EXTENT MANAGEMENT LOCAL
This line denotes the location of the system01.dbf data file of this database which will contain the system information
SYSAUX DATAFILE '/u01/app/oracle/oradata/FLIPKART/sysaux01.dbf' SIZE 500M
This line denotes the location of the sysaux01.dbf data file of this database which will contain the database screenshots
Default Tablespace USERS DATAFILE '/u01/app/oracle/oradata/FLIPKART/users01.dbf' SIZE 100M
This line denotes the location of the data file of the default tablespace of all users the database
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/FLIPKART/undotbs01.dbf' SIZE 300M AUTOEXTEND OFF
This line denotes the location of the data file of the undo tablespace of the database
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/FLIPKART/TEMP01.dbf' SIZE 200M REUSE AUTOEXTEND OFF
This line denotes the location of the data file of the default temporary tablespace of the database
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET UTF8
This 2 lines denotes the characters which will be used by the database
;

---

See the files have been created.

Step 11: Now we have to run 2 scripts provided by oracle catalog.sql and catproc.sql.

This ? denotes the location of oracle home. Writing this and writing @/u01/app/oracle... is the same thing.

---

This catalog.sql is creating the data dictionary and all the static and dynamic views we use.

Now lets run the other sql script.

This catproc.sql creates the PLSQL scripts required for performance and tuning purposes.

Step 12: Now we need to enable the archive log. For that shutdown the database and start the database in mount state.

Step 13: Now we can start the database in open mode. Next unlock the system user account and change the password of sys user to easy one for our convinience.

See everything is created.

Note: A view v$instance.

select instance_name, instance_mode, database_type, to_char(startup_time, 'dd-mm-yyyy hh24:mi:ss') from v$instance;

We can see the details of the instance (database we created).