sqlplus 连接 重启 关闭 数据库 数据库监听启动关闭 连接数设置

[oracle@ORACLE ~]$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 14 17:07:57 2014 Copyright (c) 1982, 2008, Oracle.  All rights reserved. SQL> conn /as sysdba; Connected to an idle instance. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 3373858816 bytes Fixed Size            2170512 bytes Variable Size         1907642736 bytes Database Buffers     1459617792 bytes Redo Buffers            4427776 bytes Database mounted. Database opened. SQL>

[root@ORACLE ~]# lsnrctl stop; LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 14-MAY-2014 17:10:13 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) The command completed successfully [root@ORACLE ~]# lsnrctl start; LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 14-MAY-2014 17:10:17 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Starting /dba/oracle/product/11.1/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.1.0.7.0 - Production System parameter file is /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production Start Date                14-MAY-2014 17:10:17 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Password or Local OS Authentication SNMP                      OFF Listener Parameter File   /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s).   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "test" has 1 instance(s).   Instance "test", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [root@ORACLE ~]#

当前的连接数 select count(*) from v$process; 设置的最大连接数（默认值为150） select value from v$parameter where name = 'processes'; 修改最大连接数 alter system set processes = 300 scope = spfile; 当数据库最大连接数不够时会出现客户端连接间歇性失败，报错ORA-12519。 一、    将数据库操作模式改为共享服务器模式 注：在Oracle数据库服务器本机操作。 1、 点击开始，从程序菜单选择“Database Configuration Assistant”，如下图所示。 2、 进入“欢迎使用”界面后，点击“下一步”，如下图所示。 3、 进入“步骤1（共4步）：操作”界面后，选择“在数据库中配置数据库选项”，点击“下一步”，如下图所示。 4、 进入“步骤2（共4步）：数据库”界面后，选择可用数据库（本机Oracle数据库服务名），点击“下一步”，如下图所示。 5、 进入“步骤3（共4步）：数据库特性”界面后，点击“下一步”，如下图所示。 6、 进入“步骤4（共4步）：数据库连接选项”界面后，选择“共享服务器模式”，点击“编辑共享连接参数…”，如下图所示。 7、 进入“共享服务器模式”界面后，协议选择为TCP；调度程序数设置为5；每个调度程序的最大连接数设置为100；最大调度程序数设置为5；最大服务器进程数设置为600，点击“确定”，如下图所示。 8、 返回到“步骤4（共4步）：数据库连接选项”界面后，点击“完成”，如下图所示。 9、 系统弹出“重新启动数据库”提示，点击“是”，如下图所示。 10、              进入“概要”界面后，点击“确定”，数据库配置正在进行…如下图所示。 11、              系统弹出“数据库配置已成功完成。是否要执行其他操作？”提示，点击“否”，如下图所示。 12、              数据库操作模式改为共享服务器模式已完成！ 二、    调整ORACLE内存设置 1、 点击开始，从程序菜单选择“Enterprise Manager Console”，如下图所示。 2、 进入“登录”界面后，选择“独立启动”，点击“确定”，如下图所示。 3、 进入“独立”界面后，选择需要调整内存的数据库，并双击，如下图所示。 4、 进入“数据库连接信息”界面后，输入用户名、口令、选择连接身份为“SYSDBA”，点击“确定”，如下图所示。 5、 双击打开“例程”，点选“配置”，再选择界面右侧的“内存”选项卡，将SGA中的“SGA的最大大小”改为512，PGA中的“总计PGA目标”改为128，点击“应用”，如下图所示。 6、 进入“关闭选项”界面后，选择“立即”，点击“确定”，如下图所示。 7、 系统正在关闭数据库等相关操作，处理完成后提示“处理已完成”，点击“关闭”，如下图所示。 8、 将SGA中的“共享池”改为256；“缓冲区高速缓存”改为48；大型池改为128；“Java池”改为64，再次点击“应用”，如下图所示。 9、 进入“关闭选项”界面后，选择“立即”，点击“确定”，如下图所示。 10、              系统正在关闭数据库等相关操作，处理完成后提示“处理已完成”，点击“关闭”，如下图所示。 11、              Oracle内存设置调整完毕！ 三、    修改Oracle最大连接数的方法 1、 进入“SQL*Plus Worksheet”，如下图所示。 2、 点击“改变数据库连接…”按钮，如下图所示。 3、 进入“数据库连接信息”界面，输入用户名、口令、需要修改最大连接数的数据库服务名，连接身份选择“SYSDBA”，点击“确定”，如下图所示。 4、 输入指令“show parameter processes;”并执行，查看目前最大连接数，如下图所示。 5、 输入指令“alter system set processes=600 scope=spfile;”并执行，修改最大连接数为600，如下图所示。 6、 输入指令“create pfile from spfile;”并执行，创建Profile，如下图所示。 7、 关闭“Enterprise Manager Console”，重启Oracle服务或重启Oracle数据库服务器 8、 重复操作1、2、3、4步，确认最大连接数修改成功。

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf'

Xshell for Xmanager Enterprise 3.0 (Build 0260) Copyright (c) 2002-2009 NetSarang Computer, Inc. All rights reserved. Type `help' to learn how to use Xshell prompt. Xshell:\> Connecting to 172.20.100.25:22... Connection established. Escape character is '^@]'. Last login: Wed May 14 13:52:49 2014 from 172.30.29.95 [oracle@ORACLE ~]$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 5 08:59:07 2014 Copyright (c) 1982, 2008, Oracle.  All rights reserved. SQL> conn as sysdba; Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where   ::= [/][@] [edition=value] | / SQL> [1]+  Stopped                 sqlplus /nolog [oracle@ORACLE ~]$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 5 08:59:29 2014 Copyright (c) 1982, 2008, Oracle.  All rights reserved. SQL> conn as /sysdba SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where   ::= [/][@] [edition=value] | / SQL> conn /as sysdba Connected to an idle instance. SQL> shutdown immediate; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory SQL>  startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 3373858816 bytes Fixed Size            2170512 bytes Variable Size         2092192112 bytes Database Buffers     1275068416 bytes Redo Buffers            4427776 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' SQL> SQL> exit; Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ORACLE ~]$ su -l root Password: [root@ORACLE ~]# lsnrctl stop; LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 05-JUN-2014 09:01:22 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) The command completed successfully [root@ORACLE ~]# lsnrctl start; LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 05-JUN-2014 09:01:39 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Starting /dba/oracle/product/11.1/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.1.0.7.0 - Production System parameter file is /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production Start Date                05-JUN-2014 09:01:39 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Password or Local OS Authentication SNMP                      OFF Listener Parameter File   /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s).   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "test" has 1 instance(s).   Instance "test", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [root@ORACLE ~]# su -l oracle [oracle@ORACLE ~]$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 5 09:02:27 2014 Copyright (c) 1982, 2008, Oracle.  All rights reserved. SQL> conn /as sysdab; SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER|SYSASM}] where   ::= [/][@] [edition=value] | / SQL> conn/as sysdba; Connected. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 3373858816 bytes Fixed Size            2170512 bytes Variable Size         2092192112 bytes Database Buffers     1275068416 bytes Redo Buffers            4427776 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' SQL> exit; Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ORACLE ~]$ su -l root Password: [root@ORACLE ~]# lsnrctl stop LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 05-JUN-2014 09:04:44 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) The command completed successfully [root@ORACLE ~]# lsnrctl start; LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 05-JUN-2014 09:04:54 Copyright (c) 1991, 2008, Oracle.  All rights reserved. Starting /dba/oracle/product/11.1/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.1.0.7.0 - Production System parameter file is /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.100.25)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.1.0.7.0 - Production Start Date                05-JUN-2014 09:04:54 Uptime                    0 days 0 hr. 0 min. 0 sec Trace Level               off Security                  ON: Password or Local OS Authentication SNMP                      OFF Listener Parameter File   /dba/oracle/product/11.1/db_1/network/admin/listener.ora Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.100.25)(PORT=1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s).   Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "test" has 1 instance(s).   Instance "test", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [root@ORACLE ~]# su -l oracle [oracle@ORACLE ~]$ sqlplus /nolog SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jun 5 09:05:40 2014 Copyright (c) 1982, 2008, Oracle.  All rights reserved. SQL> conn /as sysdba; Connected. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 3373858816 bytes Fixed Size            2170512 bytes Variable Size         2092192112 bytes Database Buffers     1275068416 bytes Redo Buffers            4427776 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' SQL>  lsnrctl stop; SP2-0734: unknown command beginning "lsnrctl st..." - rest of line ignored. SQL> select count(*) from v$process; select count(*) from v$process * ERROR at line 1: ORA-03135: connection lost contact Process ID: 24447 Session ID: 5505 Serial number: 33 SQL> shutdown abort; ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist SQL> startup ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist SQL> alter database datafile 5 offline; ERROR: ORA-03114: not connected to ORACLE SQL> conn /as sysdba;          Connected. SQL> alter database datafile 5 offline; alter database datafile 5 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL>  recover database; ORA-00283: recovery session canceled due to errors ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' SQL> alter database datafile 5 offline; alter database datafile 5 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/dba/oracle/oradata/orcl/cms01.dbf' SQL> alter database datafile 5 offline drop; Database altered. SQL> alter database open; Database altered. SQL>  

 服务端          select userenv('language') from dual; SIMPLIFIED CHINESE_CHINA.AL32UTF8          dmp文件          select nls_charset_name(to_number('0354'，'xxxx')) from dual; ZHS16GBK          在windows平台下，注册表里面的HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG。          还可以在DOS窗口里面临时设置，比如：set nls_lang=AMERICAN_AMERICA.UTF8          客户端 SIMPLIFIED CHINESE_CHINA.ZHS16GBK