Manual Oracle Database Creation

Instance Related with : Database Related with:

pfile Datafiles
password file Logfiles
sga(db-buffer,log buffer) Control Files
Background process.

Step to Create database manually....

I am keeing my database name is ORCL ok......

•  Create pfile and setting up required parameter.
• create pfile using  /u01/app/orcl/product/11.2.0/dbhome/dbs/init.ora

1. cd /u01/app/orcl/product/11.2.0/dbhome_1/dbs/
2. cp init.ora initorcl.ora

 hosted with ❤ by GitHub

• Modify this file.

1. db_name='orcl'
2. db_unique_name='orcl'
3. sga_target=1g
4. audit_file_dest='/u01/app/orcl/admin/orcl/adump'
5. audit_trail='db'
6. compatible='11.2.0.4.0'
7. control_files='/u01/app/orcl/oradata/orcl/CONTROL01.CTL','/u01/app/orcl/fast_recovery_area/orcl/CONTROL02.CTL'
8. db_block_size=8192
9. db_domain='orcl.com'
10. db_recovery_file_dest='/u01/app/orcl/fast_recovery_area'
11. db_recovery_file_dest_size=2g

 hosted with ❤ by GitHub

• Startup database using pfile.

1. sqlplus / as sysdba 
2. startup nomount

When oracle database is start in nomount mode check background process.

1. check alert log file.
2. select program from v$process;

 hosted with ❤ by GitHub

• Create database Script file and save it.(data.sql)

• create database orcl datafile '/u01/app/orcl/oradata/orcl/system01.dbf' size 100m autoextend on next 1m
  sysaux datafile '/u01/app/orcl/oradata/orcl/sysaux01.dbf' size 100m autoextend on next 1m
  undo tablespace undotbs1 datafile '/u01/app/orcl/oradata/orcl/undotbs01.dbf' size 40m
  default temporary tablespace temp tempfile '/u01/app/orcl/oradata/orcl/temp01.dbf' size 40m
  default tablespace users datafile '/u01/app/orcl/oradata/orcl/users01.dbf' size 100m autoextend on next 1m
  logfile
  group 1 '/u01/app/orcl/oradata/orcl/redo01.log' size 50m,
  group 2 '/u01/app/orcl/oradata/orcl/redo02.log' size 50m,
  group 3 '/u01/app/orcl/oradata/orcl/redo03.log' size 50m
  /

 hosted with ❤ by GitHub

• Run these Script on sql prompt.

1. Database is in Nomount state
2. sql > @data.sql
3. select tablespace_name,file_name from dba_data_files;
4. select tablespace_name,file_name from dba_temp_files;
5. select * from v$logfile;

 hosted with ❤ by GitHub

• Finally have to run the following script:

• • This command automatically create entire database related information related to the database( i.e. Table, view,synonym etc.)

               @$ORACLE_HOME/rdbms/admin/catalog.sql

  
  
  

  • This command automatically create all default packages and procedures related to the database.

  
  

                @$ORACLE_HOME/rdbms/admin/catproc.sql

   hosted with ❤ by GitHub
  After this script

  
  

  Sql > Select db_name,open_mode from v$database;

            DB_NAME   OPEN_MODE

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

             ORCL         READ WRITE