4 Creating a Database Objectives Preparing

4 Creating a Database

Objectives • Preparing the operating system • Preparing the parameter file • Creating the database 4 -2

Overview Instance SGA User process Shared Pool Server process PGA Control files Parameter file Password file 4 -3 Redo log files Database

Creation Prerequisites • A privileged account authenticated in one of the following ways: - By the operating system - Using a password file • • 4 -4 Memory to start the instance Sufficient disk space for the planned database

Planning Database File Locations • Keep at least two active copies of a database control file on at least two different devices. • Multiplex the redo log files and put group members on different disks. • Separate data files whose data: – Will participate in disk resource contention across different physical disk resources – Have different life-spans – Have different administrative characteristics 4 -5

Oracle Software Locations /u 01/app/oracle /product /u 02/applmgr /product /8. 0. 3 /bin /dbs /orainst /sqlplus. . . /7. 3. 3 /admin /local 4 -6 /admin /local

Oracle Database Files /u 03/ /u 02/ oradata/ db 01/ system 01. dbf control 01. ctl redo 0101. rdo. . . db 02/ system 01. dbf control 01. ctl redo 0101. rdo. . . 4 -7 oradata/ db 01/ tools 01. dbf control 02. ctl redo 0102. rdo. . . db 02/ users 01. dbf control 02. ctl redo 0102. rdo. . .

Creating a Database: Considerations • On UNIX: – Created automatically during an installation – Created manually after installation • On NT: – Created using the Oracle Database Assistant – Created manually 4 -8

File Management Methods Files controlled by the file management method are control files, data files, and redo log files Determines how files are located to help with: • Multiplexing of control and redo log files • I/O load balancing Determines how files are added, extended, or deleted 4 -9

User-Managed File Management Type of File Management (first of two methods) Pros: • DBA has complete control of names, locations, and sizes of all files • Compatible with older versions Cons: • DBA must manually delete files after their associated tablespace is dropped • DBA must monitor and adjust file sizes over time 4 -10

User-Managed File Management How to implement: • For user-managed control files, set CONTROL_FILES to a list of files. For example: • For user-managed redo log files, use the LOGFILE clause in the CREATE DATABASE command. • For user-managed data files, use the DATAFILE clause in the CREATE DATABSE command or the CREATE TABLESPACE command 4 -11

