linux系统mysql创建表,Linux系统下手动新建数据库

1.设置环境变量和SID

[oracle@server01 dbs]$ORACLE_BASE=/opt/oracle

[oracle@server01 dbs]$ export ORACLE_BASE

[oracle@server01 dbs]$ORACLE_HOME=/opt/oracle//product/11.2.0/db_1

[oracle@server01 dbs]$ export ORACLE_HOME

[oracle@server01 dbs]$ ORACLE_SID=msp

[oracle@server01 dbs]$ export ORACLE_SID

[oracle@server01 dbs]$ echo $ORACLE_SID

msp

[oracle@server01 dbs]$ PATH=$ORACLE_HOME/bin:$PATH

[oracle@server01 dbs]$ export PATH

[oracle@server01 dbs]$ echo $PATH

/opt/oracle/product/11.2.0/db_1/bin:/opt/oracle/product/11.2.0/db_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin

[oracle@server01 ~]$ vi .bash_profile         #把变量和SID添加进配置文件中，以免重启后失效无法startup数据库

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

umask 022

ORACLE_BASE=/opt/oracle

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

ORACLE_SID=msp

PATH=$ORACLE_HOME/bin:$PATH

stty erase ^h               #这样就能在SQL下按回车键了

alias sqlplus='rlwrap sqlplus'                          #到网上下载安装rlwrap这个小软件可以帮助在SQL下进行上下键翻动之前运行的指令

alias rman='rlwrap rman'

DISPLAY=192.168.220.1:0.0

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH DISPALY

最好reboot重启下

[oracle@server01 ~]$ env | grep ORACLE         #检查验证环境变量生效

ORACLE_SID=msp

ORACLE_BASE=/opt/oracle

ORACLE_HOME=/opt/oracle/product/11.2.0/db_1

2.建立SPFILE(根据实际需求修改相关路径)

[oracle@server01 dbs]$ vi initmsp.ora            #根据现有或者其他机器的SPFILE通过strings提取修改而成

msp.__db_cache_size=218103808

msp.__java_pool_size=4194304

msp.__large_pool_size=4194304

msp.__oracle_base='/opt/oracle'    #ORACLE_BASEsetfromenvironment

msp.__pga_aggregate_target=335544320

msp.__sga_target=503316480

msp.__shared_io_pool_size=0

msp.__shared_pool_size=268435456

msp.__streams_pool_size=0

audit_file_dest='/opt/oracle/admin/msp/adump'

audit_trail='db'

compatible='11.2.0.0.0'

control_files='/database/msp/control01.ctl','/opt/oracle/fast_recovery_area/msp/control02.ctl'

db_block_size=8192

db_domain=''

db_name='msp'

db_recovery_file_dest='/opt/oracle/fast_recovery_area'

db_recovery_file_dest_size=4322230272

diagnostic_dest='/opt/oracle'

dispatchers='(PROTOCOL=TCP)(SERVICE=mspXDB)'

log_archive_format='%t_%s_%r.dbf'

memory_target=836763648

open_cursors=300

processes=500

remote_login_passwordfile='EXCLUSIVE'

sessions=555

undo_tablespace='UNDOTBS1'

3.创建SYS密码口令

[oracle@server01 dbs]$ orapwd  file=$ORACLE_HOME/dbs/orapwmsp password=123456 entries=11   #创建密码口令文件

[oracle@server01 dbs]$ ll

total 24

-rwxrwxrwx 1 oracle oinstall  827 Mar 23 16:01 crdb01.sql

-rw-r--r-- 1 oracle oinstall  870 Mar 23 16:50 initmsp.ora

-rw-r----- 1 oracle oinstall   24 Mar 21 12:18 lkORA1

-rwxrwxrwx 1 oracle oinstall  168 Mar 23 16:41 msp.env

drwxr-xr-x 2 oracle oinstall 4096 Mar 21 12:13 old_bk

-rw-r----- 1 oracle oinstall 2560 Mar 23 16:59 orapwmsp

4.建立PFILE中的相关目录

[oracle@server01 db_1]$ cd  /opt/oracle/admin/

[oracle@server01 admin]$ ll

total 4

drwxr-x--- 6 oracle oinstall 4096 Jul 16  2013 ora1

[oracle@server01 admin]$ cp -R ora1/ msp/

[oracle@server01 admin]$ ll

total 8

drwxr-x--- 6 oracle oinstall 4096 Mar 23 17:24 msp

drwxr-x--- 6 oracle oinstall 4096 Jul 16  2013 ora1

[oracle@server01 admin]$ cd msp

[oracle@server01 msp]$ ll

total 24

drwxr-x--- 2 oracle oinstall 12288 Mar 23 17:24 adump

drwxr-x--- 2 oracle oinstall  4096 Mar 23 17:24 dpdump

drwxr-x--- 2 oracle oinstall  4096 Mar 23 17:24 pfile

drwxr-x--- 2 oracle oinstall  4096 Mar 23 17:24 scripts

[oracle@server01 msp]$ cd adump/

