Manual Database Creation

Contents:- Manual Database Creation

  1.   Manual Database Creation in Oracle 11g Standalone Grid Infra

Steps

 è Environment Variable Settings

$>export ORACLE_BASE=/u01/app/oracle

$>export ORACLE_HOME=$ORACLE_BASE/product/11.0.3/db1

$>export ORACLE_SID=stc

 è Verify Your Cluster Resource is online or available

$> . oraenv

          +ASM

$>crs_stat –t

$>asmcmd

$>ls –ltra

 è Create new PFILE For your new database by using existing database pfile or dummy pfile and rename SID whenever required.

Here I am creating PFILE using my existing database PFILE and then will rename SIT with my new database sid in newly created pfile.

$> . oraenv

          Orcl

$> sqlplus sys as sysdba

$> Create pfile=’/u01/app/oracle/initstc.ora’ from spfile;

$> exit

Now I will replace orcl with stc in my newly created pfile named initstc.ora

 è Create Password File for your new database.

$export ORACLE_SID=stc

$export ORACLE_BASE=<ORACLE_BASE>

$export ORACLE_HOME=<ORACLE_HOME>

$orapwd file=$ORACLE_HOME/dbs/PWDstc.ora password=stc entries=10

 è Create Directory Structure for your new database.

 è Create Directory for your new database into ASM Disks.

$> . oraenv

          +ASM

$> asmcmd mkdir +DATA/stc

$> asmcmd mkdir +FLASH/stc

 è Execute the Create Database Command to Create a Database.

$> export ORACLE_BASE=<ORACLE_BASE>

$> export ORACLE_HOME=<ORACLE_HOME>

$> export ORACLE_SID=stc

$> sqlplus sys as sysdba

sql> startup nomount pfile=’/u01/app/oracle/initstc.ora’;

sql> CREATE DATABASE "stc"

MAXINSTANCES 32

MAXLOGHISTORY 1

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

DATAFILE SIZE 300M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

DEFAULT TABLESPACE USERS DATAFILE SIZE 100M

CHARACTER SET WE8MSWIN1252

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1  SIZE 500M,

GROUP 2  SIZE 500M

USER SYS IDENTIFIED BY ORACLE

USER SYSTEM IDENTIFIED BY ORACLE;

 è Adding CONTROL_FILES parameters in pfile.

SQL> set linesize 2048;

SQL> column ctl_files NEW_VALUE ctl_files;

SQL> select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';

SQL> host echo &ctl_files >> $ORACLE_HOME/dbs/initsample.ora;

 è Run Following Script as sys user

$> conn sys as sysdba

$> @?/rdbms/admin/catalog.sql

$> @?/rdbms/admin/catproc.sql

 è Run Following Script as System User

SQL> connect system/oracle

SQL> @?/sqlplus/admin/pupbld.sql