Thursday, December 6, 2012

8 Oracle Database(4): Create Database from script

Setup ORACLE_SID name

SID is PODB.

export ORACLE_SID=PODB

image

Setup the parameter file: init$ORACLE_SID.ora

  • DB_NAME
  • DB_BLOCK_SIZE
  • SGA_TARGET
  • CONTROL_FILES
  • UNDO_MANAGEMENT

The file is under $ORACLE_HOME/dbs

DB_NAME=PODB
DB_BLOCK_SIZE= 8192
SGA_TARGET  = 300M
CONTROL_FILES=/home/oracle/app/oracle/oradata/podb/controlfile/PODB_controlfile.ctl
UNDO_MANAGEMENT = AUTO

image

Create all the directory and grant the permission

Create database

  • Startup with nomount
  • NO OMF: db_create_file_dest
startup nomount pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initPODB.ora
@/home/oracle/app/oracle/oradata/podb/createdb.sql

image

image

Run the createdb.sql. Here is my createdb.sql

create database PODB
user sys identified by oracle
user system identified by oracle
MAXLOGFILES 5
MAXLOGMEMBERS 5
LOGFILE GROUP 1 ( '/home/oracle/app/oracle/oradata/podb/logfile/redo01a.log','/home/oracle/app/oracle/oradata/podb/logfile/redo01b.log') SIZE 30M, GROUP 2 ('/home/oracle/app/oracle/oradata/podb/logfile/redo02a.log','/home/oracle/app/oracle/oradata/podb/logfile/redo02b.log') size 30M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/system01.dbf' SIZE 300M
SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/sysaux01.dbf' SIZE 300M
DEFAULT TABLESPACE users DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/user01.dbf' SIZE 300M AUTOEXTEND ON MAXSIZE UNLIMITED 
DEFAULT TEMPORARY TABLESPACE tempts TEMPFILE '/home/oracle/app/oracle/oradata/podb/controlfile/tempts01.dbf' SIZE 300M
UNDO TABLESPACE undots DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/undots01.dbf' SIZE 300M AUTOEXTEND ON MAXSIZE UNLIMITED 

image

Check database

select STATUS from V$INSTANCE;
select name from V$DATABASE;
select name from V$DATAFILE;
select name from V$TEMPFILE;
select name from V$CONTROLFILE;
select GROUP#,member from V$LOGFILE;
select NAME from V$TABLESPACE;
-- check the users / dba_users
select NAME from SYS.USER$ where TYPE#=1;
-- check the roles
select NAME from sys.user$ where type#=0;
 

Dictionary related tables are not usable because we are not yet create those tables.

Post DB creation

Please refer here for more detail.

  • Create the oracle password file to enable the remote login as sysdba

    image

    Please refer here for more detail.

  • Create Data Dictionary and Build-in Package

  • Create SQL PLUS related tables

orapwd file=$ORACLE_HOME/dbs/orapwPODB password=orcl
spool /tmp/PODB_POST.log
show user
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
connect system/oracle
@?/sqlplus/admin/pupbld.sql
spool off

8 comments:

  1. Thanks for sharing this informative article to our vision.
    Regards.
    Oracle Apps Training in Chennai

    ReplyDelete

  2. Thanks for sharing with us that awesome article you have amazing blog.....
    http://hadooptraininginhyderabad.co.in/salesforce-training-in-hyderabad/

    ReplyDelete
  3. Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts. If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features.If anyone interested to take Oracle Apps Fusion Online Training, please reach us KBS Training located at Bangalore. Rated as best training institute in Bangalore.

    ReplyDelete
  4. This tutorial explains how to create a database, tables, and insert row ... how to enter the values by using “&” in mysql table ..from command prompt? .... R Programming Training | DataStage Training | SQL Training | SAS Training | Android Training | SharePoint Training

    ReplyDelete

  5. Pleasant Post. It is truly intriguing to peruse from the earliest starting point and I might want to share your site to my circles, continue sharing more like this.
    SAS Training in Chennai | SAS course in Chennai

    ReplyDelete
  6. Thanks for sharing in useful information
    Extraordinary and the maximum interesting records. that is simply the form of statistics that i had
    Been searching out, i'm already your rss reader now and i might often be careful with the present day posts.
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  7. Thanks for sharing the useful information and good points were stated in the article and for the further information visit our site
    Oracle Fusion Financials Training

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates