一、关闭数据库
二、修改SID
[oracle@redhat4~]$ vi .bash_profile
exportORACLE_SID=powter
使其生效并验证：
[oracle@redhat4~]$ source .bash_profile
[oracle@redhat4~]$ env |grep -i sid
ORACLE_SID=powter
三、创建目录
[oracle@redhat4admin]$ cd $ORACLE_BASE/admin/
[oracle@redhat4admin]$ mkdir powter
[oracle@redhat4admin]$ cd powter/
[oracle@redhat4powter]$ pwd
/u01/app/oracle/admin/powter
[oracle@redhat4powter]$ mkdir {a,b,c,u}dump
[oracle@redhat4oradata]$ cd $ORACLE_BASE/oradata
[oracle@redhat4oradata]$ pwd
/u01/app/oracle/oradata
[oracle@redhat4oradata]$ mkdir powter
到刚创建的powter目录下创建3个目录，模拟3个磁盘
[oracle@redhat4 oradata]$ cd powter/
[oracle@redhat4 powter]$ mkdirDisk{1,2,3}
四、添加图形化界面信息
修改/etc/oratab文件，添加要创建数据库的信息，这样在dbca中就可以看到此DB，并可以对此进行图形界面管理。
[oracle@redhat4 powter]$ vi /etc/oratab
追加：
| powter:/u01/app/oracle/product/10.2.0/db_1:N |
为了在终端可以打开图形界面，需要安装“Xmanager”，并且进行以下修改
[oracle@redhat4~]$ w
16:33:42 up 2 days, 12:38, 6 users, load average: 0.04, 0.05, 0.01
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/2 192.168.0.111 16:33 0.00s 0.02s 0.00s w
root :0 - 12:44 ?xdm? 29:45 0.24s /usr/bin/gnome-
root pts/3 192.168.0.111 12:44 2:54m 1.34s 1.09s rman
root pts/5 192.168.0.111 12:50 2:56m 0.08s 0.06s sqlplus as sy
root pts/6 192.168.0.111 15:54 2:18 5.19s 0.00s /bin/sh -f /u01
root pts/1 :0.0 16:07 25:41 0.02s 0.00s -bash
[oracle@redhat4~]$ export DISPLAY=192.168.0.111:0.0
192.168.0.111属于（已安装Xmanager）客户端的IP（我这里是windows7中虚拟机虚拟网卡的IP）
测试：
[oracle@redhat4 powter]$ dbca
可以看到，此时已经可以在dbca中看到powter数据库了。
五、添加初始化文件
[oracle@redhat4~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@redhat4dbs]$ cat init.ora|grep -v ^#|grep -v ^$>initpowter.ora
[oracle@redhat4dbs]$ ls
hc_wolex.dat init.ora lkWOLEX snapcf_wolex.f
initdw.ora initpowter.ora orapwwolex spfilewolex.ora
[oracle@redhat4dbs]$ vi initpowter.ora
| db_name=powter db_files = 80 db_file_multiblock_read_count = 8 shared_pool_size = 3500000 log_checkpoint_interval = 10000 processes = 50 parallel_max_servers = 5 max_dump_file_size = 10240 global_names = TRUE control_files = ( /u01/app/oracle/oradata/powter/Disk1/ctl01.ctl, /u01/app/oracle/oradata/powter/Disk2/ctl02.ctl, /u01/app/oracle/oradata/powter/Disk3/ctl03.ctl)
sga_max_size=300M sga_target=300M undo_tablespace=undotbs undo_management=auto |
六、创建数据库脚本
（参见《Administrator'sGuide》第二章第7节“Step 7: Issue the CREATE DATABASEStatement”）
[oracle@redhat4~]$ cd $ORACLE_BASE
[oracle@redhat4oracle]$ gedit createdb_powter,sql
| CREATE DATABASE powter USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/powter/Disk1/redo01_01.log', '/u01/app/oracle/oradata/powter/Disk2/redo01_02.log') SIZE 100M, GROUP 2 ('/u01/app/oracle/oradata/powter/Disk1/redo02_01.log', '/u01/app/oracle/oradata/powter/Disk2/redo02_02.log') SIZE 100M, GROUP 3 ('/u01/app/oracle/oradata/powter/Disk1/redo03_01.log', '/u01/app/oracle/oradata/powter/Disk2/redo03_02.log') SIZE 100M MAXLOGFILES 200 MAXLOGMEMBERS 5 MAXLOGHISTORY 200 MAXDATAFILES 100 MAXINSTANCES 2 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE '/u01/app/oracle/oradata/powter/Disk1/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u01/app/oracle/oradata/powter/Disk1/sysaux01.dbf' SIZE 325M REUSE DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/powter/Disk1/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/powter/Disk1/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; |
执行脚本：
[oracle@redhat4~]$ cd $ORACLE_BASE
[oracle@redhat4oracle]$ sqlplus / as sysdba
此处顺便创建spfile：
SQL>create spfile from pfile;
File created.
以nomount状态启动数据库：
SQL>startup nomount
ORACLEinstance started.
Total SystemGlobal Area 314572800 bytes
FixedSize 2020480 bytes
VariableSize 67111808 bytes
DatabaseBuffers 243269632 bytes
RedoBuffers 2170880 bytes
SQL> !ls
admin createdb_powter,sql~ log_archive_area oraInventory
createdb_powter,sql flash_recovery_area oradata product
SQL>@createdb_powter.sql
Databasecreated.
查看当前连接数据库的实例名
SQL>select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
powter
查看当前数据库开启状态
SQL>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
运行两个脚本，创建必要的视图和过程、包等等。
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
[oracle@redhat4admin]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 21:25:30 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL>@catalog.sql
……
PL/SQLprocedure successfully completed.
SQL>@catproc.sql
……
PL/SQLprocedure successfully completed.
七、创建SCOTT/TIGER练习用户
跑utlsaml.sql脚本，以创建SCOTT用户
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin
[oracle@redhat4admin]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 22:10:29 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> @utlsampl.sql
Disconnectedfrom Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bitProduction
With thePartitioning, OLAP and Data Mining options
修改SCOTT用户的密码以及将其解锁
SQL> show user
USER is"SYS
SQL> alter user scott identified bytiger account unlock;
User altered.
以system用户连接跑pupbld.sql脚本
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin
[oracle@redhat4admin]$ sqlplus system/oracle
SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 22:16:01 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> @pupbld.sql
……
● 另：也可以把另外一个数据库中的SCOTT用户导入到其他数据库中
数据库open状态下，
[oracle@redhat4~]$ exp scott/tiger owner=scott file=scott.dmp
关闭当前数据库，更改SID
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
SQL> !
[oracle@redhat4~]$ vi .bash_profile
exportORACLE_SID=wolex
[oracle@redhat4~]$ source .bash_profile
[oracle@redhat4~]$ !sql
sqlplus / assysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Thu Jul 19 23:42:19 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to anidle instance.
SQL> startup
ORACLEinstance started.
Total SystemGlobal Area 314572800 bytes
FixedSize 2020480 bytes
VariableSize 121637760 bytes
DatabaseBuffers 188743680 bytes
RedoBuffers 2170880 bytes
Databasemounted.
Databaseopened.
查看SID为wolex的数据库中有多少个用户
SQL> select username from all_users;
USERNAME
------------------------------------------------------------
DBSNMP
TSMSYS
DIP
OUTLN
SYSTEM
SYS
6 rowsselected.
导入到当前（wolex）数据库中：
SQL> !
[oracle@redhat4~]$ imp scott/tiger file=scott.dmp
SQL> select username from all_users;
USERNAME
------------------------------------------------------------
SCOTT
DBSNMP
TSMSYS
DIP
OUTLN
SYSTEM
SYS
6 rowsselected.
尝试连接SCOTT/TIGER：
SQL> conn scott/tiger
Connected.
SQL> show user
USER is"SCOTT"
如果是远程导入导出的话，则不需要关闭任何一个数据库，导入导出时加入SID（数据库名？）：
exp scott/tiger@powter owner=scottfile=scott.dmp
imp scott/tiger@wolex file=scott.dmp
●
exppowter_test/powter_test@192.168.0.200:1521/powter owner=powter_testfile=powter_test.dmp
在实例名为wolex的数据库中创建powter_test并且赋予相应的权限
$ imppowter_test/powter_test@192.168.0.200:1521/wolex file=powter_test.dmp
八、模拟远程登录数据库
1、创建口令文件
[oracle@redhat4dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@redhat4dbs]$ orapwd file=orapwpowter password=oracle
2、配置监听
● 图形化界面配置
（1）Oracle NetManager
[oracle@redhat4admin]$ netmgr
（2）Oracle NetConfiguration Assistant
[oracle@redhat4admin]$ netca
2、代码行配置
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@redhat4admin]$ vi listener.ora
在原来已经存在的listener监听中添加powter数据库（静态注册），再添加一个动态注册监听lsnr2
| SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = wolex) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = wolex) ) (SID_DESC = (GLOBAL_DBNAME = powter) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = powter) ) )
LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) )
LSNR2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1522)) ) |
为listener监听添加服务器名（别名），lsnr2不添加
[oracle@redhat4admin]$ vi tnsnames.ora
| POWTER_ALIAS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = powter) ) )
WOLEX_ALIAS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = wolex) ) ) |
测试别名能否ping通：
[oracle@redhat4admin]$ tnsping powter_alias
TNS Ping Utilityfor Linux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:41:50
Copyright (c)1997, 2005, Oracle. All rights reserved.
Used parameterfiles:
Used TNSNAMESadapter to resolve the alias
Attempting tocontact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = powter)))
OK (10 msec)
启动监听：
[oracle@redhat4~]$ lsnrctl start
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:37:33
Copyright (c)1991, 2005, Oracle. All rights reserved.
TNS-01106:Listener using listener name LISTENER has already been started
[oracle@redhat4~]$ lsnrctl status
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:37:40
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1521)))
STATUS of theLISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 20-JUL-2012 01:09:40
Uptime 0 days 0 hr. 27 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
ServicesSummary...
Service"powter" has 2 instance(s).
Instance "powter", status UNKNOWN,has 1 handler(s) for this service...
Instance "powter", status READY, has 1 handler(s)for this service...
Service"powter_XPT" has 1 instance(s).
Instance "powter", status READY,has 1 handler(s) for this service...
Service"wolex" has 2 instance(s).
Instance "wolex", status UNKNOWN,has 1 handler(s) for this service...
Instance "wolex", status READY, has1 handler(s) for this service...
Service"wolexXDB" has 1 instance(s).
Instance "wolex", status READY, has1 handler(s) for this service...
Service"wolex_XPT" has 1 instance(s).
Instance "wolex", status READY, has1 handler(s) for this service...
The commandcompleted successfully
通过IP和端口号和实例名模拟远程连接
[oracle@redhat4dbs]$ sqlplus sys/oracle@192.168.0.200:1521/powteras sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Fri Jul 20 01:28:28 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
通过别名简化连接
SQL> !
[oracle@redhat4dbs]$ cd
[oracle@redhat4~]$ sqlplus sys/oracle@powter_alias as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Fri Jul 20 01:30:12 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL>
开启lsnr2监听
[oracle@redhat4~]$ lsnrctl start lsnr2
[oracle@redhat4~]$ lsnrctl status lsnr2
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:43:47
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1522)))
STATUS of theLISTENER
------------------------
Alias lsnr2
Version TNSLSNR for Linux: Version10.2.0.1.0 - Production
Start Date 20-JUL-2012 01:43:34
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener LogFile /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log
ListeningEndpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1522)))
The listener supports no services
The commandcompleted successfully
将数据库中的默认端口号设置为lsnr2的端口号，然后动态注册
SQL>show parameter local_
NAME TYPE VALUE
------------------------------ ------------------------------------
local_listener string
SQL>alter system set local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.200)(PORT = 1522))" scope=memory;
Systemaltered.
SQL>show parameter local_
NAME TYPE VALUE
------------------------------ ------------------------------------
local_listener string (ADDRESS= (PROTOCOL = TCP)(HOST
=192.168.0.200)(PORT = 1522))
SQL>alter system register;
System altered.
[oracle@redhat4admin]$ lsnrctl status lsnr2
LSNRCTL forLinux: Version 10.2.0.1.0 - Production on 20-JUL-2012 01:57:07
Copyright (c)1991, 2005, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias lsnr2
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 20-JUL-2012 01:43:34
Uptime 0 days 0 hr. 13 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log
Listening EndpointsSummary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1522)))
ServicesSummary...
Service"powter" has 1 instance(s).
Instance "powter", status READY, has 1 handler(s)for this service...
Service"powter_XPT" has 1 instance(s).
Instance "powter", status READY,has 1 handler(s) for this service...
The commandcompleted successfully
九、小技巧
1、在SQL>中使用vi编辑
（1）、
[oracle@redhat4~]$ vi .bash_profile
export EDITOR=vi
[oracle@redhat4~]$ source .bash_profile
SQL>edit
Wrote file afiedt.buf
以上方法并没有完全像vi环境一样？不知道是否因为数据库类型（事务DB、数据仓库等）不同？
（2）、修改登录参数
[oracle@localhost~]$ cd $ORACLE_HOME/sqlplus/admin/
在glogin.sql文件中追加：
[oracle@localhostadmin]$ vi glogin.sql
DEFINE _EDITOR =vi
保存退出即可，以上修改后，在sqlplus中输入ed(it)即可进入vi编辑器修改上一条SQL语句。
（3）、在SQL*Plus中临时修改：
SQL>define _editor=/bin/vi
2、查看所有68个系统变量值
SQL>show all
3、列出上一条缓存的SQL语句
● l
● list
● ；
4、改变终端提示符：
SQL>show all
┇
sqlprompt "SQL> "
┇
SQL> setsqlprompt "redhat@wolex> "
redhat@wolex>
5、对上一条缓存的SQL语句进行部分修改
● c[hange]
6、执行上一条缓存的SQL语句
● /
7、修改系统变量，并在所有会话中生效
[oracle@redhat4admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin
[oracle@redhat4admin]$ vi glogin.sql
例如追加：
| set linesize 120 set pagesize 30 col[umn]ename for[mat] a10 |
8、通过错误代码查看信息
[oracle@redhat4~]$ oerr ora 04031
04031, 00000,"unable to allocate %s bytes of shared memory(\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than wasallocated in the shared
// pool.
// *Action: Ifthe shared pool is out of memory, either use the
// dbms_shared_pool package to pin largepackages,
// reduce your use of shared memory, orincrease the amount of
// available shared memory by increasingthe value of the
// INIT.ORA parameters"shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory,increase the INIT.ORA
// parameter"large_pool_size".
9、连接sql developer
（1）配置密码文件？
（2）启动监听
（3）关闭Linux的防火墙
[root@redhat4~]# /sbin/iptables -F
十、两个SID连接两个DB
SID为powter的实例启动powter数据库：
[oracle@redhat4~]$ echo $ORACLE_SID
powter
[oracle@redhat4~]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Fri Aug 10 08:47:11 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> selectinstance_name from v$instance;
INSTANCE_NAME
--------------------------------
powter
SID为wolex的实例启动wolex_db数据库：
[oracle@redhat4~]$ export ORACLE_SID=wolex
[oracle@redhat4~]$ echo $ORACLE_SID
wolex
[oracle@redhat4~]$ sqlplus / as sysdba
SQL*Plus:Release 10.2.0.1.0 - Production on Fri Aug 10 08:49:13 2012
Copyright (c)1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With thePartitioning, OLAP and Data Mining options
SQL> selectinstance_name from v$instance;
INSTANCE_NAME
----------------
wolex
1616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
