创建与控制数据库 －－ 实操

作者：gdy1039

使用EM关闭或启动数据库

如果是XP（2003好像要添加更多的权限，未经测试，所以不作讨论）要想使EM关闭或启动数据库，需要为操作数据库的系统用户添加“作为批处理作业登录”的权限。
方法如下：

1. 开始－＞运行－＞输入MMC－＞确定
2. 文件－＞添加删除管理单元－＞添加－＞点击“组策略对象编辑器”－＞添加－＞完成－＞关闭－＞确定
3. 在打开的“本地计算机策略”中定位如下：计算机配置－＞WINDOWS设置－＞安全设置－＞本地策略－＞用户权力指派－＞右则窗口中定位“作为批处理作业登录”－＞双击－＞添加需要的用户－＞确定
4. 关闭窗口
5. 开始－＞运行－＞输入"gpupdate"－＞确定

使用SQL＊PLUS连接数据库

1. 在主机字符串中，填入的格式如下hostname:port/ORACLE_SID as sysdba(或着sysoper).　其中只有ORACLE＿SID是必需的
2. 在使用SYS连接时必需以SYSDBA或SYSOPER连接，所以主机字符串最少也应该是ORACLE＿SID　AS　SYSDBA

命令行下使用SQL＊PLUS

命令行下SQL＊PLUS命令为SQLPLUS。语法为：sqlplus [ [<option>] [<logon>] [<start>] ]

例：sqlplus sys/sys@orcl as sysdba

启动和关闭iSql*Plus

D:/Documents and Settings/gdy>isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

D:/oracle/product/10.2.0/db_1/BIN>isqlplusctl start
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.

D:/oracle/product/10.2.0/db_1/BIN>

启动和关闭管理代理程序、启动和关闭EM

dbconsole应该是依懒agent的。下面有一些命令是重复的，目标是为了展示一些出错的情况。

D:/oracle/product/10.2.0/db_1/BIN>set ORACLE_SID=

D:/oracle/product/10.2.0/db_1/BIN>emctl stop agent
Environment variable ORACLE_SID not defined. Please define it.

D:/oracle/product/10.2.0/db_1/BIN>set ORACLE_SID=orcl

D:/oracle/product/10.2.0/db_1/BIN>emctl stop agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
This will stop the Oracle Enterprise Manager 10g Database Control process. Conti
nue [y/n] :y
OracleDBConsoleorcl 服务正在停止....................
OracleDBConsoleorcl 服务已成功停止。


D:/oracle/product/10.2.0/db_1/BIN>emctl start agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
已经启动以下 Windows 服务:

   Application Layer Gateway Service
   Ati HotKey Poller
   Automatic Updates
   COM+ Event System
   Computer Browser
   Cryptographic Services
   DCOM Server Process Launcher
   DHCP Client
   Distributed Link Tracking Client
   DNS Client
   Error Reporting Service
   Event Log
   Fast User Switching Compatibility
   Help and Support
   IPSEC Services
   Logical Disk Manager
   Network Connections
   Network Location Awareness (NLA)
   OracleOraDb10g_home1iSQL*Plus
   OracleOraDb10g_home1TNSListener
   OracleServiceORCL
   Plug and Play
   Print Spooler
   Protected Storage
   Remote Access Connection Manager
   Remote Procedure Call (RPC)
   Remote Registry
   Secondary Logon
   Security Accounts Manager
   Security Center
   Server
   Shell Hardware Detection
   SSDP Discovery Service
   System Event Notification
   Task Scheduler
   TCP/IP NetBIOS Helper
   Telephony
   Terminal Services
   Themes
   WebClient
   Windows Audio
   Windows Firewall/Internet Connection Sharing (ICS)
   Windows Management Instrumentation
   Windows Time
   Wireless Zero Configuration
   Workstation
   卡巴斯基互联网安全套装6.0个人版

命令成功完成。


D:/oracle/product/10.2.0/db_1/BIN>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://192.168.1.1:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...OracleDBConsoleorcl
服务正在启动 .................................
OracleDBConsoleorcl 服务已经启动成功。


