Transcript Chapter 1

CHAPTER 2
Implementing a Database
Introduction to Creating Databases
• After you’ve installed the Oracle software, the next logical
step is to create a database.
• DBAs must know how to configure required operating
system variables, directories, and the initialization file
before creating the database.
• As part of creating the database the data dictionary must
be instantiated.
• After the database is created it needs to be made
available for remote Oracle Net connections by
configuring and starting a listener.
Creating a Database
• Database Configuration Assistant utility
• CREATE DATABASE statement
Operating System Variables
• Manually setting
• Using Oracle provide scripts
• DBA home grown script
Creating a Database
1. Set the operating system variables
2. Configure the initialization file
3. Create required directories
4. Create the database
5. Create the data dictionary
6. Run pupbld.sql as SYSTEM
OS Variables that Oracle Uses
• ORACLE_HOME
• PATH
• ORACLE_SID
• LD_LIBRARY_PATH
Initialization File versus Spfile
• Spfile is required by some Oracle products
• Spfile is modifiable via ALTER SYSTEM
• Spfile on server will be used by remote clients for
operations such as starting up the database
• Initialization text file can be edited directly with an OS
editor (like vi)
• Comments can be placed in a text file
Configure Initialization File
ORACLE_HOME/dbs/init<SID>.ora
db_name
db_block_size
memory_target
control_files
undo_management
undo_tablespace
Security Related Initialization Parameters
sql92_security=TRUE
os_authent_prefix='‘
global_names=TRUE
audit_sys_operations=TRUE
audit_trail='DB'
Create Required Directories
• Follow standards (OFA or standards for your environment)
• Directories to contain: datafiles, control files, online redo
log files
• 10g and lower requires creation of directories that
background processes can write to
CREATE DATABASE Statement
CREATE DATABASE o12c
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 680
CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/o12c/system01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u01/dbfile/o12c/undotbs01.dbf'
SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/o12c/sysaux01.dbf'
SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u01/dbfile/o12c/temp01.dbf'
SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/o12c/users01.dbf'
SIZE 20M
LOGFILE GROUP 1
('/u01/oraredo/o12c/redo01a.rdo',
'/u02/oraredo/o12c/redo01b.rdo') SIZE 50M,
GROUP 2
('/u01/oraredo/o12c/redo02a.rdo',
'/u02/oraredo/o12c/redo02b.rdo') SIZE 50M,
GROUP 3
('/u01/oraredo/o12c/redo03a.rdo',
Tablespaces Initially Created
• SYSTEM
• SYSAUX
• UNDO
• TEMP
• USERS
SYS versus SYSTEM
• SYS is like superuser
• SYS owns all data dictionary objects
• SYS has all privileges (start/stop database)
• SYSTEM is a database account that has the DBA role
granted to it
• Some shops lock the SYSTEM account and don’t use it
(because it’s usually the first account a hacker will try to
access)
Create the Database
1. Create CREATE DATABASE statement
2. Connect as SYS and startup nomount
3. Run CREATE DATABASE statement
4. Create the data dictionary (must be done as SYS)
5. Run the pupbld.sql script as SYSTEM
Creating the Data Dictionary
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> connect system/foo
SQL> @?/sqlplus/admin/pupbld.sql
• What does the “?” translate to in the prior statements?
• What do these scripts actually create?
• Why do you need to be connected as SYS when creating
the data dictionary
Creating a Listener
• Listener required for connections from remote clients
• ORACLE_HOME/network/admin default location for
Oracle Net files
• Consider setting a variable like TNS_ADMIN that points to
default location for Oracle Net files
• lnsrctl utility
Listener Security Considerations
PASSWORDS_INVPRD=f00bar
#
ADMIN_RESTRICTIONS_INVPRD=ON
#
LOG_FILE_INVPRD=invlistener.log
LOG_DIRECTORY_INVPRD=/orahome/app/oracle/product/11.2.0.2/db
_1/network/log
#
TRACE_FILE_INVPRD=invlistener.trc
TRACE_DIRECTORY_INVPRD=/orahome/app/oracle/product/11.2.0.2/
db_1/network/trace
#
LOG_STATUS=ON
#
INBOUND_CONNECT_TIMEOUT_INVPRD2=60
Named Listener
INVPRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
131.147.127.152)(PORT = 1528))))
SID_LIST_INVPRD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = INVPRD)
(ORACLE_HOME =
/orahome/app/oracle/product/12.1.0.1/db_1)
(SID_NAME = INVPRD)))
Creating a Password File
• Optional
• Allows remote connections to the database as SYS
• orapw utility
Operating System Authentication
• OS group of dba and oper assigned when installing the
Oracle software
• Any OS user belonging to dba group can connect to the
database without a password
$ sqlplus / as sysdba
SQL> show user;
USER is “SYS”
SYSDBA vs. SYSOPER
• SYSDBA contains all privileges
• SYSOPER can start/stop, alter, toggle archivelog mode
and recover the database
Starting a Database
• Nomount: background processes and memory allocated,
only file accessed at this point is the initialization file
• Mount: Control files opened. Datafiles and online redo
logs not opened.
• Open: Datafiles and online redo logs opened
sqlplus / as sysdba
SQL> startup;
Stopping a Database
sqlplus / as sysdba
SQL> shutdown immediate;
• NORMAL
• TRANSACTIONAL
• TRANSACTIONAL LOCAL
• IMMEDIATE
• ABORT
Phases of Oracle Startup
Database vs. Instance
• Database consists of datafiles, controlfiles, and online
redo log files
• Instance consists of background processes and memory
structures.
Using Database Configuration Assistant
• Good way to consistently create databases (when used
with a response file)
• dbca utility
Dropping a Database
• Be very careful
• Permanently drops datafiles, control files and online redo
logs
• You are not prompted
• There is no undrop database command
How many Oracle Homes on one
Database Server
• One set of Oracle binaries for each database?
• One set of Oracle binaries for all databases?
• Different versions of Oracle binaries
• Security considerations?
• Hardware resource considerations?
One Database or Many Databases on
One Server
• One database per each application
• Shared database with different users and tablespaces for
each application
• Security considerations?
• Hardware resource considerations?
Summary
• Creating a database is a critical DBA task.
• The database should be created in a consistent, secure,
and maintainable manner.
• As part of creating a database, a listener must be
configured to allow for remote connections to the
database via Oracle Net.