User-Managed File Management Example Initialization parameter: CONTROL_FILES = (/d 1/oracle/control 01. ctl, /d 2/oracle/control 02. ctl) CREATE DATABASE command: CREATE DATABASE TECHNO 92 MAXDATAFILES 100 DATAFILE ‘C: oradatasystem 01. dbf' SIZE 325 M AUTOEXTEND ON NEXT 10240 K MAXSIZE UNLIMITED LOGFILE GROUP 1 (‘C: oralogsredo 01. log') SIZE 50 M, GROUP 2 (‘D: oralogsredo 02. log') SIZE 50 M; 4 -12

Oracle Managed File Management Type of File Management (second of two methods) Pros: • Automated control of names and sizes of all files • DBA only has to determine the locations • Less monitoring required due to automated size adjustment and deleting of appropriate files Cons: • File names can be somewhat cryptic • No control over exact sizes and names of files 4 -13

Oracle Managed File Management How to implement: • For user-managed data files, set the DB_CREATE_FILE_DEST to a valid directory • For user-managed control files and redo log files, set DB_CREATE_ONLINE_LOG_DEST_n to a valid directory • When the database is created, insert the control_files parameter back into the init. ora file so that db can be started and stopped later 4 -14

Oracle Managed File Management Example Initialization parameters: DB_CREATE_FILE_DEST = ‘C: oradata' DB_CREATE_ONLINE_LOG_DEST_1= ‘C: oralogs’ DB_CREATE_ONLINE_LOG_DEST_2= ‘D: oralogs’ CREATE DATABASE command: CREATE DATABASE TECHNO 92 MAXDATAFILES 100; 4 -15

Creating a Database Two distinct methods: • Automated: Database Configuration Assistant – Better for novice DBA – Will create a parameter file for you – May not be able to use OFM methods – Easier to use due to the many pre-defined settings • Manual: CREATE DATABASE command – More flexible – Useful when using script for creating multiple identical (or similar) databases on several sites – Can work off of a parameter file 4 - Must run other scripts later (catalog. sql, etc. ) – 16

Creating a Database Manually 1. Decide on a unique instance and database name and database character set. 2. Set the operating system variables. 3. Prepare the parameter file. 4. Create a password file (recommended). 5. Start the instance. 6. Create the database. 7. Run scripts to generate the data dictionary and accomplish postcreation steps. 4 -17

Operating System Environment On UNIX set the following environment variables: • ORACLE_HOME • ORACLE_SID • ORACLE_BASE • ORA_NLS 33 • PATH 4 -18

Operating System Environment On NT • Set the variable ORACLE_SID to use SVRMGR 30. • Create the service and the password file with ORADIM 80. C: > ORADIM 80 -NEW -SID u 16 -INTPWD password -STARTMODE auto -PFILE ORACLE_HOMEDATABASEinit. U 16. ora 4 -19

Preparing the Parameter File 1. Create the new init<SID>. ora. $cp init. ora $ORACLE_HOME/dbs/init. U 16. ora 2. Modify the init. U 16. ora by editing the parameters. 4 -20

Editing the Parameter File db_name = U 16 db_files = 100 # db_files = 400 # MEDIUM # db_files = 1000 # LARGE db_file_multiblock_read_count = 8 # db_file_multiblock_read_count = 16 # MEDIUM # db_file_multiblock_read_count = 32 # LARGE control_files = (/disk 1/control 01. con, /disk 2/control 02. con) db_block_size = 8192 db_block_buffers = 2000 # SMALL # db_block_buffers = 550 # MEDIUM # db_block_buffers = 3200 # LARGE shared_pool_size = 30000000 # shared_pool_size = 5000000 # MEDIUM # shared_pool_size = 9000000 # LARGE log_buffer = 65536 # log_buffer = 32768 # MEDIUM # log_buffer = 163840 # LARGE. . . 4 -21

Starting the Instance 1. Connect as SYSDBA. 2. Start the instance in NOMOUNT stage. SVRMGR> STARTUP NOMOUNT 2> PFILE=init. U 16. ora ORACLE instance started. 4 -22

Creating the Database SPOOL cre. U 16. log STARTUP NOMOUNT PFILE=init. U 16. ora CREATE DATABASE U 16 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXLOGHISTORY 100 LOGFILE GROUP 1 ('/DISK 3/log 1 a. rdo', /DISK 4/log 1 b. rdo’) SIZE 1 M, GROUP 2 ('/DISK 3/log 2 a. rdo', /DISK 4/log 2 b. rdo’) SIZE 1 M DATAFILE '/DISK 1/system 01. dbf' size 50 M autoextend on CHARACTER SET WE 8 ISO 8859 P 1; 4 -23

Oracle Database Assistant 4 -24

Troubleshooting Creation of the database fails if: • There are syntax errors in the SQL script • Files that should be created already exist • Operating system errors such as file or directory permission or insufficient space errors occur 4 -25

After Creation of the Database The database contains: • Data files which make up the SYSTEM tablespace • Control files and redo log files • User SYS/change_on_install • User SYSTEM/manager • Rollback segment SYSTEM • Internal tables (but no data dictionary views) 4 -26

OMF • Oracle-Managed Files • feature introduced in Oracle 9 i • Allows Oracle RDBMSto manage datafiles for you. Oracle has been making significant strides in making the database easier to manage and OMF falls into this category of features. • For example, in Oracle databases prior to 9 i, when you dropped a tablespace, you would also have to remove the physical datafile associated with that tablespace. With Oracle 9 i, you can leave physical file management to the database itself by using OMFs 4 -27

OMF (Cont. ) • Very useful in low-use / smaller databases in order to reduce the administrative overhead. • OMF reduces the overall administrative overhead required for such smaller databases. • OMF feature can be particularly useful for development and test databases. • OMF simplifies management of a standby database. In pre -Oracle 9 i databases, when you added a tablespace or datafile to the primary database, human intervention was required on the standby database to perform the same operation. • With OMF, iff the standby database is configured to use OMF, then the creation of a tablespace or addition of a datafile to the primary database will result in the automated creation of that tablespace or datafile on the standby server. No other administrative activity is required! 4 -28

OMF (Cont. ) • OMF is also useful in a large database environment that is using large disk arrays. (i. e. RAID-0). • OMF is not an appropriate choice for use with a high -volume or mission-critical database that is not using high-end striped disk arrays. • For example, OMF is not recommended on systems with many smaller file systems, or systems running RAID-5. This is because the nature of managed datafiles is such that the DBA is not able to distribute I/O as required. • Also, the managed datafile feature does not support the use of raw disk devices. 4 -29

OMF (Cont. ) • Example Create Database Command: CREATE DATABASE mydb DATAFILE SIZE 500 M LOGFILE GROUP 1 SIZE 10 M , GROUP 2 SIZE 10 M DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 100 M UNDO TABLESPACE undotbs 1 DATAFILE SIZE 50 M MAXLOGFILES = 5 MAXLOGMEMBERS = 5 MAXDATAFILES = 600 NOARCHIVELOG; 4 -30

OMF (Cont. ) • Oracle 9 i Release 2 (9. 2) changes: File Type Naming Convention Datafile Tempfile Redo logfile Control file o 1_mf_%t_%u_. dbf o 1_mf_%t_%u_. tmp o 1_mf_%g_%u_. log o 1_mf_%u_. ctl Example o 1_mf_tbs 1_2 ixfh 90 q_. dbf o 1_mf_temp 1_6 dygh 80 r_. tmp o 1_mf_1_wo 94 n 2 xi_. log o 1_mf_cmr 7 t 90 p_. ctl Where: %t is the tablespace name (possibly truncated) %u is an eight character string that guarantees uniqueness %g is the online redo log file group number A file is now considered OMF if its base file name has: - a "o 1_mf_" prefix - and a ". dbf", ". tmp", ". log", or ". ctl" extension - and an "_" character immediately preceding 4 -31 the extension

Summary • Planning the database structure • Preparing the operating system environment • Creating the database 4 -32