Oracle一些常用的命令行工具

在使用和管理Oracle时，经常要用到一些命令行的工具。比如：sqlplus、dbca、netca、netmgr、emctl、emca、lsrnctl、isqlplusctl、tnsping等等。这些简单的工具，我们要能够熟练的使用它们。利用他们可以启动和关闭Oracle，创建以及配置数据库，配置网络（监听器、本地服务名），启动和关闭em，启动和关闭isqlplus等等。

1. 利用 sqlplus 来启动和关闭数据库
[oracle@redhat4 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 22 14:12:05 2012

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

SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup;
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size                  1218604 bytes
Variable Size              88082388 bytes
Database Buffers          125829120 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2. 利用 lsrnctl 来管理监听器
[oracle@redhat4 ~]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-SEP-2012 14:19:49

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

查看命令行 lsnrctl 的使用方法：
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start               stop                status
services            version             reload
save_config         trace               spawn
change_password     quit                exit
set*                show*

利用命令行 lsnrctl start 启动监听器：
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-SEP-2012 14:20:13
Uptime                    0 days 0 hr. 0 min. 0 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/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PL***tProc" has 1 instance(s).
  Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

利用命令行 lsnrctl status 查看监听器的状态：
[oracle@redhat4 ~]$ lsnrctl status;

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-SEP-2012 14:23:43

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=redhat4)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-SEP-2012 14:20:13
Uptime                    0 days 0 hr. 3 min. 30 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/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=redhat4)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PL***tProc" has 1 instance(s).
  Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jiagulun" has 1 instance(s).
  Instance "jiagulun", status READY, has 1 handler(s) for this service...
Service "jiagulunXDB" has 1 instance(s).
  Instance "jiagulun", status READY, has 1 handler(s) for this service...
Service "jiagulun_XPT" has 1 instance(s).
  Instance "jiagulun", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@redhat4 ~]$

3. 利用 emctl 来管理em:
查看emctl 使用方法：
[oracle@redhat4 ~]$ emctl
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
Invalid arguments

Unknown command option
Usage::
   Oracle Enterprise Manager 10g Database Control commands:
       emctl start| stop| status| setpasswd dbconsole
       emctl secure

       emctl set ssl test|off|on em
       emctl set ldap
emctl blackout options can be listed by typing "emctl blackout"
emctl config options can be listed by typing "emctl config"
emctl secure options can be listed by typing "emctl secure"
emctl ilint  options can be listed by typing "emctl ilint"
emctl deploy  options can be listed by typing "emctl deploy"

利用 emctl start dbconsole 启动em:
[oracle@redhat4 ~]$ emctl start dbconsole;
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

Starting Oracle Enterprise Manager 10g Database Control ................. started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/redhat4_jiagulun/sysman/log

利用 emctl status dbconsole 查看em的状态:
[oracle@redhat4 ~]$ emctl status dbconsole;
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/redhat4_jiagulun/sysman/log

利用 emctl status dbconsole 关闭em:
[oracle@redhat4 ~]$ emctl stop dbconsole
TZ set to PRC
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@redhat4 ~]$

4. 利用 emca 来配置em:
[oracle@redhat4 config]$ emca
/u01/app/oracle/product/10.2.0/db_1/bin/emca [operation] [mode] [dbType] [flags] [parameters]

-config dbcontrol db [-repos (create | recreate)] [-cluster] [-silent] [-backup] [parameters]: configure Database Control for a database
-config centralAgent (db | asm) [-cluster] [-silent] [parameters]: configure central agent management
-config all db [-repos (create | recreate)] [-cluster] [-silent] [-backup] [parameters]: configure both Database Control and central agent management

-deconfig dbcontrol db [-repos drop] [-cluster] [-silent] [parameters]: de-configure Database Control
-deconfig centralAgent (db | asm) [-cluster] [ -silent] [parameters]: de-configure central agent management
-deconfig all db [-repos drop] [-cluster] [-silent] [parameters]: de-configure both Database Control and central agent management

-addInst (db | asm) [-silent] [parameters]: configure EM for a new RAC instance
-deleteInst (db | asm) [-silent] [parameters]: de-configure EM for a specified RAC instance

-reconfig ports [-cluster] [parameters]: explicitly reassign Database Control ports
-reconfig dbcontrol -cluster [-silent] [parameters]: reconfigures RAC Database Control deployment

-displayConfig dbcontrol -cluster [-silent] [parameters]: displays information about the RAC Database Control configuration

-upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]: upgrades an earlier version of the EM configuration to the current version

-restore (db | asm | db_asm) [-cluster] [-silent] [parameters]: restores the current version of the EM configuration to an earlier version

对上面参数和选择的解释：
Parameters and Options:
[parameters]: [ -respFile fileName ] [ -paramName paramValue ]*
db: perform configuration operation for a database (including databases that use ASM)
asm: perform configuration operation for an ASM-only instance
db_asm: perform upgrade/restore operation for a database and an ASM instance
-repos create: create a new Database Control repository
-repos drop: drop the current Database Control repository
-repos recreate: drop the current Database Control repository and recreate a new one
-cluster: perform configuration operation for a RAC database
-silent: perform configuration operation without prompting for parameters
-backup: configure automatic backup for a database

Parameters for single instance databases
        HOST: Database hostname
        SID: Database SID
        PORT: Listener port number
        ORACLE_HOME: Database ORACLE_HOME
        HOST_USER: Host username for automatic backup
        HOST_USER_PWD: Host user password for automatic backup
        BACKUP_SCHEDULE: Automatic backup schedule (HH:MM)
        EMAIL_ADDRESS: Email address for notifications
        MAIL_SERVER_NAME: Outgoing Mail (SMTP) server for notifications
        ASM_OH: ASM ORACLE_HOME
        ASM_SID: ASM SID
        ASM_PORT: ASM port
        ASM_USER_ROLE: ASM user role
        ASM_USER_NAME: ASM username
        ASM_USER_PWD: ASM user password
        SRC_OH: ORACLE_HOME for the database to be upgraded
        DBSNMP_PWD: Password for DBSNMP user
        SYSMAN_PWD: Password for SYSMAN user
        SYS_PWD: Password for SYS user
        DBCONTROL_HTTP_PORT: Database Control HTTP port
        AGENT_PORT: EM agent port
        RMI_PORT: RMI port for Database Control
        JMS_PORT: JMS port for Database Control

Additional Parameters for cluster databases
        CLUSTER_NAME: Cluster name
        DB_UNIQUE_NAME: Database unique name
        SERVICE_NAME: Service name
        EM_NODE: Database Control node name
        EM_SID_LIST: Agent SID list [comma separated]
[oracle@redhat4 config]$


5. 利用 isqlplusctl 来管理isqlplus:
查看isqlpluctl命令行的使用方法：
[oracle@redhat4 ~]$ isqlplusctl
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Invalid arguments
Unknown command option
Usage::
       isqlplusctl start| stop

利用isqlpluctl start 命令启动isqlplus：
[oracle@redhat4 ~]$ isqlplusctl start;
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
getnameinfo failed
getnameinfo failed
getnameinfo failed
getnameinfo failed
Starting iSQL*Plus ...
iSQL*Plus started.

利用isqlpluctl stop 命令关闭isqlplus：
[oracle@redhat4 ~]$ isqlplusctl stop;
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
getnameinfo failed
getnameinfo failed
getnameinfo failed
getnameinfo failed
Stopping iSQL*Plus ...
iSQL*Plus stopped.

6. dbca、netca、netmgr等命令行启动图形界面
dbca可以完成创建配置数据库、netca和netmgr可以配置网络，即配置监听器、本地服务名等。