Oracle Database Management
Creation of an Oracle database can erase an exisiting database. Especially important to database
creation is selection of data block size. Data block size cannot be changed after database creation.
The database is always read and written in blocks. A larger block size means that for every item read
from the database that a larger chunk is read from disk. Thus large individual item storage requirements
are suited to larger block sizes, for instance a database with heavy multimedia content.
- database_name - maximum of eight characters.
- AUOTEXTEND - automatically extends datafiles as required. This can use up large amounts of disk space since when records are deleted from datafiles already allocated extended space is not reclaimed by the operating system or released by Oracle.
- MAXLOGFILES, MAXDATAFILES, MAXINSTANCES, MAXLOGMEMBERS & MAXLOGHISTORY - should be set to the maximum ever to be expected.
- CHARACTER SET - operating system dependant character set that data will be stored in.
- ARCHIVELOG or NOARCHIVELOG - sets archive logging.
- EXCLUSIVE or PARALLEL - parallel mode allows one or more instances to have access to the same database. A database must be altered into parallel mode after creation.
- NATIONAL CHARACTER SET - defaults to the database character set. Can not be altered after database creation.
As shown below, when the CREATE DATABASE command is executed the following items are created.
- System tablespace
- Data dictionary tables
- System rollback segment
- Redo log files
- Data files
- Control file(s)
In Oracle8i there are three generally different types of databases.
- Online Transaction Processing (OLTP) - many users and many transactions both reading and writing the database. Recovery, availability and concurrency are important.
- Data Warehouses / Decision Support Systems (DSS) - generally read-only, fast-access and very available requirements such as the internet.
- Combinations of the above two - for instance, an interactive internet site such as online trading and reporting.
Creating a Database Step-by-Step
- Select a database name and SID (instance name).
- Create an initialisation parameter file.
- Execute sqlplus with the /nolog option, connect as internal/oracle AS SYSDBA; and startup nomount;. This starts up the SGA and the background processes.
- Create the database using the create database command. Database creation causes the following to occur.
- Datafiles creation.
- Control files creation.
- Redo logs creation.
- SYSTEM tablespace and SYSTEM rollback segment creation.
- Data dictionary creation.
- SYSTEM and SYS users (schemas) creation.
- Character set data storage setting.
- Mount and open the database. The database is mounted in NOARCHIVELOG and EXCLUSIVE modes.
- Run the catalog.sql and catproc.sql scripts to create meta-data views plus dynamic performance views and create PL/SQL supplied packages respectively.
- Backup the database NOW !!!
- Create a rollback tablespace and create a second rollback segment in the rollback tablespace.
- Create a users or data or multiple users, data or applications and tools tablespaces.
- Create a temporary tablespace for sorting use.
- Bring all the new tablespaces and datafiles online.
... OR use the database creation tool that comes packaged with Oracle8i. I find this tool very useful. Even if you do not create the
database exectly right the first time then it can always be altered at a later stage, atleast in development of that database.
If you need to drop a database shutdown the database and remove all physical files.
CREATE DATABASE Command Failure
When a CREATE DATABASE command failes do the following.
- Shut down the instance.
- Delete any files created by the failed CREATE DATABASE command.
- Correct the error in the CREATE DATABASE command.
- Restart the instance.
For any datafiles or redo logs already in existence use the REUSE option in the CREATE DATABASE
command in order to reuse already existing files.
- database_name - upto 8 characters.
- filespec - <file_name> SIZE n K/M REUSE
- file_name - full path name.
- SIZE - bytes, kilobytes or megabytes, 500K is the default.
- K or M - (K)ilobytes or (M)egabytes.
- REUSE - reuse an existing file for a file the proper size.
- file_name - full path name.
- MOUNT - database available for some DBA functions in exclusive (default) or PARALLEL mode. PARALLEL mode is present in a shared server.
- STANDBY DATABASE - a standby database is left in recovery mode where archive logs are applied to keep it current. This provides automatic failover instance for the current database.
- OPEN - mount and open a database for general use. Use RESET LOGS if an incomplete recovery was performed. NORESET LOGS is the default.
- ACTIVATE STANDBY DATABASE.
- ADD LOGFILE THREAD - adds a thread or redo log to a PARALLEL instance.
- ADD LOGFILE MEMBER.
- CLEAR LOGFILE - reinitialises a specified online redo log. If it is necessary to
clear a log containing redo items after the database checkpoint then incomplete media recovery will be
necessary. The current redo log of an open thread can never be cleared. The current log of a closed
thread can be cleared by switching logs in the closed thread. If the CLEAR LOGFILE is interrupted by
a system or instance failure then the database may hang. If the database hangs then the CLEAR LOGFILE
command must be reissued on restart. If the failure occured due to I/O errors accessing a single
log group member then the offending member can be dropped and other members can be added.
- UNARCHIVED - reuse a redo log which was not archived. This option will make backups unusable if the redo log is required for recovery.
- UNRECOVERABLE DATAFILE - if the tablespace has a datafile offline and the unarchived log must
be cleared to bring the tablespace online. The datafile and the entire tablespace has to be dropped when
the CLEAR LOGFILE has been completed.
- DROP LOGFILE.
- DROP LOGFILE MEMBER.
- RENAME - renames a database file. Note that when renaming datafiles ensure to take the datafile OFFLINE after the taking the tablespace OFFLINE. Then rename the datafile physically in the operating, bring the datafile back ONLINE and finally the tablespace back ONLINE.
- RECOVER - puts the database into recovery mode.
- BACKUP CONTROLFILE.
- Make a recoverable copy of the control file (TO file_name).
- Make a script to rebuild the control file (TO TRACE).
- CREATE DATAFILE - The filespec specifies the name and size of the new datafile. If the AS clause is omitted then a new file with the same name and size is created.
- CREATE STANDBY CONTROLFILE - standby database control file.
- DATAFILE - allows manipulation of datafiles such as resizing, turning autoextend on or off and setting backup status against the instances' datafiles.
- ENABLE THREAD/DISABLE THREAD - enabling and disabling of redo log threads in parallel databases.
- RESET COMPATIBILTY - resets to older database versions. Redo logs become unusable.
- SET DBLOW/DBHIGH/DBMAC - Secure Oracle only.
- RENAME GLOBAL_NAMETO - changes global database name. Does not change data with regards to the global name in remote instances, connect string or database links.
CREATE CONTROLFILE [REUSE] [SET] DATABASE database name log clause datafile clause character set clause
The ALTER SYSTEM command is used to alter the characteristics of the database environment and not to alter database structures. The ALTER SYSTEM
command can be used to dynamically tune many of the database initialisation parameters whilst the database is mounted and opened.
- RESOURCE_LIMIT - TRUE or FALSE to set the use of resource limits.
- GLOBAL_NAMES - TRUE or FALSE to set use of global names in database links.
- MTS_SERVERS - number of enabled shared server processes upto MAX_SERVERS.
- MTS_DISPATCHERS - dispatcher network protocol. MAX_DISPATCHERS is a sum of all dispatchers for all protocols.
- SWITCH LOGFILE - switches active log file groups. To drop a redo log member use the
ALTER DATABASE DROP LOGFILE MEMBER command. If the database is in ARCHIVELOG mode you can jot drop an
online member until it has been archived. If the group is active you must force a log switch before
dropping one of it's members. You can not drop the last valid member of a group. Dropping a member
does not remove its operating system file. When a log switch occurs the LGWR stops writing to the current
online redo log group, a checkpoint occurs and the DBWR writes the dirty buffers to the datafiles.
The DBWR process writes to the datafiles at every log switch.
- CHECKPOINT - GLOBAL for all open database instances or LOCAL for current instance.
- CHECK DATAFILES - datafile access verification. GLOBAL for all open database instances or LOCAL for current instance.
- ENABLE/DISABLE RESTRICTED SESSION - restricts to users with restricted session privelages. Disabling allows all users to log into the database.
- ENABLE/DISABLE RESTRICTED RECOVERY.
- ARCHIVE LOG - enables automatic archiving.
- SUSPEND and RESUME - suspend all I/O and queries (tablespaces must be in hot backup mode. Thus database copies can be made when the database is suspended. Resuming the database will allow I/O activity again.
- FLUSH SHARED POOL - clears the SGA shared pool thus clearing the cached data dictionary, SQL and PL/SQL areas. Currently executing items are not removed.
The ARCHIVE LOG Clause
The ALTER SYSTEM command can also be used to make changes to database archive logging.
ALTER SYSTEM ARCHIVE LOG CLAUSE;
ARCHIVE LOG clauses:
[SEQUENCE n] [TO <location>]
[CHANGE n] [TO <location>]
[CURRENT] [TO <location>]
[GROUP n] [TO <location>]
[LOGFILE <filename>] [TO <location>]
[NEXT] [TO <location>]
[ALL] [TO <location>]
[START] [TO <location>]
- THREAD - current instance redo log thread affected as default.
- SEQ - archives the nth redo log group.
- CHANGE - system change number (SCN) for the transaction to be archived.
- CURRENT - causes all non-archived redo log members of the current group to be archived.
- GROUP - manually archives redo logs in specified group. If THREAD and GROUP specified group must belong to specified thread.
- LOGFILE - manually archives group filename, if thread provided file must be in group contained in specified thread.
- NEXT - forces manual archiving of oldest online redo log requiring it. If no thread given oldest available unarchived redo log is archived.
- ALL - archives all online archive logs which are part of the current thread. All unarchived logs from all threads are archived if no thread given.
- START - starts automatic archiving of redo log file groups and only applies to the thread assigned to the current instance. Also the control file is modified by START such that archive status is recorded and used on the next database restart.
- TO - full path name of where to archive logs.
- STOP - disables automatic redo log file group archiving. Applies to the current instance only.
CREATE DATABASE LINK
CREATE [SHARED] [PUBLIC] DATABASE LINK database link
[ CONNECT TO (CURRENT USER | user name IDENTIFIED BY password [AUTHENTICATED BY user name IDENTIFIED BY password] ]
[ AUTHENTICATED BY user name IDENTIFIED BY password ]
[ USING connect string ]
DROP DATABASE LINK
DROP [PUBLIC] DATABASE LINK database link