Oracle 手工建立資料庫

手工建立資料庫

 

1、  建立所需目錄

mkdir -p /opt/oracle/admin/doudou/adump

mkdir -p /opt/oracle/admin/doudou/bdump

mkdir -p /opt/oracle/admin/doudou/cdump

mkdir -p /opt/oracle/admin/doudou/dpdump

mkdir -p /opt/oracle/admin/doudou/udump

mkdir -p /opt/oracle/admin/doudou/pfile

mkdir -p /opt/oracle/oradata/doudou

mkdir -p /opt/oracle/flash_recovery_area/DOUDOU

mkdir -p /opt/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/doudou --（建立日誌）

set ORACLE_SID=doudou

2、   建立密碼檔案orapwd file=orapwdoudou password=oracle entries=10

3、   建立引數檔案

由系統自帶的init.ora來建立新的initdoudou.ora檔案

[ora@dg-pp dbs]$ cat init.ora |grep -v ^# |grep -v ^$ > initdoudou.ora

[ora@dg-pp dbs]$ cat initdoudou.ora

db_name=DEFAULT

db_files = 80                                                         # SMALL 

db_file_multiblock_read_count = 8                                     # SMALL 

db_block_buffers = 100                                                 # SMALL 

shared_pool_size = 3500000                                            # SMALL

log_checkpoint_interval = 10000

processes = 50                                                        # SMALL 

parallel_max_servers = 5                                              # SMALL

log_buffer = 32768                                                    # SMALL

max_dump_file_size = 10240      # limit trace file size to 5 Meg each

global_names = TRUE

control_files = (ora_control1, ora_control2)

 

修改：

# db_block_buffers = 100

# shared_pool_size = 3500000

db_name=doudou

control_files=(/opt/oracle/oradata/doudou/control01.ctl,/opt/oracle/oradata/doudou/control02.ctl,/opt/oracle/oradata/ doudou/control03.ctl)

sga_max_size=300M
sga_target=300M

*.audit_file_dest='/opt/oracle/admin/doudou/adump'

*.background_dump_dest='/opt/oracle/admin/doudou/bdump'

*.core_dump_dest='/opt/oracle/admin/doudou/cdump'

*.user_dump_dest='/opt/oracle/admin/doudou/udump'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

 

4、   create database 指令碼

create database doudou

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 10  --（ maxlogfile的大小，至少是 maxinstances的 2倍）

MAXLOGMEMBERS 5 --（最大值為 5）

MAXDATAFILES 200

DATAFILE

'/opt/oracle/oradata/doudou/system01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited extent management local -- （這必須有，建立 system 表空間）

sysaux datafile

'/opt/oracle/oradata/doudou/sysaux01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited

default temporary tablespace TEMP tempfile

'/opt/oracle/oradata/doudou/temp01.dbf' size 20m reuse autoextend on next 640k maxsize unlimited

undo tablespace UNDOTBS1 datafile

'/opt/oracle/oradata/doudou/undo01.dbf' size 20m reuse autoextend on next 5M maxsize unlimited

logfile

GROUP 1 ('/opt/oracle/oradata/doudou/redo1.dbf') size 10m,

GROUP 2 ('/opt/oracle/oradata/doudou/redo2.dbf') size 10m,

GROUP 3 ('/opt/oracle/oradata/doudou/redo3.dbf') size 10m

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

;

 

5、  建立資料字典

   sysdba執行

SQL> @?/rdbms/admin/catalog.sql

SQL> @?/rdbms/admin/catblock.sql

SQL> @?/rdbms/admin/catproc.sql

SQL> @?/rdbms/admin/catoctk.sql

SQL> @?/rdbms/admin/owminst.plb

 system執行

SQL> @?/sqlplus/admin/pupbld.sql

 

解釋指令碼用途

sys

Rem   CATALOG.SQL

Rem FUNCTION

Rem   Creates data dictionary views.

 

Rem    catblock.sql

Rem  FUNCTION  -  create views of oracle locks

 

Rem      catproc.sql

Rem    DESCRIPTION

Rem      Run all sql scripts for the procedural option

 

Rem      catoctk.sql - CATalog - Oracle Cryptographic ToolKit

Rem

Rem    DESCRIPTION

Rem      Contains scripts needed to use the PL/SQL Cryptographic Toolkit

Rem      Interface

 

[ora@dg-pp admin]$ more owminst.plb

/* create user ovm_sys for storing metadata. This will help in export */

/* CREATE ALL THE SYSTEM TABLES */

 

system

--   pupbld.sql

--

-- DESCRIPTION

--   Script. to install the SQL*Plus PRODUCT_USER_PROFILE tables.  These

--   tables allow SQL*Plus to disable commands per user.  The tables

--   are used only by SQL*Plus and do not affect other client tools

--   that access the database.  Refer to the SQL*Plus manual for table

--   usage information.

--

--   This script. should be run on every database that SQL*Plus connects

--   to, even if the tables are not used to restrict commands.

 

 

6、  其他過程省略

 

附表：錯誤日誌

 

1、

SQL> startup pfile=/opt/oracle/product/10.2.0/db_1/dbs/initresearch.ora

ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information

ORA-01078: failure in processing system parameters

 

解決：

註釋    #db_block_buffers = 100                                              

#shared_pool_size = 3500000   

 

2、

ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00200: control file could not be created

ORA-00202: control file: '/opt/oracle/oradata/doudou/control01.ctl'

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

解決：

這裡寫的 controlfiles  位置寫錯了。修改了引數檔案的位置 OK了

 

3、奇怪控制檔案從何而來？

解決：

其實在 create database的時候，會自動建立控制檔案的。（控制檔案的位置就是你引數檔案指定的位置）