手工创建oracle数据库

1.停止数据库

sqlplus /nolog
conn / as sysdba;
shutdown

 

2.创建sid，修改oracle用户环境变量.bash_profile 文件
返回oracle用户根目录
cd 回车
vi .bash_profile
内容如下：
PATH=$PATH:$HOME/bin
ORACLE_BASE=/opt/oracle/10g;   export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE;   export ORACLE_HOME
ORACLE_SID=ora10g;   export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH

export PATH
unset USERNAME
保存退出。
使变量生效：
. .bash_profile

3.创建目录：
诊断目录

mkdir -p /opt/oracle/admin/ora10g
mkdir -p /opt/oracle/admin/ora10g/adump
mkdir -p /opt/oracle/admin/ora10g/bdump
mkdir -p /opt/oracle/admin/ora10g/cdump
mkdir -p /opt/oracle/admin/ora10g/udump
mkdir -p /opt/oracle/admin/ora10g/pfile
mkdir -p /opt/oracle/admin/ora10g/initsql

 

数据文件目录：

mkdir -p /opt/oracle/oradata/ora10g
mkdir -p /opt/oracle/oradata/ora10g/archive

 

4.在/opt/oracle/admin/ora10g/pfile目录下创建参数文件：
initora10g.ora,内容如下：

ora10g.__db_cache_size=331350016
ora10g.__java_pool_size=4194304
ora10g.__large_pool_size=8388608
ora10g.__shared_pool_size=138412032
ora10g.__streams_pool_size=0
*._kgl_large_heap_warning_threshold=8388608
*.audit_file_dest='/opt/oracle/admin/ora10g/adump'
*.background_dump_dest='/opt/oracle/admin/ora10g/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/ora10g/control01.ctl','/opt/oracle/oradata/ora10g/control02.ctl','/opt/oracle/oradata/ora10g/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/ora10g/cdump'
*.db_2k_cache_size=33554432
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=128
*.db_files=4000
*.db_name='ora10g'
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest=''
*.log_archive_dest='/opt/oracle/oradata/ora10g/archive'
*.log_checkpoints_to_alert=FALSE
*.open_cursors=300
*.parallel_execution_message_size=65535
*.parallel_max_servers=128
*.pga_aggregate_target=209715200
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.replication_dependency_tracking=FALSE
*.session_cached_cursors=100
*.sga_target=500m
*.shared_pool_size=100m
*.undo_management='AUTO'
*.undo_retention=0
*.undo_tablespace='UNDOTS'
*.user_dump_dest='/opt/oracle/admin/ora10g/udump'
*.workarea_size_policy='AUTO'
_allow_resetlogs_corruption=true

 

5.创建密码文件

orapwd file=/opt/oracle/10g/dbs/orapwora10g.ora password=admin entries=5 force=y

 

6.在/opt/oracle/admin/ora10g/initsql目录下编写建库脚本createdb.sql,内容如下：

CREATE DATABASE ora10g
LOGFILE
GROUP 1 ('/opt/oracle/oradata/ora10g/redo01.log','/opt/oracle/oradata/ora10g/redo01_1.log') size 100m reuse,
GROUP 2 ('/opt/oracle/oradata/ora10g/redo02.log','/opt/oracle/oradata/ora10g/redo02_1.log') size 100m reuse,
GROUP 3 ('/opt/oracle/oradata/ora10g/redo03.log','/opt/oracle/oradata/ora10g/redo03_1.log') size 100m reuse
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXLOGHISTORY 200
MAXDATAFILES 500
MAXINSTANCES 5
ARCHIVELOG
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/opt/oracle/oradata/ora10g/system01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/opt/oracle/oradata/ora10g/sysaux01.dbf' SIZE 1000M
UNDO TABLESPACE UNDOTS DATAFILE '/opt/oracle/oradata/ora10g/undo.dbf' SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/oracle/oradata/ora10g/temp.dbf' SIZE 500M;

 

7.启动数据库，并初始化

sqlplus /nolog
conn / as sysdba;

startup nomount pfile=/opt/oracle/admin/ora10g/pfile/initora10g.ora

 

数据库启动完毕
8.执行建库脚本

@/opt/oracle/admin/ora10g/initsql/createdb.sql

 

数据库创建完成
9.数据库创建完成后，再创建ORACLE的数据字典。

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

 

到此ORACLE手动创建过程就完成了

10.创建表空间

create tablespace portal
datafile '/opt/oracle/oradata/ora10g/portal.dbf'
size 10000M 
AutoExtend On Next 100M Maxsize unlimited
extent management local
segment space management auto;

 

11.修改system用户密码为manager

sqlplus /nolog
conn / as sysdba;

alter user system identified by manager;

 

12.修改网络连接tns内容，进入/opt/oracle/10g/network/admin目录，编辑tnsnames.ora文件
文件内容如下：

ORA10G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.155)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora10g)
    )
  )
  
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

 

修改监听文件listener.ora:
vi listener.ora
内容如下：

# listener.ora Network Configuration File: /opt/oracle/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/10g)
      (PROGRAM = extproc)
    )
  )

(SID_DESC =
  (GLOBAL_DBNAME = ORACLE)
     (ORACLE_HOME = /opt/oracle/10g)
     (SID_NAME = ora10g)
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.155)(PORT = 1521))
    )
  )

 

13.创建用户
//创建用户并指定表空间  

SQL> create user portal identified by portal
default tablespace portal
temporary tablespace temp;

create user WAP20 identified by WAP20
default tablespace portal
temporary tablespace temp;

grant DBA,CONNECT,RESOURCE TO WAP20;

//给用户授予权限
SQL> GRANT CREATE USER,DROP USER,ALTER USER,CREATE ANY VIEW,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO portal;

 14.启动监听
lsnrctl start
至此，数据库创建完成，用户创建完成，若要导入数据，可通过imp user/password@ora10g full=y file=xxx.dmp log=import.log ignore=y,导入所需的dmp文件

本例中用到的dmp文件导入如下：
imp portal/portal@ora10g full=y file=/opt/aspire/product/expportal.dmp log=expport.log ignore=y

查看数据库字符集：

select userenv('language') from dual;

 

注意dmp文件的字符集需与数据库的字符集保持一致，数据库字符集在创建数据库时指定，具体见建库脚本部分；