[oracle@server01 adump]$ rm -rf *

同样，清空其他三个文件夹

[oracle@server01 msp]$ cd /database/

[oracle@server01 database]$ ll

total 28

drwx------ 2 root   root     16384 Mar  2 01:50 lost+found

drwxr-x--- 2 oracle oinstall  4096 Mar  2 02:07 ora1

[oracle@server01 database]$ mkdir msp

[oracle@server01 database]$ ll

total 32

drwx------ 2 root   root     16384 Mar  2 01:50 lost+found

drwxr-xr-x 2 oracle oinstall  4096 Mar 23 17:29 msp

drwxr-x--- 2 oracle oinstall  4096 Mar  2 02:07 ora1

[oracle@server01 database]$ cd /opt/oracle/fast_recovery_area/

[oracle@server01 fast_recovery_area]$ mkdir msp

[oracle@server01 fast_recovery_area]$ ll

total 16

drwxr-xr-x 2 oracle oinstall 4096 Mar 23 17:31 msp

drwxr-x--- 2 oracle oinstall 4096 Mar 13  2013 ora1

5.连接数据库并启动到nomount状态

[oracle@server01 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 23 12:18:09 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

SQL>

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2232960 bytes

Variable Size             612371840 bytes

Database Buffers          218103808 bytes

Redo Buffers                2396160 bytes

SQL> select status from v$instance

2  ;

STATUS

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

STARTED

6.通过脚本建立数据库

[oracle@server01 dbs]$vi crdb01.sql

spool dbcreate.log;

create database "msp"

maxdatafiles 500

maxinstances 8

maxlogfiles  32

character set  "UTF8"

national character set al16utf16

SYSAUX DATAFILE '/database/msp/sysaux01.dbf' size 300M

archivelog

datafile

'/database/msp/system01.dbf' size 300M

extent management local

default temporary tablespace temp tempfile '/database/msp/temp01.dbf' size 100M

extent management local

undo tablespace "undotbs1"

datafile '/database/msp/undotbs01.dbf' size 200M

logfile

group 1 (

'/database/msp/redo01a.rdo',

'/database/msp/redo01b.rdo'

)size 100M,

group 2 (

'/database/msp/redo02a.rdo',

'/database/msp/redo02b.rdo'

)size 100M,

group 3 (

'/database/msp/redo03a.rdo',

'/database/msp/redo03b.rdo'

)size 100M

;

spool off

SQL> @$ORACLE_HOME/dbs/crdb01.sql;

Database created.

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

msp

SQL> select status from V$instance             #数据库状态已经从nomount变为open状态

2  ;

STATUS

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

OPEN

[oracle@server01 db_1]$ cd /database/

[oracle@server01 database]$ cd msp/

[oracle@server01 msp]$ ll

total 1450728

-rw-r----- 1 oracle oinstall  16433152 Mar 23 17:53 control01.ctl

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo01a.rdo

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo01b.rdo

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo02a.rdo

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo02b.rdo

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo03a.rdo

-rw-r----- 1 oracle oinstall 104858112 Mar 23 17:52 redo03b.rdo

-rw-r----- 1 oracle oinstall 314580992 Mar 23 17:52 sysaux01.dbf

-rw-r----- 1 oracle oinstall 314580992 Mar 23 17:52 system01.dbf

-rw-r----- 1 oracle oinstall 104865792 Mar 23 17:52 temp01.dbf

=============至此，数据库基本建立完成===========================

7.建立用户数据表空间

SQL> create tablespace mytbs datafile'/database/msp/mspmytbs.dbf' size 100M extent management local;

Tablespace created.

8.建立必要的数据字典视图

[oracle@server01 db_1]$ cd rdbms/

[oracle@server01 rdbms]$ ll

total 88

drwxr-xr-x 2 oracle oinstall 49152 Mar 13  2013 admin

drwxr-xr-x 2 oracle oinstall  4096 Mar 23 17:13 audit

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 demo

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 doc

drwxr-xr-x 5 oracle oinstall  4096 Mar 13  2013 install

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 jlib

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 lib

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 log

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 mesg

drwxr-xr-x 2 oracle oinstall  4096 Mar 13  2013 public

drwxr-xr-x 5 oracle oinstall  4096 Mar 13  2013 xml

[oracle@server01 rdbms]$ cd admin/

[oracle@server01 admin]$ ls | more

a0902000.sql

a1001000.sql

a1002000.sql

a1101000.sql

a1102000.sql

addmrpti.sql

addmrpt.sql

addmtmig.sql

agtept.lst

ashrptinoop.sql

ashrpti.sql

ashrptistd.sql

ashrpt.sql

........................

SQL> spool log1.log;

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

SQL> spool log2.log;

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

查看log2.log会有如下报错为正常报错，可以不管

drop public synonym XMLSequence

*

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

drop operator XMLSequence

*

ERROR at line 1:

ORA-29807: specified operator does not exist

SQL> spool log3.log;

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

阅读(1071) | 评论(0) | 转发(0) |

0

上一篇：没有了