创建Oracle数据库并导入,unix下手动创建oracle以及imp数据导入

一、手动创建数据库步骤以及数据导入：

1、指定新的SID，我们这里创建一个以ecdsdb为名的SID。

[oracle@localhost ~]$ export ORACLE_SID=ecdsdb

2：创建初始化参数文件

[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/initecdsdb.ora

— 如下内容

ecds_80.__db_cache_size=1191182336

ecds_80.__java_pool_size=16777216

ecds_80.__large_pool_size=16777216

ecds_80.__shared_pool_size=369098752

ecds_80.__streams_pool_size=0

*.audit_file_dest='/home/db/oracle/OraHome_1/admin/ecdsdb/adump'

*.background_dump_dest='/home/db/oracle/OraHome_1/admin/ecdsdb/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/home/db/oracle/OraHome_1/oradata/ecdsdb/control01.ctl','/home/db/oracle/OraHome_1/oradata/ecdsdb/control02.ctl','/

home/db/oracle/OraHome_1/oradata/ecdsdb/control03.ctl'

*.core_dump_dest='/home/db/oracle/OraHome_1/admin/ecdsdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='ecdsdb'

*.db_recovery_file_dest='/home/db/oracle/OraHome_1/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ecdsdbXDB)'

*.job_queue_processes=10

*.nls_language='AMERICAN'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=845152256

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=50

*.session_max_open_files=50

*.sessions=500

*.sga_target=1610612736

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/db/oracle/OraHome_1/admin/ecdsdb/udump'

备注:这里根据需求填写多个控制文件路径.

3、以超级用户登录，连接实例

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 14 17:29:46 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4：创建服务器参数文件

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initecdsdb.ora';

File created.

5：创建相关目录

[oracle@localhost ~]$ mkdir -p /home/db/oracle/OraHome_1/admin/ecdsdb/{b,c,u}dump

[oracle@localhost ~]$ mkdir /home/db/oracle/OraHome_1/oradata/ecdsdb

6：启动实例

我们把实例启动到nomount状态

SQL> startup nomount

ORACLE instance started.

Total System Global Area  293601280 bytes

Fixed Size                  1219016 bytes

Variable Size              92276280 bytes

Database Buffers          192937984 bytes

Redo Buffers                7168000 bytes

7：创建数据库(写成.sql脚本)

下面是创建数据库语句：

CREATE DATABASE ecdsdb

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/home/db/oracle/OraHome_1/oradata/ecdsdb/redo01.log') SIZE 20M,

GROUP 2 ('/home/db/oracle/OraHome_1/oradata/ecdsdb/redo02.log') SIZE 20M,

GROUP 3 ('/home/db/oracle/OraHome_1/oradata/ecdsdb/redo03.log') SIZE 20M

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

CHARACTER SET ZHS16GBK

DATAFILE '/home/db/oracle/OraHome_1/oradata/ecdsdb/system01.dbf' SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/home/db/oracle/OraHome_1/oradata/ecdsdb/sysaux01.dbf' SIZE 325M REUSE

DEFAULT TEMPORARY TABLESPACE temp1

TEMPFILE '/home/db/oracle/OraHome_1/oradata/ecdsdb/temp01.dbf'

SIZE 20M REUSE

UNDO TABLESPACE undotbs1

DATAFILE '/home/db/oracle/OraHome_1/oradata/ecdsdb/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

注意：CHARACTER SET ZHS16GBK此处设置的字符集要先查看本地数据库是什么字符集就设置成什么字符集

SQL> SELECT USERENV('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS16GBK

8、检查创建是否成功

SQL> select instance_name, status from v$instance;

INSTANCE_NAME                    STATUS

——————————– ————————

ecdsdb                             OPEN

SQL> select name from v$datafile;

NAME

——————————————————————————–

/home/db/oracle/OraHome_1/oradata/ecdsdb/system01.dbf

/home/db/oracle/OraHome_1/oradata/ecdsdb/undotbs01.dbf

/home/db/oracle/OraHome_1/oradata/ecdsdb/sysaux01.dbf

9：创建默认用户表空间以

SQL> CREATE TABLESPACE users

2     DATAFILE '/home/db/oracle/OraHome_1/oradata/ecdsdb/user01.dbf' SIZE 100M

3     AUTOEXTEND ON;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

10：用system用户登录执行脚本创建数据字典相关视图

SQL>conn system/oracle as sysdba

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

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

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

至此数据库创建成功！

二、导入数据

1、创建用户ecds(以sys用户登录),并赋权限

SQL>create user ecds identified by ecds;

SQL>grant dba to ecds;

2、在136机器上查旬用户ecds用到的表空间

SQL> select distinct tablespace_name from dba_segments where owner ='ECDS';

TABLESPACE_NAME

------------------------------

SYSTEM

DATDBS01

TBS_DATA1

USERS

3、建立表空间

create tablespace DATDBS01 datafile '/home/db/oracle/OraHome_1/oradata/ecdsdb/databs01' size 100m AUTOEXTEND ON;

create tablespace TBS_DATA1 datafile '/home/db/oracle/OraHome_1/oradata/ecdsdb/tbs_data1' size 100m AUTOEXTEND ON;

4、导入数据

imp ecds/ecds@ecdsdb FULL=Y file=ecds20121114.dmp

注意：第一次我建立表空间DATDBS01的时候没带参数AUTOEXTEND ON，表空间不足后来报错和一下相似：

IMP-00003: ORACLE error 1659 encountered

ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace PSTATLOG_DATA_TB16

看到错误提示应该是没有办法自动扩展，将数据文件变为自动扩展之后问题解决

alter database datafile '/home/db/oracle/OraHome_1/oradata/ecdsdb/tbs_data1' autoextend on maxsize unlimited