|
Even if your company standardizes on using the GUI tools, such as DBCA, it would be a good idea to create a database manually at least once, not only to help you with this test (although it will help you to pass this portion of the exam), but so that you have an idea of what the DBCA is doing behind the scenes. This knowledge will help you to make more informed decisions in creating your databases, going forward, regardless of how you create them. Step 1: Decide on Your Instance's System Identifier (SID)The first step in creating the database is to determine the System Identifier (SID). This is what the environment will be set to when your database starts up and shuts down, and what the instance processes will have in their names as unique identifiers. Step 2: Create the Initialization Parameter FileIn the ORACLE_HOME/dbs directory, a set of generic initialization files are available for you to copy and customize to your situation. There is one for a regular OLTP database, one for a data warehouse database, and one for a combination database. Find the one that most closely matches what you intend your database to be used for, copy it to another name (saving the template for reuse later), and make your specific alterations to the copy. Name the copied init.ora file init<SID>.ora because this is the default naming convention that the Oracle server looks for when it tries to start up the instance. Edit this file's parameters so that your database and your instance will run effectively on your system. Let's address the parameters in the initialization file in greater depth:
Step 3: Connect to and Start the InstanceConnect to the instance as SYSDBA either using operating system authentication or the password file method of authentication and start up the instance using the STARTUP command. >sqlplus '/ as sysdba'
Because you do not yet have a database attached to the instance, you need to start up the instance in NOMOUNT state: SQL> startup nomount; The instance is now started and ready for the CREATE DATABASE command. There are several options for starting up the database. The following are the ways that you can start up the instance and the database, and what each means:
Step 4: Issue the CREATE DATABASE StatementTo create the database, issue the CREATE DATABASE command. The CREATE DATABASE command creates data files, control files, redo log files, the system tablespace along with the data file associated with it, and a system rollback segment. It creates the tables that underlie the data dictionary, assigns the character set to the database, optionally sets the database time zone, and mounts and opens the database for use. The general format follows: CREATE DATABASE [database name] [CONTROLFILE REUSE] [LOGFILE [GROUP integer] file specification] [MAXLOGFILES integer] [MAXLOGMEMBERS integer] [MAXLOGHISTORY integer] [MAXDATAFILES integer] [MAXINSTANCES integer] [ARCHIVELOG|NOARCHIVELOG] [CHARACTER SET charset] [NATIONAL CHARACTER SET charset] [DATAFILE filespec [AUTOEXTEND]] [DEFAULT TEMPORARY TABLESPACE tablespace filespec] [UNDO TABLESPACE tablespace DATAFILE filespec] [SET TIME_ZONE [time_zone_region]]; Database is the name of the database that you are creating. If the name of the database is omitted, the initialization parameter DB_NAME is used. The database name should be the same as the DB_NAME parameter. Control file reuse specifies that an existing control file identified by the control file parameter in the initialization file should be reused. Log file group identifies the names of the log files to be used and the group to which they belong. MAXLOGFILES specifies the maximum number of redo log files that can ever be created in the database. MAXLOGMEMBERS specifies the maximum number of redo log file members that any given log file group can have in the database. MAXLOGHISTORY specifies the maximum number of archive redo logs for automatic media recovery. AUTOEXTEND enables or disables the automatic extension of SYSTEM tablespace's data files in the database. MAXDATAFILES controls the initial sizing of the data file section of the control file at the time when the CREATE DATABASE or CREATE CONTROLFILE commands are issued. If an attempt is made to add a new file with a number greater than the value set to the MAXDATAFILES parameter, but less than the DB_FILES parameter, it causes the control file to expand automatically so that the data files section can accommodate the new files. The MAXINSTANCES parameter is optional and is primarily used for Real Application Clusters (RAC) environments. The default, if left unspecified, is 1. The optional ARCHIVELOG|NOARCHIVELOG statement provides the option for the database's redo log files to automatically initially be archived. This is an optional statement at database creation time. NOARCHIVELOG is the default if ARCHIVELOG is not specified. If, after database creation, you choose to change the archive log mode of the database, you can use the ALTER DATABASE command to change between the two. ARCHIVELOG establishes that the redo logs can be reused, but only after they have been archived. NOARCHIVELOG establishes that the redo logs can be reused without archiving their contents. CHARACTER SET is the character set that the database uses to store the data. The NATIONAL CHARACTER SET parameter is optional and is used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.
DATAFILE, when connected directly to the CREATE DATABASE statement, is the file specification used to specify the data files to be used for the system tablespace. DEFAULT TEMPORARY TABLESPACE instructs the database to assign this tablespace to all users created without having a temporary tablespace specified. UNDO TABLESPACE creates and names the undo tablespace and the associated data files that should be used to store undo records for the database if you have specified (as Oracle suggests) UNDO_MANAGEMENT=AUTO in the initialization parameter file. A working example of a CREATE DATABASE script follows: CREATE DATABASE mydb1 CONTROLFILE REUSE LOGFILE GROUP 1 ('/mydatabases/mydb1/log01a.log', '/mydatabase02/mydb1/log01b.log') SIZE 50M, GROUP 2 ('/mydatabases/mydb1/log02a.log', '/mydatabase02/mydb1/log02b.log') SIZE 50M, GROUP 3 ('/mydatabases/mydb1/log03a.log', '/mydatabase02/mydb1/log03b.log') SIZE 50M, GROUP 4 ('/mydatabases/mydb1/log04a.log', '/mydatabase02/mydb1/log04b.log') SIZE 50M DATAFILE '/mydatabases/mydb1/mydb1_system.dbf' 250M AUTOEXTEND ON MAXSIZE 500M UNDO TABLESPACE mydb1_undo1 DATAFILE '/mydatabases/mydb1/mydb1_undo1.dbf' 50M DEFAULT TEMPORARY TABLESPACE mydb1temp tempfile '/mydatabases/mydb1/mydb1temp1.dbf' size 75M EXTENT MANAGEMENT LOCAL CHARACTER SET US7ASCII MAXLOGFILES 10 MAXLOGMEMBERS 10 MAXLOGHISTORY 1 MAXDATAFILES 500;
If you are using Oracle Managed File (OMF) systems and you have specified the appropriate OMF initialization parameters in the parameter file (DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n), the following statement creates the database: STARTUP NOMOUNT CREATE DATABASE DEFAULT TEMPORARY TABLESPACE mydb1temp; At this point, your database now contains data files (at least a limited number of them), control files, redo log files, the SYS and SYSTEM users, and all the internal data dictionary tables, but no data dictionary views. You can see the dynamic performance views, such as V$LOGFIEL, V$CONTROLFILE, and V$DATAFILE. If you have not yet changed the default passwords for SYS (change_on_install) and SYSTEM (manager), now is a good time to do it. Step 5: Run Scripts to Build Data Dictionary ViewsNow that you have a database, you need to complete the data dictionary views and procedures. In the ORACLE_HOME/rdbms/admin directory, a pair of scripts need to be run to complete the database's data dictionary creation. CATALOG.sql creates the data dictionary views from which you can gain insight into the contents of the database. CATPROC.sql creates the objects required to use PL/SQL. Step 6: Run Scripts to Install Additional OptionsThis step is optional. However, if you need to add any of the optional products, such as Spatial or InterMedia, you can now run the scripts to add these products to your database. You can locate the scripts and the following procedures in the Oracle documentation. Step 7: Create a Server Parameter FileThis step is optional, although highly recommended by Oracle. Your Oracle database was created by starting the instance with a parameter file, or PFILE. Because it is editable, you can migrate, easily, your new database to using a server parameter file. Creation of the server parameter file (or spfile) is accomplished by issuing the following statement: CREATE SPFILE FROM PFILE; This command looks in the default location (ORACLE_HOME/dbs) for an initialization parameter file (or PFILE) with the default name. If your database is named mydb1, the default name would be initmydb1.ora. The command creates an spfile with the default name in the same default location unless otherwise specified.
Step 8: Back Up the DatabaseNow that your database is created, take this chance to shut down the database and take a cold backup of the new database. Step 9: Create Additional TablespacesAfter the backups are finished, you can restart the database and create new tablespaces, tables, and users; add data; and in general, open the new database for business. |
|