Skip to main content

Create a Database Manually

Below are the manual Steps to create an Oracle Database:


  1. Create an Initialization parameter file. You can copy it from another database or create it using samples provided inside $ORACLE_HOME/dbs/ with the name init.ora. Below is an example of text based parameter file.

*.__db_cache_size=322961408
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
*.__pga_aggregate_target=339738624
*.__sga_target=503316480
*.__shared_io_pool_size=0
*.__shared_pool_size=163577856
*.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/orcl/control01.ctl','/opt/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=842006528
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

2. Create the directory locations mentioned in the parameter file.
3. export ORACLE_SID=<instance_name>
In our case it will "export ORACLE_SID=orcl" as db_name mentioned inside parameter file is set to orcl.
4. create database creation script and save it. Below is the sample script:

CREATE DATABASE mynewdb
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE UNDOTBS1
      DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

5. Ensure that the undo tablespace name mentioned in the script matches with the one mentioned in parameter file.
6. connect to sqlplus as sysdba and create spfile

sqlplus '/as sysdba'
SQL> create spfile from pfile;
File Created

7. Startup instance in nomount mode

SQL> startup nomount

8. Execute the database creation script. Assuming that I saved the script with the name create_db.sql

SQL> @create_db.sql

You should get the message Database Created

If it returns any error then resolve the error, remove any control files or datafiles that are created during the unsuccessful run and re-execute the script.

9. With the above steps database is created but it does not contains any data. There are no objects (system or user) inside the database. 

10. Run the below scripts to create system objects
  • @?/rdbms/admin/catalog.sql - create data dictionary views.
  • @?/rdbms/admin/catproc.sql - run all sql scripts for the procedural option
  • @?/sqlplus/admin/pupbld.sql - To create the PUP (Product User Profile) table Log in to SQL*Plus as the SYSTEM user and run PUPBLD.SQL.
To know more about Product User Profile see the link Product_User_Profile






Comments

Post a Comment

Popular posts from this blog

Configure Oracle ASM Disks on AIX

Configure Oracle ASM Disks on AIX You can use below steps to configure the new disks for ASM after the raw disks are added to your AIX server by your System/Infrastructure team experts: # /usr/sbin/lsdev -Cc disk The output from this command is similar to the following: hdisk9 Available 02-T1-01 PURE MPIO Drive (Fibre) hdisk10 Available 02-T1-01 PURE MPIO Drive (Fibre) If the new disks are not listed as available, then use the below command to configure the new disks. # /usr/sbin/cfgmgr Enter the following command to identify the device names for the physical disks that you want to use: # /usr/sbin/lspv | grep -i none This command displays information similar to the following for each disk that is not configured in a volume group: hdisk9     0000014652369872   None In the above example hdisk9 is the device name and  0000014652369872  is the physical volume ID (PVID). The disks that you want to use may have a PVID, but they must not belong to a volu...
2 comments
Read more

Gitlab installation steps on Redhat Linux

In this blog we will see the steps to install Gitlab on Redhat Enterprise Linux 6. I will be using the virtual machine "gitserver" that I have created on Google Cloud. You can use any server or VM running RHEL 6 and follow these steps. Follow the below steps to install gitlab. Run these steps as root user. # yum install -y curl policycoreutils-python openssh-server cronie # lokkit -s http -s ssh  # yum install postfix  # service postfix start  # chkconfig postfix on  # curl https://packages.gitlab.com/install/repositories/gitlab/gitlab-ee/script.rpm.sh | sudo bash  # EXTERNAL_URL="http://34.69.44.142" yum -y install gitlab-ee  You will see a screen similar to below, once your gitlab installation is successful. You can now access the gitlab console using the http or https url that you provided during the installation, i.e., http://<ip/server_name> http://gitserver.localdomain.com or  http://34.69.44.142 When you open the c...
2 comments
Read more

Load records from csv file in S3 file to RDS MySQL database using AWS Data Pipeline

 In this post we will see how to create a data pipeline in AWS which picks data from S3 csv file and inserts records in RDS MySQL table.  I am using below csv file which contains a list of passengers. CSV Data stored in the file Passenger.csv Upload Passenger.csv file to S3 bucket using AWS ClI In below screenshot I am connecting the RDS MySQL instance I have created in AWS and the definition of the table that I have created in the database testdb. Once we have uploaded the csv file we will create the data pipeline. There are 2 ways to create the pipeline.  Using "Import Definition" option under AWS console.                    We can use import definition option while creating the new pipeline. This would need a json file which contains the definition of the pipeline in the json format. You can use my Github link below to download the JSON definition: JSON Definition to create the Data Pipeline Using "Edit Architect" ...
1 comment
Read more