Oracle Database Creation

Creation and deletion of Oracle databases CREATE DATABASE statement 2011-08-28 16:42:48

Category: Oracle

First, the concept

An Oracle database, the most basic composition (logic):

2. (1) system table Space 1--# for storage of "Data dictionary"
4. (2) Sysaux table Space 1--# for storage "not part of the data dictionary, but Oracle management requires the table"
6. (3) Redo log Logs 2--# for "Record all submitted data and actions"
8. A. Requires at least 2 groups, one group to be full, to switch to another group.
   You can then back up this set of logs that are full, and then empty the backup to the next use.
10. B. There can be multiple logs in a group, but their content is exactly the same. So you don't have to worry about
    A problem with a log file affects the later use of the recovery process.
14. (4) Temporary table Space 1--# for "Memory sort" when workspace space is not enough to use
16. (5) Undo Table Space 1--# for "Fallback uncommitted data stored on disk"

Second, practice

2. Create DATABASE {Oracle_name}---the same as Oracle_sid
4. User Sys identified by {password} and # not set, default is ' Change_on_install '
6. User system identified by {password} and # not set, default to "manager"
8. Maxlogfiles 5 --> # Maximum number of log groups
10. Maxlogmembers 5-- # Maximum number of members in a log group
12. Maxloghistory 1-- #(active in RAC environment, not in depth)
14. Maxdatafiles and # Maximum number of data files (relationship to "Db_files")? ）
    After 9i, the maximum data file count is controlled by the Db_files parameter. This parameter is in the only word.
16. LogFile Group 1 ('/u01/app/oracle/oradata/orcl/redo01a.log ',
    '/u01/app/oracle/oradata/orcl/redo01b.log ',
    '/u01/app/oracle/oradata/orcl/redo01c.log ') size 20M,
18. Group 2 ('/u01/app/oracle/oradata/orcl/redo02a.log ',
    '/u01/app/oracle/oradata/orcl/redo02b.log ',
    '/u01/app/oracle/oradata/orcl/redo02c.log ') size 20M,
20. Group 3 ('/u01/app/oracle/oradata/orcl/redo03a.log ',
    '/u01/app/oracle/oradata/orcl/redo03b.log ',
    '/u01/app/oracle/oradata/orcl/redo03c.log ') size 20M
22. --> # Create log groups, and members in groups
24. Character Set WE8ISO8859P1--# database character Set
26. National character Set UTF8--# Country Character Set
27. Extent management Local--# Specify how extended segments are managed in the system table space
    [local | dictionary]
    
    1. Use "Local management", the way to manage the throne. (See the "Oracle Architecture" section later)
       When set to local administration, you must specify both the size of the extension block (two ways):
       1. UNIFORM size (uniform size) {unit: "K" or "M"};
       2, Autoallocate (automatic distribution)
       The default is "Dictionary Management (Dictionary)" mode.
    2. 
       e.g//
    3. Extent Management Local Autoallocate
    4. Extent management Local uniform size 128k
    5. Extent Management Dictionary
31. DataFile '/u01/app/oracle/oradata/orcl/system01.dbf ' size 500M
    --> # Create "System Tablespace"
33. Sysaux datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf ' size 500M
    -# Create a "Sysaux table space"
35. Default Tablespace users
37. DataFile '/u01/app/oracle/oradata/orcl/users01.dbf ' size 500M autoextend on MaxSize Unlimited- # # Create default (permanent) tablespace
    When a user creates a table, it is placed in this table space if no table space is specified
39. Default temporary tablespace Temptbs
41. Tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf ' size 300M
    -# Create a "Default temp table Space"
43. Undo Tablespace Undotbs1
45. datafile  '/u01/app/oracle/oradata/orcl/undotbs01.dbf '  size 300m autoextend on MaxSize Unlimited                            -- > # create "undo table Space" (i.e.: Undo tablespace)  
                                        //  Note: The Undo table space name here must be associated with the
                                                 "Undo Tablespace" name in parameter file, creation fails
47. Set time_zone = ' +08:00 '; --> # Specify time zone

Iii. other clauses of CREATE database

1. Maxinstances-# The maximum number of instances a database can have (for RAC)
   
2. Noarchivelog/archivelog --# Non-archive mode/Archive mode
Force logging
--#

all operations on other objects need to be logged in addition to the temporary table space operation

3. Set Default Tablespace smallfile | Bigfile --# Specifying the physical implementation of a table space
   
4. Smallfile A table space can have one or more data files
   
5. Bigfile A table space can have only one data file
   (typically used in ASM environments)

Iv. Skills

(1) terminal window A:

1. sql> startup Nomount;

(2) terminal window B:

1. sql> tail-f $ORACLE _base/admin/$ORACLE _sid/bdump/alert_orcl.log

(3) terminal window A:

1. sql> CREATE Database ...;

(4) terminal window B:

1. (At this point, you can see the execution details of the CREATE DATABASE statement)

Reference: CREATE DATABASE statement for creation and deletion of Oracle databases

Oracle Database Creation