1. 4 Creating a Database

2. Objectives • Preparing the operating system • Preparing the parameter file • Creating the database

3. Userprocess Serverprocess PGA Control files Parameterfile Redo logfiles Datafiles Passwordfile Overview Instance SGA Shared Pool Database

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

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

6. /u01/app/oracle /product /8.0.3 /bin/dbs/orainst/sqlplus... /7.3.3 /admin /local Oracle Software Locations /u02/app/applmgr /product /admin /local

7. /u03/ /u02/ oradata/ oradata/ db01/ db01/ tools01.dbfcontrol02.ctlredo0102.rdo... system01.dbfcontrol01.ctlredo0101.rdo... db02/ db02/ users01.dbfcontrol02.ctlredo0102.rdo... system01.dbfcontrol01.ctlredo0101.rdo... Oracle Database Files

8. Creating a Database: Considerations • On UNIX: • Created automatically during aninstallation • Created manually after installation • On NT: • Created using the Oracle DatabaseAssistant • Created manually

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

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

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

12. User-Managed File Management • Example • Initialization parameter: CONTROL_FILES = (/d1/oracle/control01.ctl, /d2/oracle/control02.ctl) • CREATE DATABASE command: CREATE DATABASE TECHNO92 MAXDATAFILES 100 DATAFILE ‘C:\ora\oradata\system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED LOGFILE GROUP 1 (‘C:\ora\oralogs\redo01.log') SIZE 50M, GROUP 2 (‘D:\ora\oralogs\redo02.log') SIZE 50M;

13. Oracle Managed File Management • Type of File Management (second of two methods) • Pros: • Automated control of 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

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

15. Oracle Managed File Management • Example • Initialization parameters: DB_CREATE_FILE_DEST = ‘C:\ora\oradata' DB_CREATE_ONLINE_LOG_DEST_1= ‘C:\ora\oralogs’ DB_CREATE_ONLINE_LOG_DEST_2= ‘D:\ora\oralogs’ • CREATE DATABASE command: CREATE DATABASE TECHNO92 MAXDATAFILES 100;

16. 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 • Must run other scripts later (catalog.sql,etc.)

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

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

19. Operating System Environment • On NT • Set the variable ORACLE_SID to use SVRMGR30. • Create the service and the password file with ORADIM80. C:\> ORADIM80 -NEW -SID u16 -INTPWD password -STARTMODE auto -PFILE ORACLE_HOME\DATABASE\initU16.ora

20. Preparing the Parameter File • . Create the new init<SID>.ora. $cp init.ora $ORACLE_HOME/dbs/initU16.ora • . Modify the initU16.ora by editing the parameters.

21. Editing the Parameter File db_name = U16 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 = (/disk1/control01.con,/disk2/control02.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 ...

22. Starting the Instance • . Connect as SYSDBA. • . Start the instance in NOMOUNT stage. SVRMGR> STARTUP NOMOUNT \ 2> PFILE=initU16.ora ORACLE instance started.

23. Creating the Database SPOOL creU16.log STARTUP NOMOUNT PFILE=initU16.ora CREATE DATABASE U16 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXLOGHISTORY 100 LOGFILE GROUP 1 ('/DISK3/log1a.rdo',/DISK4/log1b.rdo’) SIZE 1 M, GROUP 2 ('/DISK3/log2a.rdo',/DISK4/log2b.rdo’) SIZE 1 M DATAFILE '/DISK1/system01.dbf' size 50M autoextend on CHARACTER SET WE8ISO8859P1;

24. Oracle Database Assistant

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

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

27. OMF • Oracle-Managed Files • feature introduced in Oracle9i • 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 9i, when you dropped a tablespace, you would also have to remove the physical datafile associated with that tablespace. With Oracle9i, you can leave physical file management to the database itself by using OMFs

28. 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-Oracle9i 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!

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

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

31. OMF (Cont.) • Oracle9i Release 2 (9.2) changes: File Type Naming Convention Example Datafile o1_mf_%t_%u_.dbf o1_mf_tbs1_2ixfh90q_.dbf Tempfile o1_mf_%t_%u_.tmp o1_mf_temp1_6dygh80r_.tmp Redo logfile o1_mf_%g_%u_.log o1_mf_1_wo94n2xi_.log Control file o1_mf_%u_.ctl o1_mf_cmr7t90p_.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 "o1_mf_" prefix - and a ".dbf", ".tmp", ".log", or ".ctl" extension - and an "_" character immediately preceding the extension

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