ORACLE 常用命令

最新推荐文章于 2021-04-06 17:49:43 发布

绿源2008

最新推荐文章于 2021-04-06 17:49:43 发布

阅读量2.1k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/chenkeqin_2012/article/details/25817931

版权

oracle 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

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 <logon> ::= <username>[/<password>][@<connect_identifier>] [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 <logon> ::= <username>[/<password>][@<connect_identifier>] [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 <logon> ::= <username>[/<password>][@<connect_identifier>] [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