D:/oracle/product/10.2.0/db_1/BIN>emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://192.168.1.1:1158/em/console/aboutApplication
OracleDBConsoleorcl 服务正在停止.......................
OracleDBConsoleorcl 服务已成功停止。


D:/oracle/product/10.2.0/db_1/BIN>emctl stop agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
此命令的语法是:


NET STOP
service


D:/oracle/product/10.2.0/db_1/BIN>emctl start agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
已经启动以下 Windows 服务:

   Application Layer Gateway Service
   Ati HotKey Poller
   Automatic Updates
   COM+ Event System
   Computer Browser
   Cryptographic Services
   DCOM Server Process Launcher
   DHCP Client
   Distributed Link Tracking Client
   DNS Client
   Error Reporting Service
   Event Log
   Fast User Switching Compatibility
   Help and Support
   IPSEC Services
   Logical Disk Manager
   Network Connections
   Network Location Awareness (NLA)
   OracleOraDb10g_home1iSQL*Plus
   OracleOraDb10g_home1TNSListener
   OracleServiceORCL
   Plug and Play
   Print Spooler
   Protected Storage
   Remote Access Connection Manager
   Remote Procedure Call (RPC)
   Remote Registry
   Secondary Logon
   Security Accounts Manager
   Security Center
   Server
   Shell Hardware Detection
   SSDP Discovery Service
   System Event Notification
   Task Scheduler
   TCP/IP NetBIOS Helper
   Telephony
   Terminal Services
   Themes
   WebClient
   Windows Audio
   Windows Firewall/Internet Connection Sharing (ICS)
   Windows Management Instrumentation
   Windows Time
   Wireless Zero Configuration
   Workstation
   卡巴斯基互联网安全套装6.0个人版

命令成功完成。


D:/oracle/product/10.2.0/db_1/BIN>emctl stop agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
此命令的语法是:


NET STOP
service


D:/oracle/product/10.2.0/db_1/BIN>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://192.168.1.1:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...OracleDBConsoleorcl
服务正在启动 ..................................
OracleDBConsoleorcl 服务已经启动成功。


D:/oracle/product/10.2.0/db_1/BIN>emctl stop agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
This will stop the Oracle Enterprise Manager 10g Database Control process. Conti
nue [y/n] :y
OracleDBConsoleorcl 服务正在停止........................
OracleDBConsoleorcl 服务已成功停止。


D:/oracle/product/10.2.0/db_1/BIN>emctl status agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent is Not Running

D:/Documents and Settings/gdy>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://192.168.1.1:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory D:/oracle/product/10.2.0/db_1/192.168.1.1_orcl/s
ysman/log

D:/Documents and Settings/gdy>

启动和关闭数据库

D:/oracle/product/10.2.0/db_1/BIN>sqlplus system/system@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 16 21:32:08 2007

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown
ORA-01031: 权限不足
SQL> conn sys/sys@orcl
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


警告: 您不再连接到 ORACLE。
SQL> conn sys/sys@orcl as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup　　　　　 ##应该是由于shutdown的时候会断开连接，所以必需先重新连接
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
SQL> conn sys/sys
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


SQL> conn /nolog
SP2-0306: 选项无效。
用法: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
其中 <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
SQL> conn /
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

SQL> conn sys/sys@orcl as sysdba
ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务


警告: 您不再连接到 ORACLE。
SQL> conn sys/sys as sysdba          ##重新连接时必需不能指定数据库SID，因为它的实例还未启动
已连接到空闲例程。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  251658240 bytes
Fixed Size                  1248380 bytes
Variable Size              92275588 bytes
Database Buffers          155189248 bytes
Redo Buffers                2945024 bytes
SQL> startup mount
ORA-01081: ????????? ORACLE - ??????
SQL> startup open
ORA-01081: ????????? ORACLE - ??????
SQL> alter database mount;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> alter database mount;   
alter database mount
*
第 1 行出现错误:
ORA-01100: ????????????
##不能使用它后退数据库状态，要先SHUTDOWN，然后才能回到mount状态

SQL>