手工创建oracle数据库
1.停止数据库
sqlplus /nologconn / 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/ora10gmkdir -p /opt/oracle/admin/ora10g/adumpmkdir -p /opt/oracle/admin/ora10g/bdumpmkdir -p /opt/oracle/admin/ora10g/cdumpmkdir -p /opt/oracle/admin/ora10g/udumpmkdir -p /opt/oracle/admin/ora10g/pfilemkdir -p /opt/oracle/admin/ora10g/initsql
?
数据文件目录：
mkdir -p /opt/oracle/oradata/ora10gmkdir -p /opt/oracle/oradata/ora10g/archive
?
4.在/opt/oracle/admin/ora10g/pfile目录下创建参数文件：
initora10g.ora,内容如下：
ora10g.__db_cache_size=331350016ora10g.__java_pool_size=4194304ora10g.__large_pool_size=8388608ora10g.__shared_pool_size=138412032ora10g.__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 ora10gLOGFILEGROUP 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 reuseMAXLOGFILES 50MAXLOGMEMBERS 5MAXLOGHISTORY 200MAXDATAFILES 500MAXINSTANCES 5ARCHIVELOGCHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16DATAFILE '/opt/oracle/oradata/ora10g/system01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCALSYSAUX DATAFILE '/opt/oracle/oradata/ora10g/sysaux01.dbf' SIZE 1000MUNDO TABLESPACE UNDOTS DATAFILE '/opt/oracle/oradata/ora10g/undo.dbf' SIZE 500MDEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/oracle/oradata/ora10g/temp.dbf' SIZE 500M;?
7.启动数据库，并初始化
sqlplus /nologconn / 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.sqlSQL>@?/rdbms/admin/catproc.sqlSQL>@?/rdbms/admin/catexp.sql
?
到此ORACLE手动创建过程就完成了
10.创建表空间
create tablespace portaldatafile '/opt/oracle/oradata/ora10g/portal.dbf'size 10000M AutoExtend On Next 100M Maxsize unlimitedextent management localsegment space management auto;
?
11.修改system用户密码为manager
sqlplus /nologconn / 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 portaldefault tablespace portaltemporary tablespace temp;create user WAP20 identified by WAP20default tablespace portaltemporary 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文件的字符集需与数据库的字符集保持一致，数据库字符集在创建数据库时指定，具体见建库脚本部分；