3.4 Create a database ----------------- - Creation of database. The steps required to create database: 1. Organize the contents of the database. 2. Design the structure of the database to reduce contentions and fragmentation. You should select right places for data, control and redo log files. 3. Prepare the operating system environment for the database creation and decide on the unique instance name. Instance name is determined by an environment variable ORACLE_SID on UNIX boxes, logical ORA_SID. (SID stands by system identifier). For UNIX: $ ORACLE_SID=;export ORACLE_SID $ echo ORACLE_SID. Notes: - for some platforms like NetWare or OS/2 only one Oracle instance per server machine is allowed. For those platforms terms "instance" and "SID" do not exist; - database name (DB_NAME INIT.ORA param.) and SID must be of the SAME VALUE for non parallel databases. 4. Copy and edit the parameter file INIT.ORA. Actual name of this file usually INIT.ORA You should prepare: - INIT.ORA parameters: CONTROL_FILES DB_BLOCK_SIZE - size of the dbatabase block. If you have no reasons to modify it leave it default. CREATE DATABASE parameter DB_NAME and, optionally, DB_DOMAIN - CREATE DATABASE parameter DB_BLOCK_BUFFERS - usually default value is insufficient to run all the installation script. (May be changed at any time) SHARED_POOL_SIZE - see discussion above DML_LOCKS - number of simultaneous system-wide locks (Max Number of DMLs per transaction multiply by max number of transactions in system OPEN_CURSORS - the more the better if you use Forms LOG_CHECKPOINT_INTERVAL - see checkpoints and log switch discussion LOG_CHECKPOINT_TIMEOUT - ---- "" --------------- SEQUENCE_CACHE_ENTRIES - the total number of sequence numbers that are cached in the SGA at one time. if sequence is created with NOCACHE option, setting this parameter will have no effect on the sequence PROCESSESS - the number of processes (including background proc) allowed at one time SESSIONS - the number of user and system sessions allowed at one time. Default value is PROCESSES * 1.1. Unless your applications create concurrent recursive sessions the default should be sufficient. SORT_AREA_SIZE - will affect the performance of select statements (see appropriate section) ENQUEUE_RESOURCES - represents the number of resources that can be locked by lock manager It is convenient, especially in parallel server environment, to use IFILE= INIT.ORA param. which invoke file . - monitoring current INIT.ORA parameters: - SHOW PARAMETER command from SQL*DBA - examine V$PARAMETER tables - all undocumented INIT.ORA parameters (those which begin with _ sign, e.g. _DB_BLOCK_WRITE_BATCH) are can be seen from SYS.X$KSPPI (you mast connect as SYS) 5. Start the instance (CONNECT INTERNAL and STARTUP NOMOUNT command from SQL*DBA). Examples: $ sqldba mode=line (or lmode=y) connect internal (or connect UN/PW as SYSDBA or SYSOPER) sturtup nomount 6. Execute the CREATE DATABASE which: - creating and/or initializing the control file and redo log files; - don't forget CHARACTER SET parameter, if character set differs from US7ASCII. Valid character sets for russian are CL8ISO8859P5, RU8PC866, CL8MSWINDOW31 (CL8MSWIN1251 - new name), RU8PC855, CL8MACCYRILLIC, CL8MACCYRILLICS, RU8BESTA for ASCII based platforms and CL8EBCDIC1025 and CL8BS2000 for EBCDIC based ones. - creating new data files or erasing data that existed in previous datafiles; - creating the data dictionary containing: - all the necessary dictionary tables in user schema SYS (script SQL.BSQ) - users: SYS (password change_on_install) and SYSTEM (manage r); - filling the data dictionary created: views, public synonyms and grants (CATALOG.SQL), procedural script (CATPROC.SQL). You can do this manually or CREATE DATABASE will do it for you if INIT.ORA parameters: INIT_SQL_FILES= (SQL.BSQ,... CATPROC.SQL, CATALOG.SQL,...,...). for RDBMS < 7.1.3 !!! INIT_SQL_FILES is not longer supported for RDBMSs beginning with 7.1.3 There may be a problem in some releases of Oracle with CATPROC.SQL script which creates the data dictionary catalog views that necessary for procedural objects and snapshots. But attempting to run this script by itself will result in errors. CATPROC.SQL is dependent on two other scripts: STANDARD.SQL (PL/SQL packages for procedural option) and DBMSSTDX.SQL (extensions to STANDARD package). The CATPROC.SQL script must be run after STANDARD.SQL and before DBMSSTDX.SQL. Rely upon INSTALLER to create the new instances and databases. In any case files crdb.sql and crdb2.sql (UNIX) or BUILD_DB.SQL (NetWare) generated by installer may be used. Other scripts which may need to be included in the list ($ORACLE_HOME/rdbms/admin): - CATALOG6.SQL - set up V6 comatable dictionary views - EXPVEW5.SQL - EXP/IMP V5 views - CATEXP6.SQL - EXP/IMP V6 views - UTLXPLAN.SQL - PLAN_TABLE for EXPLAIN PLAN - UTLMONTR.SQL - grants access to the dynamic performance tables to PUBLIC - UTLBSTAT.SQL - creates a set of statistics tables at the start of performance tests - DBMSLOCK.SQL - package allowing to create user defined locks. - DBMSALTR.SQL - creates DBMS alerts procedures which allow applications to be notified when whenever values of interest in the database change. - DBMSPIPE.SQL - Database pipes. Allows sessions within the same instances to communicate with each other. - DBMSOTPT.SQL - allows users to output messages from procedures and triggers. - DBMSDESC.SQL - allows developers to describe the arguments of a stored procedure. - DBMSMAIL.SQL & UTLMAIL.SQL- creates packages needed to send mail via Oracle*Mail. - UPBLD for product user profile (in SQL*PLUS directory) - CATSTAT.SQL - executes ANALYZE command on data dictionary for cost-based optimization - UTLCHAIN.SQL - creates table to collect chained rows after ANALYZE command These files are located in $ORACLE_HOME/dbs and $ORACLE_HOME/rdbms/admin on UNIX boxes. Notes: - if database name is specified in CREATE DATABASE STATEMENT it must be the same as in INIT.ORA file. - Usually an initial database is created as a part of the installation procedure by installer. In this case the installer issues CREATE DATABASE and build new database during installation (UNIX and other multiuser platforms). However, on some platforms, for example NetWare or OS/2, installer does NOT create new "fresh" database, but instead it copies pre-created data files for SYSTEM tablespace, control and redo log files from distribution media. In this case DBA can't control some parameters, for example, CHARSET parameter. In this case (1) you better to create a new database after installation or (2) login as SYS and issue: update SYS.PROPS$ set Value$ = where Name = 'NLS_CARACTERSET'; and restart instance. Be careful! If you specify wrong charset_needed, then NO WAY to restart database! 7. Ensure the safety of the database by creating muliplexed redo log files and control files. 8. Define the data dictionary and views to monitor database. 9. Define and create tablespaces and rollback segments. 10. Create user's schema objects. Example of BUILDALL.SQL file: >>> Begin of SQL*DBA script rem You should use SQLDBA MODE=LINE rem rem This script is used in Personal Oracle environment rem and supposes that: rem rem directory %rdbms71% contains INIT.ORA file. rem Environment variable %rdbms71% from rem \windows\ORACLE.INI references rem directory \ORAWIN\rdbms71. INIT.ORA file rem is located there and that's why rem PFILE parameter is not presented in rem STARTUP NOMOUNT command below. rem directory \orawin\dbs contains database, rem redo log files and control file (value of rem CONTROL_FILES parameter in INIT.ORA). rem Environment %oracle_home% points to \orawin rem directory %oarcle_home%\admin\ contains all the rem needed system scripts. rem rem This file must be run out of the directory containing the rem initialization file. rem rem Instance startup (background processes + loading SGA) startup nomount rem Let's create log file set echo on spool %rdbms71%\trace\build.log rem Create database create database oracle controlfile reuse logfile '%oracle_home%\dbs\wglog1.ora' size 200K reuse, '%oracle_home%\dbs\wglog2.ora' size 200K reuse datafile '%oracle_home%\dbs\wgsys.ora' size 10M reuse maxdatafiles 25 maxlogfiles 21 maxlogmembers 3 /* maxloghistory 200 for parallel server (Max # of archived redo log */ character set RU8PC866; rem After creation database is MOUNTED and OPENED and rem now we can run the necessary installation scripts: rem Two users exist now: rem system/manager rem sys/change_on_install rem For V7 catalog @%rdbms71%\admin\catalog rem For V6 catalog @%rdbms71%\admin\catalog6 rem For procedural option @%rdbms71%\admin\catproc rem for snapshots rem Some additional views rem Export/import tables/views @%rdbms71%\admin\catexp6 rem Vies for Oracle locks (check this line in rem installer output crdb2.sql) @%rdbms71%\admin\catblock rem And now we will create synonyms of dictionary views rem for the SYSTEM account. All the DBAs should have these synonyms. connect system/manager @%rdbms71%\admin\catdbsyn rem execute V7PUP.SQL for oracle tools rem To prevent message "product user profile is not loaded" rem upon SQL*Plus startup @%oracle_home%\dbs\v7pup.sql rem Create rollback segs rem Let's create temporary rollback segment in SYSTEM rem tablespace to allow to create other then SYSTEM rem tablespaces: create rollback segment TEMP; rem and make it active: alter rollback segment TEMP online; rem rem Let's create tablespace for rollback segments create tablespace RBS datafile '%oracle_home%\dbs\rbs.ora' size 18M; rem Create two private rollback segments in RBS rem tablespace and activate them (online): create rollback segment RBS1 tablespace RBS storage (initial 100k next 100k minextents 60 optimal 6M); alter rollback segment RBS1 online; create rollback segment RBS2 tablespace RBS storage (initial 100k next 100k minextents 60 optimal 6M); alter rollback segment RBS2 online; rem Don't forget insert into INIT.ORA the line: rem ROLLBACK_SEGMENTS = (RBS1, RBS2) rem This line will alter these two rollback rem segments ONLINE at startup time as rem ALTER ROLLBACK SEGMENT ONLINE; command rem does. rem rem Now we drop temporary rollback segment created before alter rollback segment temp offline; drop rollback segment temp; rem rem Let's create temporary tablespace. Put a special rem attention to STORAGE clause. create tablespace TEMP datafile '%oracle_home%\dbs\temp.ora' size 20M; alter tablespace TEMP default storage (initial 200k next 300k pctincrease 0); rem rem Tablespace for Oracle tools (forms, browser ...) create tablespace TOOLS datafile '%oracle_home%\dbs\tools.ora' size 30M; rem Tablespace For Users create tablespace USERS datafile '%oracle_home%\dbs\users.ora' size 30M; rem rem Change SYSTEM's default and temporary tablespace Alter user SYSTEM default tablespace TOOLS temporary tablespace TEMP; Alter user SYS default tablespace TOOLS temporary tablespace TEMP; rem rem Change the password in SYS account alter user SYS identified by MANAGER; rem And shutdown database connect internal shutdown rem Close log file spool off >>> End of SQL*DBA script - Altering database Useful commands: ALTER DATABASE OPEN RESETLOGS - resets the current log sequence number to 1 and invalidates all the redo entries in the online and archived redo files. - Tablespaces: creation, dropping, altering, enlarging, taking tablespace online/offline, renaming files in tablespace. - Rollback segments: creation, altering, dropping, taking online/offline. Types of rollback segments. Using extent space utilization parameters for rollback segments. OPTIMAL storage parameter. Contention for rollback segments. Optimal number of extents in rollback segment. Optimal extent size of rollback segment.