Create_database_Manually_11gor12c

Prévia do material em texto

How To Create A Database Manually 
An oracle database can be created either using Manually or using DBCA( GUI Method). But 
few organizations recommend using manual method for creating database. Here I have 
provided steps for creating database Manually with installation of differnt optional 
components. 
Before creating an oracle database, make sure oracle database software is installed. 
 
Let’s say we will create a database named DBATEAM 
1. First, create a profile with a correct environment variable. 
ORACLE_SID = SYSTEM IDENTIFIER , SAME AS db instance name 
ORACLE_HOME = Oracle database binary installation location. 
vi .profile_DBATEAM 
 
#!/bin/sh 
umask 022 
ORACLE_SID=DBATEAM; export ORACLE_SID 
ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1; export ORACLE_HOME 
export PATH=$ORACLE_HOM/bin:$PATH 
 
2. Now create the init.ora file( in $ORACLE_HOME/dbs location) 
When we start the instance, it will read this file and set values accordingly. 
mkdir /u01/app/oracle 
mkdir -p /u01/oradata/DBATEAM/data/DBATEAM 
cd $ORACLE_HOME/dbs 
 
$ vi initDBATEAM.ora 
*.compatible='12.1.0.2.0' 
*.control_files='/u01/oradata/DBATEAM/data/DBATEAM/control01.ctl' 
*.db_block_size=8192 
*.db_domain='' 
*.db_name='DBATEAM' 
*.diagnostic_dest='/u01/app/oracle' 
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBATEAMXDB)' 
*.open_cursors=300 
*.pga_aggregate_target=536870912 
*.processes=150 
*.remote_login_passwordfile='EXCLUSIVE' 
*.sga_target=1610612736 
*.undo_tablespace='UNDOTBS1' 
 
 
COMPATIBLE – Version of the oracle database 
CONTROL_FILE – When we create the database, the control_files will be created in the 
specified location. 
DB_BLOCK_SIZE– This is the default block_size of the database. Any tablespace we 
create in the database will take this block size value. 
DB_NAME – Name of the database 
DIAGNOSTIC_DEST– ADR location, where the log and trace files will be written 
SGA_TARGET – Shared global area, memory allocated. 
UNDO_TABLESPACE– The default undo tablespace, that will be created in the database 
 
3. Start the database in nomount stage: 
. ./.profile_DBATEAM 
SQL > sqlplus / as sysdba 
 
SQL*Plus: Release 11.1.0.2.0 Production on Mon Jul 20 11:19:05 2015 
 
Copyright (c) 1982, 2013, Oracle. All rights reserved. 
 
Connected to an idle instance. 
 
SQL > startup nomount 
ORACLE instance started. 
 
Total System Global Area 1603411968 bytes 
Fixed Size 2228784 bytes 
Variable Size 503320016 bytes 
Database Buffers 1073741824 bytes 
Redo Buffers 24121344 bytes 
 
SQL > 
 
 
 
 
 
 
 
 
 
 
 
4. Below the create database script. 
SQL > CREATE DATABASE DBATEAM 
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
MAXINSTANCES 1 
LOGFILE 
GROUP 1 '/u04/oradata/DBATEAM/data/redo1.log' SIZE 10M, 
GROUP 2 '/u04/oradata/DBATEAM/data/redo2.log' SIZE 10M, 
GROUP 3 '/u04/oradata/DBATEAM/data/redo3.log' SIZE 10M 
DATAFILE 
'/u04/oradata/DBATEAM/data/system.dbf' size 200M REUSE 
sysaux datafile '/u04/oradata/DBATEAM/data/sysaux.dbf' size 100m 
undo tablespace UNDOTBS1 datafile '/u04/oradata/DBATEAM/data/undo1.dbf' size 100m 
DEFAULT TEMPORARY TABLESPACE temp1 
TEMPFILE '/u04/oradata/DBATEAM/data/temp01.dbf' SIZE 100M REUSE 
CHARACTER SET AL32UTF8; 
 
database created. 
 
MAXLOGFILES – Maximum number of redo log files that can be created in the database. 
MAXLOGMEMBERS – maximum number of redo log file members that any given log file 
group can have in the database. 
MAXLOGHISTORY – Maximum number of archive redo logs for automatic media recovery. 
MAXDATAFILES – This defines the maximum number of datafile, But upon reaching max 
value, it will extend automatically to value upto DB_FILES parameter. 
The MAXINSTANCES – No. of instances for the database, for standalone it will be 1 and for 
RAC database, it will be that of no. of rac nodes. 
CHARACTER SET – Characterset of the database. 
5. Now create the data dictionary views(CATALOG and CATPROC 
component) 
These two are mandatory scripts we need to run after creating a database. Below scripts will 
create the dictionary views and tables. 
SQL > @$ORACLE_HOME/rdbms/admin/catalog.sql 
 
SQL > @$ORACLE_HOME/rdbms/admin/catproc.sql 
 
6. Create the product user profile table ( by connecting to system user) 
SQL > conn system/manager 
 
SQL > @$ORACLE_HOME/sqlplus/admin/pupbld.sql 
You can install the additional components depending upon the requirement. These are the 
optional components. 
7. Install JVM(JAVA VIRTUAL MACHINE) component:[optional] 
conn / as sysdba 
 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/javavm/install/initjvm.sql; 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/initxml.sql; 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/xmlja.sql; 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catjava.sql; 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catxdbj.sql; 
8. Install CONTEXT component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ctx/admin/catctx.sql change_on_install 
SYSAUX TEMP LOCK; 
 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ctx/admin/defaults/dr0defin.sql "AMERIC
AN"; 
 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/dbmsxdbt.sql; 
 
9. Install ORADIM component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ord/admin/ordinst.sql SYSAUX SYSAUX; 
10. Install MEDIA component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/ord/im/admin/iminst.sql; 
 
12. Install OLAP component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/olap/admin/olap.sql SYSAUX TEMP; 
 
13. Install SPATIAL component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/md/admin/mdinst.sql; 
 
14. Install LABEL security component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catols.sql; 
15. Install APEX component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/apex/catapx.sql change_on_install SYSAU
X SYSAUX TEMP /i/ NONE; 
 
16. Install DATABASE VAULT component:[optional] 
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catmac.sql SYSAUX TEMP &&sy
sPassword; 
 
You can check the installed components by running the below query. 
select comp_id,status,comp_name from dba_registry; 
17. Create a listener file and start the listener. 
vi listener.ora 
 
LISTENER_DBATEAM = 
(DESCRIPTION= 
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1532)) 
) 
 
SID_LIST_LISTENER_DBATEAM = 
(SID_LIST= 
(SID_DESC= 
(SID_NAME=DBATEAM) 
(ORACLE_HOME= /u01/app/oracle/product/11.2.0.3/db_1) 
) 
) 
 
lsnrctl start LISTENER_DBATEAM