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).