1 / 29

Chapter 2

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.

fox
Download Presentation

Chapter 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 2 Implementing a Database

  2. 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.

  3. Creating a Database • Database Configuration Assistant utility • CREATE DATABASE statement

  4. Operating System Variables • Manually setting • Using Oracle provide scripts • DBA home grown script

  5. Creating a Database • Set the operating system variables • Configure the initialization file • Create required directories • Create the database • Create the data dictionary • Run pupbld.sql as SYSTEM

  6. OS Variables that Oracle Uses • ORACLE_HOME • PATH • ORACLE_SID • LD_LIBRARY_PATH

  7. 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

  8. Configure Initialization File ORACLE_HOME/dbs/init<SID>.ora db_name db_block_size memory_target control_files undo_management undo_tablespace

  9. Security Related Initialization Parameters sql92_security=TRUE os_authent_prefix='‘ global_names=TRUE audit_sys_operations=TRUE audit_trail='DB'

  10. 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

  11. Crafting a CREATE DATABASE statement CREATE DATABASE O11R2 maxlogfiles 16 maxlogmembers 4 maxdatafiles 1024 maxinstances 1 maxloghistory 680 character set "UTF8" DATAFILE '/ora01/dbfile/O11R2/system01.dbf' SIZE 500m EXTENT MANAGEMENT LOCAL UNDO TABLESPACE undotbs1 DATAFILE '/ora02/dbfile/O11R2/undotbs01.dbf' SIZE 800m SYSAUX DATAFILE '/ora03/dbfile/O11R2/sysaux01.dbf' SIZE 200m DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/ora03/dbfile/O11R2/temp01.dbf' SIZE 800m DEFAULT TABLESPACE users DATAFILE '/ora02/dbfile/O11R2/users01.dbf' SIZE 20m LOGFILE GROUP 1 ('/ora02/oraredo/O11R2/redo01a.rdo', '/ora03/oraredo/O11R2/redo01b.rdo') SIZE 100m, GROUP 2 ('/ora02/oraredo/O11R2/redo02a.rdo', '/ora03/oraredo/O11R2/redo02b.rdo' ) SIZE 100m, GROUP 3 ('/ora02/oraredo/O11R2/redo03a.rdo', '/ora03/oraredo/O11R2/redo03b.rdo' ) SIZE 100m USER sys IDENTIFIED BY secretfoo USER system IDENTIFIED BY secretfoobar;

  12. Tablespaces Initially Created • SYSTEM • SYSAUX • UNDO • TEMP • USERS

  13. 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)

  14. Create the Database • Create CREATE DATABASE statement • Connect as SYS and startup nomount • Run CREATE DATABASE statement • Create the data dictionary (must be done as SYS) • Run the pupbld.sql script as SYSTEM

  15. Creating the Data Dictionary SQL> connect / as sysdba SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.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

  16. 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

  17. 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

  18. 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/11.2.0.2/db_1) (SID_NAME = INVPRD)))

  19. Creating a Password File • Optional • Allows remote connections to the database as SYS • orapw utility

  20. 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”

  21. SYSDBA vs. SYSOPER • SYSDBA contains all privileges • SYSOPER can start/stop, alter, toggle archivelog mode and recover the database

  22. 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;

  23. Stopping a Database sqlplus / as sysdba SQL> shutdown immediate; • NORMAL • TRANSACTIONAL • TRANSACTIONAL LOCAL • IMMEDIATE • ABORT

  24. Database vs. Instance • Database consists of datafiles, controlfiles, and online redo log files • Instance consists of background processes and memory structures.

  25. Using Database Configuration Assistant • Good way to consistently create databases (when used with a response file) • dbca utility

  26. 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

  27. 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?

  28. 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?

  29. 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.

More Related