In this post I will guide you through the steps on how to
create a new database on a new instance created (In my virtual Linux machine I
already have a instance & database (orcl) created).
The below details explain the steps involved in creating a
second Instance & Database on the standalone server
Details of my Virtual
server:
Virtual machine used – Oracle Virtual Box 4.2.18
Operating System – Oracle Linux version 2.6.39
Oracle Server Installed – Oracle 12c r1
All the steps are performed by logging into my virtual linux
server using Oracle user connected using sqlplus as sysdba by OS authentication
(sqlplus / as sysdba)
Step 1:
Specify an Instance Identifier (SID) & Set Environment Variables:
Instance Identifier is a unique name to identify the
instance(when there are multiple instances already created in the host server).
Open any text editor in Linux operating(say gedit) and have
the SID defined, mention the below commands in gedit and save it with name as .MyDB_Profile (Instance & DB Name
we going to create is MyDB). Please find the terminal screenshot, creating a
profile file .MyDB_Profile in /home/oracle and setting the environment
variables.
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
.
~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=ol6-12c.localdomain
export ORACLE_UNQNAME=MyDB
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=MyDB
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
------------------------------------------------------
Note: .Profile
file in Linux - Is the first file which gets executed when you login to a
shell. It has some initializations, aliases, exports etc to make you go easy
while working on command line. It is present in the user's home directory and
it’s a hidden file as it begins with a dot(.) To have the environment variable
set to work on creating our database, we need to manually execute .MyDB_Profile
file as . ./.MyDB_Profile
An environment variable is a named object that contains data used by one or more applications. In simple terms, it is a variable with a name and a value. The value of an environmental variable can for example be the location of all executable files in the filesystem, the default editor that should be used, or the system locale settings.
Step 2:
Create the Initialization Parameter File:
To start an instance, the database must read instance
configuration parameters (the initialization parameters) from either a server
parameter file (SPFILE – Binary file) or a text initialization parameter file (init<SID>.ora)
By default Initialization parameter file is located at - $ORACLE_HOME/dbs
(in Unix OS)
Create the Initialization parameter file named initMyDB.ora
as below and save the same as initMyDB.ora file in $ORACLE_HOME/dbs
db_name='MyDB'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/MyDB/MyDB_adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/MyDB_fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/MyDB'
dispatchers='(PROTOCOL=TCP) (SERVICE=MyDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
UNDO_RETENTION = 1800
# You may want to ensure that control files are created on
separate physical
# devices
control_files = (/u01/app/oracle/oradata/MyDB/ora_control1, /u01/app/oracle/oradata/MyDB/ora_control2)
compatible ='11.2.0'
After connecting to instance(authenticated by OS authentication
/) using the initMyDB.ora file, create the spfile from the pfile using the
below statement
create spfile from
pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initMyDB.ora';
nNote: I have installed “readline
wrapper”(rlsqlplus alias for sqlplus) utility is my Linux OS to provide a
command history and editing of keyboard input commands.
Step 3:
Start the Instance:
Nomount clause is used whenever a database need to be
created or when performance maintenance need to be performed.
When you try to Startup instance without NOMOUNT clause
below error will be displayed
Now start the instance in nomount state and check
whether the instance has started using the spfile created as below to create
the new database.
Step 4: Create
Database using CREATE DATABASE Statement:
Using the below create statement, Database can be created
which has the below specifications
§
Database name considered is “MyDB”
§
Directory locations for redo log files &
datafiles created before execution the Create Database statement (CREATE
DATABASE statement cannot create the directories mentioned if they are not
existing already)
/u01/app/oracle/oradata/MyDB/MyDBRedoFiles - Redo Log Files Location(redo01/02/03.log)
/u01/app/oracle/oradata/MyDB/MyDBDataFiles - DataFiles Location(system01.dbf,
sysaux01.dbf, users01.dbf, temp01.dbf, undotbs01.dbf)
§
Tablespace Extent Management is Locally Managed
- A tablespace that manages its own extents maintains a bitmap in each datafile
to keep track of the free or used status of blocks in that datafile. Each bit
in the bitmap corresponds to a block or a group of blocks. When an extent is
allocated or freed for reuse, Oracle Database changes the bitmap values to show
the new status of the blocks.
§
Character set considered - AL32UTF8 - Unicode 4.0 UTF-8 Universal character set
§
National Character Set considered - AL16UTF16 - used to store data in
columns specifically defined as NCHAR, NCLOB, or NVARCHAR2
§
MAXLOGFILES
– 5 - Maximum number of redo log file groups that can ever be created for the
database. Rt now defined 3 groups(Group 1/2/3) and eventually in the future the
can allow to create two more log groups.
§
MAXLOGMEMBERS
- 5 - Maximum number of members, or copies, for a redo log file group. Rt now
one file is defined in each file group(redo1/2/3.log)
§
MAXDATAFILES
– 50 – This defines to add a new data file whose number is greater than
MAXDATAFILES, but less than or equal to DB_FILES initialization parameter,
causes the Oracle Database control file to expand automatically so that the
datafiles section can accommodate more files.
CREATE DATABASE MyDB
USER SYS
IDENTIFIED BY <Mention SYS Password>
USER
SYSTEM IDENTIFIED BY < Mention SYSTEM Password >
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo02.log') SIZE
50M,
GROUP 3 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo03.log') SIZE
50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 50
MAXINSTANCES
1
CHARACTER
SET AL32UTF8
NATIONAL
CHARACTER SET AL16UTF16
EXTENT
MANAGEMENT LOCAL
DATAFILE
'/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf' SIZE 325M REUSE
SYSAUX
DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf' SIZE 325M
REUSE
DEFAULT
TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf'
SIZE
300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT
TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/temp01.dbf'
SIZE
50M REUSE
UNDO
TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf'
SIZE
200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Executing the above CREATE DATABASE statement, MyDB Database
is successfully created.
Note: In the
process of creating the Database whenever as error is faced, one need to shut
down the instance(in nomount using shutdown immediate or shutdown) and clear
all the files created by the Create Database statement prior to encountering
the error.(clear control files created, delete diag folder present in /u01/app/oracle/MyDB,
delete datafiles present in /u01/app/oracle/oradata/MyDBDataFiles, delete
redologfile present in /u01/app/oracle/oradata/MyDBRedoFiles, delete files
present in /u01/app/oracle/admin/MyDB/MyDB_adump, delete folder xdb_wallet present in /u01/app/oracle/admin/MyDB/xdb_wallet)
In the process I have come across few errors as below (as I
forgot to delete the control files which got created because bases on the
Initialization file and a mismatch in the UNDO_TABLESPACE initialization
configuration error).
After deleting control files after instance
shutdown and then when tried the Create Database statement it got successfully
created.
Step 5: Run
Scripts to Build Data Dictionary Views:
Be in the NOMOUNT instance state and run the scripts(catalog.sql,
catproc.sql, pupbld.sql) to build data dictionary views(v$ views), synonyms,
and PL/SQL packages, and to support proper functioning of SQL*Plus
catalog.sql - create data dictionary views
catproc.sql - run all sql scripts for the procedural
option
pupbld.sql – The PRODUCT_USER_PROFILE (PUP) table
provides product-level security that supplements the user-level security
provided by the SQL GRANT and REVOKE commands and user roles.
Run above scripts at the SQL prompt as below with
SYSDBA login
1.
@/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/catalog.sql (this
gonna take 15 – 20 mins to create the complete data dictionary views)
2.
@/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/catproc.sql (this
gonna take 20– 30 mins to complete the execution)
3.
To run the pupbld.sql script we should first connect
to the database instance as SYSTEM user.To have a successful connection using
SYSTEM user make sure you
a. Stop the
listener service as below
b. Contents of
the LISTENER.ora & TNSNAMES.ora files modified as below
Listener.ora file content:
#
listener.ora Network Configuration File:
/u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated
by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = ol6-12c.localdomain)(PORT = 1521)) ) )
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=MyDB)
(ORACLE_HOME =
/u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = MyDB)
)
)
Tnsnames.ora file content
Please note
that as I have two instance/databases in my virtual box, tnsnames.ora file
should have the descriptions for both as below. Please note that service name value is derived from the
initialization parameter set in spfile
#
tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated
by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol6-12c.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain)
)
)
MyDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol6-12c.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MyDB.localdomain)
)
)
and
stop/start the listener service and then connect as SYSTEM user and run the
pupbld.sql script(located in /u01/app/oracle/product/12.1.0/db_1/sqlplus/admin)
c. Start the listener
service as below
d. Now connect as SYSTEM user a below
e. Finally run
the pupbld.sql script below in the sql prompt after connecting s SYSTEM user @/u01/app/oracle/product/12.1.0/db_1/sqlplus/admin/pupbld.sql(this
gonna take few secs to finish execution)
Thanks to my Wife who has supported me in making this
post happen. Appreciate her patience, effort & understanding me.
References:
http://www.linuxquestions.org/questions/linux-general-1/how-to-get-os-name-from-command-line-557339/
Snagit software which helped in capturing the
above screenshots.
No comments:
Post a Comment