/*** relational database ***/
E-R model (logic model represented by E-R) includes: entity / relationship / attribute
/ *** Oracle installation *** /
Oracle_Home: 'i: / oracle / ora90' Oracle_Home_name: OraHome90db_name: Database Name DB_NAME DB_DOMAIN: Global Database Name
1Data warehouse: Applied to the Decision Support System (DSS), to deal with a large number of complex queries 2General purpose: Applied to the database 3New Database: Customize Database 4Transaction Processing: Apply to online transactions Processing (OLTP), mainly to handle concurrent transactions
Create a database After modifying the DBA password: c: /> sqlplus / nologsql> Connect / as sysdbasql> ALTER USER UserName Identified by Password;
The default tablespace after the database is created: SystemToolStempundotbsusersindXexample
Create a database after the default server side / normal initialization parameters: spfile
/ * Manually create a database * /
1. Verify the instance name of the new database 2. Verify that DBA authentication mode 3. Create an initialization parameter file 4. Connect and start instance 5. Creating a database using CREATE DATABASE 6. Creating additional tablespace 7. Run the script Create a Data Dictionary View 8. Run Script Install Additional Features 9. Backup Database
Create an initialization parameter file: (Oracle_Home / Admin / Sample / Pfile / There is an initsmpl.ora, is an initialization parameter sample file) Below is an initialization parameter file used by a SID to ORA01.
# Cache area and database block size DB_BLOCK_SIZE = 4096 db_cache_size = 10971520
# 游标 位置 CURSOR_SHARING = Similar Open_CURSORS = 300
# Diagnostics and Tracking Information Set Background_Dump_Dest = i: / Oracle / Admin / ORA01 / BDUMP User_Dump_Dest = i: / Oracle / Admin / ORA01 / UDUMP
# Control file settings control_files = ("d: /oracle/oradata/ora817/control01.ctl",
"D: /oracle/oradata/ora817/control02.ctl",
"D: /oracle/oradata/ora817/control03.ctl")
# Filing mode setting log_archive_start = true log_archive_dest_1 = 'location = f: / oracle / oradata / backup / arc1' log_archive_dest_2 = 'location = f: / oracle / oradata / backup / arc2' log_archive_format = arch_% t_% s.arc
# Version compatibility and database SID settings compatible = 8.1.5.0.0 db_name = ORA01
# 网 网络 域 认 认 方式 方式 方式 设置 设置 _ om 方式 方式 方式 方式 方式 方式 方式 方式 方式 方式
# 网 网络 标 = = = a = = = 设置 5 5 5 5 5 5 5 5 5 5 5 5 5
# User Process Settings Processes = 150
# 排 区 区 设置 Sort_Area_Size = 524288
# 回退 段 and revocation table space setting undo_management = auto undo_tablespace = undotbs
Connection and start instances: c: /> sqlplus / nologsql> connection / as sysdbasql> Startup Nomount
SQL> CREATE DATABASE ORA01 CONTROLFILE REUSE MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 DATAFILE: creating a database CREATE DATABASE 'I: /ORACLE/ORADATA/ORA01/SYSTEM01.DBF' SIZE 325M REUSE UNDO TABLESPACE undotbs DATAFILE 'I: / ORACLE /ORADATA/ORA01/UNDOTBS01.DBF 'SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temptbs1 CHARACTER SET ZHS16GBK NOARCHIVELOG LOGFILE GROUP 1 (' I: /ORACLE/ORADATA/ORA01/REDO01.LOG ') SIZE 100M, GROUP 2 ('I: /oracle/oradata/ora01/redo02.log') size 100m, group 3 ('i: /oracle/oradata/ora01/redo03.log') size 100m;
Create additional tablespace: Create TableSpace Uses logGGing DataFile 'i: /oracle/oradata/ora01/Users01.dbf' size 25m reuse autoextens on Next 1m maxsize unlimited extent management local;
Create TableSpace Index Logging DataFile 'i: /oDex01.dbf' size 25m Reuse Autoextend On Next 1m MaxSize Unlimited Extent Management Local
Run the script Create a Data Dictionary View: Use the Create Database created database, only after the additional tablespace and data dictionary can work properly. Do the following script: SQL> Connect Sys / Sys password as sysdbasql> @ $ Oracle_home / rdbms / admin / catalog.sql; (Create a data dictionary view, including V $ Dynamic performance view) SQL> @ $ oracle_home / rdbms / admin / catproc.sql; (establish support for PL / SQL)
Create a server-side parameter file SQL> create spfile = 'i: /oracle/ora90/database/spfileora01.ora' from pfile = 'i: /oracle/admin/ora90/pfile/initora01.ora'; created server-side initialization parameters Located in the default location, so you don't have to specify spfile / *** *** when starting the database.
An initialization parameter file must be provided when starting the database (whether the text initialization parameter file or server-side initialization parameter file),
When performing a Startup statement, it will look for initialization parameter files in order as follows:
* First check if the SPFile parameter specifies the server-side initialization parameter file * and check if the PFILE parameter specifies the text initialization parameter file * If you do not use the spfile parameter or PFILE parameter, you will find the default name to the default name. * If you do not find the default server-side initialization parameter file, you will find the text initialization parameter file for the default name in the default location.
/ * ALTER SYSTEM statement affects the range of ALTER System Set ..... Take later SCOPE options, named: 1.scope = spfile The modification of the parameter is recorded only in the server-side initialization parameter file, after modification The parameter is only after the next started database
Effective, while applicable to dynamic and static initialization parameters
2.Scope = Memory only records the parameters only in memory, and the dynamic initialization parameters will take effect immediately, and cannot be used for static parameters, and the modification is not
In the initialization parameters of the server side, the next started database is still using the parameter settings before modifying
3.Scope = Both Suitable for dynamic initialization parameters, modifications to parameters will be recorded in memory and server-side initialization parameters, and will apply the modified parameter setting after the next startup
Note: When you want to modify the static initialization parameters, you can only set the scope option to spfile.eg:4qlfalter system set db_files = 100 scope = spfile;
Export server-side initialization parameter file: SQL> CREATE PFILE = 'i: /ore/database/temppfile/expinit.ora' from spfile = 'i: /oacle/database/myspfile.ora'; or SQL> Create Pfile from spfile; At this time, the default server-side initialization parameter file is exported to the text initialization parameter file with the default name on the default location.
Viewing Initialization Parameters SQL> Show Parameters
Specifies the size of the database block (the size of the database block must be an integer multiple of the operating system database block size, usually 4KB or 8KB, the database block size cannot be changed after the database is established, and the usual database block is larger than the operating system block. Effective improvement of the I / O performance of the database) DB_BLOCK_SIZE = 4096
The DB_CACHE_SIZE parameter specifies the size of the database cache in the SGA area, using the db_cache_size parameter in Oracle 9i instead of previous versions
DB_BLOCK_BUFFERS parameters, DB_CACHE_SIZE parameters are dynamic parameters, the db_block_buffers parameter is the data of the cache area by setting the data of the data block, and the db_cache_size parameter is directly specified to specify the cache area size. Db_cache_size = 32m
Specifying the maximum concurrent process number The minimum of the processes parameter is 6 (each database is at least 5 daemons, plus a user process) Processes = 150
