Oracle SQL Analyze DBA
SQLADMIN
SQLADMIN VMQROLE.SQL
$ORACLE_HOME¥SYSMAN¥ADMIN
1. Oracle Enterprise Manager SQL Worksheet
2. SQL Analyze SYS
3. VMQROLE.SQL
SQLADMIN 4. SQL Worksheet
SQLADMIN
Grant SQLADMIN to < >;
5. SQL Worksheet
A
Oracle Enterprise Manager
Oracle Enterprise Manager
Oracle Intelligent Agent
Oracle SQL*Net Oracle Enterprise Manager 1.4
Oracle8 Net8
Oracle Data Manager Oracle Backup Manager Oracle Tablespace Manager
SQL* Net V2.3 Net 8
Oracle Enterprise Manager
sqlnet.ora
################
# Filename...: sqlnet.ora
# Name...: tcpcom.world
# Date...: 13-AUG-97 10:09:52
################
AUTOMATIC_IPC = OFF TRACE_LEVEL_CLIENT = OFF SQLNET.EXPIRE_TIME = 0 NAMES.DEFAULT_DOMAIN = world NAME.DEFAULT_ZONE = world
SQLNET.CRYPTO_SEED = "2418306024240649"
SQLNET.AUTHENTICATION_SERVICES = (NONE) DAEMON.TRACE_LEVEL = 16
DAEMON.TRACE_DIRECTORY = c:¥orant¥network¥trace
sqlnet.ora world
tnsnames.ora world
DAEMON 3
DAEMON.TRACE_LEVEL OFF
: world world
ORACLE_
HOME¥NET80¥TRACE
ORACLE_HOME C:¥ORANT
DAEMON.TRACE_DIRECTORY daemon.trc
tnsnames.ora
################
# Filename...: tnsnames.ora
# Name...: LOCAL_REGION.world
# Date...: 13-AUG-97 10:09:52
################
mydb.world = (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS =
(COMMUNITY = tcpcom.world) (PROTOCOL = TCP)
(Host = myhost) (Port = 1526) )
)
(CONNECT_DATA = (SID = mysid)
(GLOBAL_NAME = mydb.world) )
)
tnsnames.ora $ORACLE_HOME/network/admin
$ORACLE_HOME/net80/admin Solaris tnsnames.ora
: mysid SID mydb
global_name world world
Net8
Oracle Oracle Intelligent Agent
sqlnet.ora
################
# Filename...: sqlnet.ora
# Name...: myhost.world
# Date...: 13-AUG-97 10:09:52
################
AUTOMATIC_IPC = ON TRACE_LEVEL_CLIENT = OFF SQLNET.EXPIRE_TIME = 0 NAMES.DEFAULT_DOMAIN = world NAME.DEFAULT_ZONE = world
SQLNET.CRYPTO_SEED = "2418306024240649"
SQLNET.AUTHENTICATION_SERVICES = (ALL)
sqlnet.ora world
tnsnames.ora world
tnsnames.ora
################
# Filename...: tnsnames.ora
# Name...: LOCAL_REGION.world
# Date...: 13-AUG-97 10:09:52
################
mydb.world = (DESCRIPTION =
(ADDRESS_LIST = (ADDRESS =
(COMMUNITY = tcpcom.world) (PROTOCOL = TCP)
(Host = myhost) (Port = 1526) )
)
: world world
(CONNECT_DATA = (SID = mysid)
(GLOBAL_NAME = mydb.world) )
)
listener.ora
################
# Filename...: listener.ora
# Name...: myhost.world
# Date...: 13-AUG-97 10:09:52
################
mylsnr =
(ADDRESS_LIST = (ADDRESS =
(PROTOCOL=IPC) (KEY= mydb.world) )
(ADDRESS = (PROTOCOL=IPC) (KEY= mysid) )
(ADDRESS =
(COMMUNITY = tcpcom.world) (PROTOCOL = TCP)
(Host = myhost) (Port = 1526) )
)
STARTUP_WAIT_TIME_mylsnr = 0 CONNECT_TIMEOUT_mylsnr = 10 TRACE_LEVEL_mylsnr = OFF SID_LIST_mylsnr =
(SID_LIST = (SID_DESC =
(SID_NAME = mysid)
(ORACLE_HOME = /myoraclehome) )
)
listener.ora SQL*Net mylsnr
SQL*Net
listener ) mylsnr
7.3.3 snmp.ora
################
# Filename...: snmp.ora.sample
#################
snmp.visibleservices = (mydb_name.world, myhost_name_mylsnr.world) snmp.index.mydb_name.world = 1
snmp.index.myhost_name_mylsnr.world = 2 snmp.contact.mydb_name.world = contact_info
snmp.contact.myhost_name_mylsnr.world = contact_info snmp.sid.mydb_name.world = server_id
snmp.oraclehome.mydb_name.world=$ORACLE_HOME nmi.register_with_names=false
nmi.trace_level = 0
nmi.trace_directory = $ORACLE_HOME¥net80¥trace
dbsnmp.address = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost_
name)(PORT=1748)))
dbsnmp.spawnaddress =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost_
name)(PORT=1754)))
snmp.ora 7.3.3 7.3.3
tnsnames.ora snmp.ora Oracle Network Manager Network Topology Generator
snmp_ro.ora snmp_rw.ora 7.3.3
snmp_ro.ora
snmp_ro.ora Windows NT $ORACLE_
HOME¥net80¥admin UNIX $ORACLE_
HOME/network/admin
SNMP.VISIBLESERVICES = (LISTENER, service_name1, service_name2, ...) SNMP.SID.service_name = server_id
SNMP.ORACLEHOME.service_name = ORACLE_HOME_DIR
snmp_rw.ora
snmp_rw.ora Windows NT $ORACLE_HOME¥net80¥admin
SNMP.INDEX.service_name = unique_index_number SNMP.CONTACT.service_name.world = "contact_info"
NMI.REGISTER_WITH_NAMES = FALSE
NMI.TRACE_LEVEL = OFF | USER | ADMIN | nn
7.3.3 1748 1754
TCP/IP
SNMP.CONNECT.service_name.USER = user_name SNMP.CONNECT.service_name.PASSWORD = password SNMP.DBPOLLTIME = nn
DBSNMP.IPCTIME = nn
NMI.TRACE_DIRECTORY = directory NMI.TRACE_FILE = filename NMI.LOG_DIRECTORY = directory NMI.LOG_FILE = filename
services.ora
services.ora Windows NT
$ORACLE_HOME¥net80¥agent UNIX $ORACLE_
HOME/network/agent Oracle
Oracle Enterprise Manager
snmp*.ora
: services.ora
■ service_name tnsnames.ora
■ host_name
■ world
SNMP.VISIBLESERVICES = (service_name1.world, service_name2.world, ...) SNMP
tnsnames.ora Names Server
SNMP.INDEX.service_name.world = index_number
snmp.index.<service_name1>=10 snmp.index.<service_name2>=20
SNMP.SID.service_name.world = server_id
ID SID SNMP.CONNECT.service_name.world.USER = user_name
dbsnmp catsnmp.sql
SNMP SNMP
SNMP NetWare
dbsnmp/dbsnmp
: sqlnet.ora .world snmp.ora
tnsnames.ora .world service_name.world host_name_lsnr.world
SNMP.CONNECT.service_name.world.PASSWORD = password
dbsnmp
catsnmp.sql
SNMP SNMP
SNMP NetWare
dbsnmp/dbsnmp
SNMP.ORACLEHOME.service_name.world = ORACLE_HOME_DIR
Oracle ORACLE_HOME_DIR
SNMP.CONTACT.service_name.world ="contact_info"
DBSNMP.POLLTIME = nn
30 DBSNMP.IPCTIME = nn
Work Comm ping
Work ping Comm
Work Comm
30
NMI.TRACE_LEVEL = OFF | USER | ADMIN | nn
13 16
16 TCP/IP
15
NMI.TRACE_DIRECTORY = directory
nmi.trace_level
$ORACLE_
HOME¥network¥trace NMI.TRACE_FILE = filename
NMI.LOG_DIRECTORY = directory
NMI.LOG_FILE = filename
Windows NT dbsnmp
dbsnmp.address =(ADDRESS=(PROTOCOL=protocol) (HOST=host_name)(PORT=port_
no)))
TNS
TCP/IP
PORT=1748 1748
IANA Oracle TCP
:
Oracle Enterprise Manager
Oracle Enterprise Manager
7.3.3 Oracle Enterprise Manager
tnsnames.ora
dbsnmp.spawnaddress =(ADDRESS= (PROTOCOL=protocol) (HOST=host_
name)(PORT=spnport_no)))
RPC TNS
spnport_no DBSNMP.ADDRESS port_no
PORT=1754 1754
IANA Oracle TCP
Oracle Enterprise Manager
B
Oracle Enterprise Manager
Oracle Enterprise Manager
Oracle Enterprise Manager
Oracle Enterprise Manager
Oracle Enterprise Manager
PC ORACLE_HOME Windows
NT Windows NT ORACLE_HOME
¥orant
¥orant¥bin
Oracle Enterprise Manager DLL
¥orant¥dbs
Oracle Enterprise Manager
¥orant¥otrace...
Oracle Trace
Oracle Enterprise Manager
Oracle Enterprise Manager ¥orant¥sysman¥...
¥orant¥sysman¥admin
Oracle Enterprise Manager SQL
¥orant¥sysman¥admin¥rdbms70, rdbms71, rdbms72, rdbms73, rdbms80
TopSessions SQL
Oracle
¥orant¥sysman¥admin¥tcl tcl
¥orant¥sysman¥bmp
¥orant¥sysman¥doc Oracle Enterprise Manager
HTML .htm toc.htm
Oracle Enterprise Manager
¥orant¥sysman¥expert Oracle Expert
¥orant¥sysman¥ifiles Oracle Instance Manager
¥orant¥sysman¥ocm160 Change manager
¥orant¥sysman¥ole2
Oracle Enterprise Manager NT
¥orant¥sysman¥osm10 Oracle Software Manager
¥orant¥sysman¥output
¥orant¥sysman¥qtour
Expert Quick Tour
¥orant¥sysman¥scripts
DBA SQL Tcl
¥orant¥sysman¥sdk
Oracle Enterprise Manager Software Developer’s Kit (SDK)
¥orant¥sysman¥temp
¥orant¥sysman¥vtm160 Oracle Performance Manager
¥orant¥sysman¥vtp160 Oracle Capacity planner
: temp
Oracle Enterprise Manager
¥orant¥net80¥...
¥orant¥net80¥admin SQL*Net
¥orant¥net80¥log
¥orant¥net80¥trace
ORACLE_HOME Windows NT
Windows NT ORACLE_HOME ¥orant
¥orant¥agentbin
dbsnmp
¥orant¥net80¥admin
snmp_ro.ora snmp_rw.ora
¥orant¥net80¥log nmiconf.log
¥orant¥net80¥mesg
¥orant¥net80¥agent
*.q services.ora
¥orant¥rdbms80¥admin
SQL catsnmp.sql
SNMPAGENT catsnmp.sql
catalog.sql catnsnmp.sql
catsnmp
¥orant¥net80¥agent¥config Tcl
¥orant¥net80¥agent¥doc readme
¥orant¥net80¥agent¥events¥oracle oracle
¥orant¥net80¥agent¥jobs¥oracle oracle
¥orant¥net80¥agent¥mibs
MIB orainit.mib
¥orant¥net80¥agent¥packages Oracle Software Manager
¥orant¥net80¥agent¥tcl Tcl
C
Oracle EnterpriseManager Oracle Enterprise Manager
Readme readme
$ORACLE_HOME¥SYSMAN¥ADMIN
Oracle Enterprise Manager 1.x.x. 8.x.x
Oracle Enterprise Manager 1.x.x NT 8.x.x ORACLE_HOME
Oracle Enterprise Manager 1.x.x ORACLE_HOME
Oracle Enterprise Manager
■ C-2
■ C-5
■ C-5
■ C-6
Oracle Installer Oracle Enterprise Manager
Oracle Enterprise Manager Oracle Installer
Windows
ORACLE_HOME¥SYSMAN¥OLE2 .REG
.OCX .OCX
1. DOS ¥ORACLE_HOME¥BIN
2. DIR *.OCX .OCX
3. .OCX REGSVR32 name.OCX name .OCX
: regsvr32 vojt.ocx
Windows NT
■ UNIX ORACLE_HOME/network/log/dbsnmp*.log
■ Windows NT
1
NT
OS snmimsg.mc
Windows NT
OS
01 OracleAgent Service Control Handler 02 OracleAgent Service Control Manager 03 OracleAgent
04 OracleAgent 05 OracleAgent 06 OracleAgent
07 OracleAgent nmiconf.tcl
nmiconf.log
08 OracleAgent Oracle CORE 09 OracleAgent Oracle NLS
10 OracleAgent Oracle SQL*Net %1
11 OracleAgent DES
12 OracleAgent Oracle Remote Operations 13 OracleAgent Oracle Software Manager
14 OracleAgent dbsnmp.ver
15 OracleAgent /
16 OracleAgent 17 OracleAgent
18 OracleAgent SNMP
19 OracleAgent SNMP
20 OracleAgent
SID
SID SID sqlnet
tnsnames.ora SQL*Plus
Net8 Net8 Assistant sqlnet 2.x Oracle Network Manager/SQL*NET easy config
TCP/IP scott-PC ORCL SID
sqlnet scott.world
scott.world=
(Description=
(Address_list=
(Address=
(community=TCP.world) (protocol=TCP) (host=scott-PC) (port=1521) (
(Address=
(community=TCP.world) (protocol=TCP) (host=scott-PC) (port=1526) )
)
(connect_data=
(SID=ORCL) )
)
SQL*Plus
sqlplus> connect username/password@scott
SQL*Plus sqlplus>
SQL*Plus SQL*NET
24 OracleAgent
Personal Oracle 7 Windows 95 Windows NT
1. ¥orawin95¥net80¥admin¥tnsnames.ora
local.world tnsnames.ora
local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(COMMUNITY = beq.world) (PROTOCOL = BEQ) (PROGRAM = oracle73) (ARGV0 = oracle73ORCL) (ARGS =
'(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') )
)
(CONNECT_DATA = (SID = ORCL) )
)
2. Oracle Enterprise Manager 3.
PC
4. : tnsnames.ora
local.world
5. Databases
DOS Repository
Manager Oracle Enterprise Manager
DOS
1. DOS ¥ORACLE_HOME¥BIN
2. DOS vobsh 3–1 vobsh
1
Oracle Enterprise Manager
vobsh -c "scott/tiger@mydb" -o VALIDATE -p "Enterprise Manager"
3–1 vobsh
Oracle Enterprise Manager -b
-c cn cn scott/tiger@mydb
/
-h -o option
CREATE DROP VALIDATE
-p name
Oracle Enterprise Manager Oracle Software Manager Oracle Expert Oracle
Trace ALL CONSOLE
PERFORMANCE ALL PERFORMANCE
CONSOLE Oracle Enterprise Manager Oracle Software Manager
name
1.
2. [F1]
Windows
agent Oracle Intelligent Agent
agent discovery
access (database)
Oracle 3 1 1
account
ID ID
application 1
authenticate
authorization
back up
batch
cache (memory)
Oracle Server
click
client
2
Communication Daemon
OEM OEM
Oracle Intelligent Agent connect
Oracle
Console 1.
GUI 2. Windows NT Windows 95
Oracle Enterprise Manager OEM
create
Oracle Daemon Manager
database
1. Oracle Server
REDO 3
2. 3. 1
database administrator: DBA 1. Oracle Server
2. DBA
Oracle 2
database name TNSNAMES.ORA
data definition language: DDL
SQL
CREATE ALTER DROP
DLL
Windows NT/95
datafile
1
Oracle Data Manager Oracle
discovery
Oracle Enterprise Manager discovery cache
distribution host
event
Event Management System
export
Oracle
extent
file
filename
fixit job Oracle Enterprise
grant
group
Name Server
index
instance
Oracle
Oracle 2
integrity constraint
Management Pack
OEM Oracle Tools Oracle
Oracle Enterprise Manager
Navigator
Name Server
node
Oracle Intelligent Agent
OEM SQLNET
RPC
SNMP SNMP
SNMP Management Information Blocks (MIB) Oracle
Oracle Oracle Process Oracle Server
OraTcl
Oracle Tcl Oracle
package
1
package body
package specification
procedure
SQL PL/SQL
property sheet
Q Q Files
REDO redo log
repository Oracle
Q ereg.q user.q job.q
jstat1.q evocc1.q
role
rollback segment
scalability
schema
schema object
sequence
session
Oracle Server
UNIX Windows NT Oracle
Simple Network Management Protocol (SNMP) 1
snapshot log
SQL Worksheet
SQL PL/SQL
synonym
System Global Area: SGA
tablespace
1 1
Tool Command Language: Tcl
Tcl topology
Oracle Enterprise Manager Oracle Enterprise Manager
trigger
INSERT UPDATE DELETE
PL/SQL user process
Oracle Tool view
1
C
catbloxx.sql
Oracle 3-2
catnsnmp.sql B-4
catsnmp.sql 1-18 B-4
D
DBA , 2-10
DBA
2-3 dbsnmp.address A-10 DBSNMP.POLLTIME A-9 dbsnmp.spawnaddress A-11 Diagnostics Pack 3-2 discnode.txt 2-5
G
GLOBAL_DBNAME 1-3 1-12 1-17
L
listener.ora A-5
listener.ora 1-17
N
NAMES.PREFERRED_SERVER 2-9 NMI.LOG_DIRECTORY A-10
O
Oracle Enterprise Manager
sysman B-2
2-10
B-2
B-4 Oracle Expert
3-7
Oracle Names 1-17 2-9 Oracle Performance Manager
3-5 Oracle TopSessions
3-2 Oracle Trace
3-3
3-4 3-4
ORACLE_HOME B-2
Oracle
3-2 3-7
oratab 1-11
R
Repository Manager 2-4 root.sh 1-8
root.sh 1-10
S
smptsixx.sql
Oracle TopSessions 3-2
Oracle 3-2
smptsixx.sql 3-2
snmp*.ora A-7
snmp_ro.ora 1-2 1-11 A-6 snmp_rw.ora 1-2 1-11 A-7
SNMPAGENT B-4
SNMP.CONNECT.service_name.world.PASSWORD A-9
SNMP.CONNECT.service_name.world.USER A-8 SNMP.CONTACT.service_name.worl A-9 SNMP.INDEX.service_name.world A-8 snmp.ora A-6
A-6 A-7
SNMP.ORACLEHOME.service_name.world A-9 SNMP.SID.service_name.world A-8
SNMP.VISIBLESERVICES A-8 SQL*Net
A-2 sqlnet.ora A-2 A-4
sysman B-2
T
Tablespace Manager 3-7
TCP ,
A-10
TNS_ADMIN 1-3 1-12
tnsnames.ora A-3 A-4
topology.ora 2-8
Tuning Pack 3-7
U
utlchain.sql
Oracle 3-7
Tablespace Manager 3-7 utlxplxx.sql
Oracle 3-2
W
Windows NT 1-3
3-2 3-7
snmpagent B-4
TCP A-10
UNIX
setuid 1-9
1-8 1-13
1-11 1-11
Windows NT
1-2 1-5
1-2 1-2
1-3 2-6 UNIX 1-8
Windows NT 1-2 UNIX 1-11
Windows NT 1-2 1-6 1-18 B-4
1-9
Windows NT 1-16 Windows UNIX
1-16
networkagent B-4
DBA 2-10
Oracle Enterprise Manager 2-6 Oracle Enterprise Manager 2-10
B-4
topo_ops.ora 2-7
2-7 2-7 2-7 2-7
listener.ora A-5 services.ora A-7 snmp_ro.ora A-6 snmp_rw.ora A-7 snmp.ora A-6 SQL*Net A-2 sqlnet.ora A-2 A-4 tnsnames.ora A-3 A-4
A-2 A-4
2-7
2-7 2-7
Oracle Expert 3-7
Oracle Trace 3-4
Oracle Trace 3-4
Windows NT 1-3
, B-4
2-7
Oracle Trace 3-3 SQL Analyze 3-8 Tablespace Manager 3-7 Tuning Pack 3-7
, 2-10
B-2
1-5 C-2 1-7 1-13
2-17
B-4
1-3
snmp.ora A-9
A-9
3-5
, B-4