lessinstance-p學(xué)完本課后應(yīng)能完成以下工作

管理Oracle

實(shí)例課程目標(biāo)學(xué)完本課后，應(yīng)能完成以下工作：?jiǎn)?dòng)和停止OracleDB

和組件使用Oracle

Enterprise

Manager使用

SQL*Plus

數(shù)據(jù)庫(kù)修改數(shù)據(jù)庫(kù)初始化參數(shù)描述數(shù)據(jù)庫(kù)啟動(dòng)階段描述數(shù)據(jù)庫(kù)關(guān)閉選項(xiàng)查看 日志?動(dòng)態(tài)性能視圖管理框架Oracle

Database

11g

管理框架組件：???數(shù)據(jù)庫(kù)實(shí)例程序管理界面：Database

Control管理

（使用

Grid

Control

時(shí)）程序DatabaseControl管理管理界面或啟動(dòng)和停止

Database

Control$

emctl

start

dbconsoleOracle

Enterprise

Manager

11g

Database

Control

Release

11.1.0.6.0Copyright

(c)

1996,

2007

Oracle

Corporation.

s

.:1158/em/console/aboutApplicationStarting

Oracle

Enterprise

Manager

11g

Database

Control

started._orcl/sysman/Logs

are

generated

in

directory/u01/app/oracle/product/11.1.0/db_1/edrsrlog$

emctl

stop

dbconsoleOracle

Enterprise

Manager

11g

Database

Control

Release

11.1.0.6.0Copyright

(c)

1996,

2007

Oracle

Corporation.

s

.:1158/em/console/aboutApplicationStop Oracle

Enterprise

Manager

11g

Database

Control

......

Stopped.Oracle

Enterprise

Manager數(shù)據(jù)庫(kù)主頁(yè)屬性頁(yè)其它Oracle

工具SQL*Plus

還提供另一個(gè)用于數(shù)據(jù)庫(kù)的界面，以便您可以：、更新或刪執(zhí)行數(shù)據(jù)庫(kù)管理操作通過(guò)執(zhí)行SQL

命令在數(shù)據(jù)庫(kù)中查詢(xún)、除數(shù)據(jù)SQL

Developer：是用于

的

Oracle

DB

實(shí)例的圖形用戶(hù)界面支持以SQL

和PL/SQL

進(jìn)行開(kāi)發(fā)默認(rèn)Oracle

DB

安裝中提供組件>SQL*Plus初始化參數(shù)數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉日志性能視圖使用SQL*PlusSQL*Plus：是一個(gè)命令行工具可以通過(guò)交互方式或者在批處理模式下使用$

sqlplus

hr/hrSQL*Plus:

Release

11.1.0.6.0

-

Production

on

Tue

Aug

5

02:39:20

2008Copyright

(c)

1982,

2007,

Oracle.

s

.Connected

to:Oracle

Database

11g

Enterprise

Edition

Release

11.1.0.6.0

-

ProductionWith

the

Partitioning,

OLAP,

Data

Mining

and

Real

Application

TestingoptionsSQL>

select

last_name

from

employees;LAST_NAMEAbelAndeAtkinson…從

S

調(diào)用

SQL*Plus$./batch_sqlplus.shSQL*Plus:

Release

11.1.0.6.0

-

Production

on

Tue

Aug

5

02:48:34

2008Copyright

(c)

1982,

2007,

Oracle.

s

.Connected

to:Oracle

Database

11g

Enterprise

Edition

Release

11.1.0.6.0

-

ProductionWith

the

Partitioning,

OLAP,

Data

Mining

and

Real

Application

TestingoptionsSQL>COUNT(*)-107SQL>107

rows

updated.SQL>Commit

complete.SQL>

Disconnected

from

Oracle

Database

11g

Enterprise

Edition

Release11.1.0.6.0

-

ProductionWith

the

Partitioning,

OLAP,

Data

Mining

and

Real

Application

Testingoptions$#

Name

of

this

file:

batch_sqlplus.sh#

Count

employees

and

give

raise.sqlplus

hr/hr

<<EOFselect

count(*)

from

employees;update

employees

set

salary

=

salary*1.10;commit;quitEOF輸出$

sqlplus

hr/hr@script.sqlSQL*Plus:

Release

11.1.0.6.0

-

Production

on

Tue

Aug

5

03:01:34

2008Copyright

(c)

1982,

2007,

Oracle.

s

.Connected

to:Oracle

Database

11g

Enterprise

Edition

Release

11.1.0.6.0

-

ProductionWith

the

Partitioning,

OLAP,

Data

Mining

and

Real

Application

TestingoptionsDEPARTMENT_ID

DEPARTMENT_NAME MANAGER_ID

LOCATION_ID60

IT

103

1400Disconnected

from

Oracle

Database

11g

Enterprise

Edition

Release11.1.0.6.0

-

ProductionWith

the

Partitioning,

OLAP,

Data

Mining

and

Real

Application

Testingoptions從SQL*Plus

調(diào)用SQLscript.sql輸出select

*

from

departments

where

location_id

=

1400;quitspfileorcl.ora初始化參數(shù)文件組件SQL*Plus>初始化參數(shù)數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉日志性能視圖簡(jiǎn)化初始化參數(shù)DB_CACHE_SIZEDB_FILE_MULTIBLOCK_READ_COUNTSHARED_POOL_SIZE…高級(jí)CONTROL_FILESDB_BLOCK_SIZEPROCESSESUNDO_MANAGEMENT…基本初始化參數(shù)：示例參數(shù)指定CONTROL_FILES一個(gè)或多個(gè)控制文件名DB_FILES最大數(shù)據(jù)庫(kù)文件數(shù)PROCESSES可以同時(shí)連接的最大操作系統(tǒng)用戶(hù)進(jìn)程數(shù)DB_BLOCK_SIZE所有表空間使用的標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)塊大小DB_CACHE_SIZE標(biāo)準(zhǔn)塊緩沖區(qū)高速緩存大小初始化參數(shù)：示例參數(shù)指定SGA_所有SGA

組件的總大小MEMORY_Oracle

系統(tǒng)范圍內(nèi)可用的內(nèi)存共享SQL區(qū)域SGA數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存重做日志緩沖區(qū)Java

池流池共享池大型池庫(kù)高速緩存數(shù)據(jù)字典高速緩存其它I/O

緩沖區(qū)響應(yīng)隊(duì)列請(qǐng)求隊(duì)列空閑內(nèi)存初始化參數(shù)：示例參數(shù)指定PGA_AGGREGATE_分配給所有服務(wù)器進(jìn)程的PGA

內(nèi)存量SHARED_POOL_SIZE共享池大?。ㄗ止?jié)）UNDO_MANAGEMENT要使用的還原空間管理模式使用SQL*Plus

查看參數(shù)SQL>

SELECT

name

,

value

FROM

V$PARAMETER;NAME

VALUE2150170TRUE0lock_name_spaceprocessessessionstimed_statisticstimed_os_statistics…SQL>SHO RAMETER

SHARED_POOL_SIZENAMETYPE

VALUEbig

integer0shared_pool_sizeSQL>

sho rameter

paraNAMETYPEVALUELOWTRUEFALSE2148stringbooleanbooleanintegerstringfast_start_parallel_rollbackparallel_adaptive_multi_userparallel_automatic_tuningparallel_execution_message_sizeparallel_instance_group…更改初始化參數(shù)值?靜態(tài)參數(shù)：只能在參數(shù)文件中更改必須重新啟動(dòng)實(shí)例才能生效動(dòng)態(tài)參數(shù)：可以在數(shù)據(jù)庫(kù)聯(lián)機(jī)時(shí)更改可以在以下級(jí)別更改：會(huì)話(huà)級(jí)別系統(tǒng)級(jí)別在會(huì)話(huà)持續(xù)期間內(nèi)有效，或有效性取決于SCOPE

設(shè)置通過(guò)使用ALTER

SESSION

和ALTER

SYSTEM

命令進(jìn)行更改更改參數(shù)值：示例SQL>

ALTER

SESSIONSET

NLS_DATE_FORMAT

='mon

dd

yyyy';Session

altered.SQL>

SELECT

SYSDATE

FROM

dual;SYSDATEjun

12

2007SQL>

ALTER

SYSTEM

SETSEC_MAX_FAILED_LOGIN_ATTEMPTS=2

COMMENT='Reducefrom

10

for

tighter

security.'

SCOPE=SPFILE;System

altered.數(shù)據(jù)庫(kù)啟動(dòng)和關(guān)閉組件SQL*Plus初始化參數(shù)>數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉日志性能視圖或啟動(dòng)Oracle

DB

實(shí)例啟動(dòng)Oracle

DB

實(shí)例：NOMOUNTOPEN

SSTARTUPMOUNTNOMOUNTHUTDOWN實(shí)例已啟動(dòng)啟動(dòng)Oracle

DB

實(shí)例：MOUNTOPEN

SSTARTUPMOUNTNOMOUNT此實(shí)例的控制文件已打開(kāi)HUTDOWN實(shí)例已啟動(dòng)啟動(dòng)Oracle

DB

實(shí)例：OPENOPEN

S已按此實(shí)例控制文件的描述打開(kāi)了所有文件STARTUPMOUNTNOMOUNT此實(shí)例的控制文件已打開(kāi)HUTDOWN實(shí)例已啟動(dòng)啟動(dòng)選項(xiàng)：示例SQL>startup1SQL>startup

nomount2SQL>alter

database

mount;3SQL>alter

database

open;4關(guān)閉Oracle

DB

實(shí)例組件SQL*Plus初始化參數(shù)數(shù)據(jù)庫(kù)啟動(dòng)>數(shù)據(jù)庫(kù)關(guān)閉日志性能視圖關(guān)閉模式關(guān)閉模式：A=ABORTI=IMMEDIATET=TRANSACTIONALN=NORMAL關(guān)閉模式AITN允許新連接否否否否等待當(dāng)前會(huì)話(huà)結(jié)束否否否是等待當(dāng)前事務(wù)處理結(jié)束否否是是強(qiáng)制選擇檢查點(diǎn)并關(guān)閉文件否是是是關(guān)閉選項(xiàng)在以下模式：SHUTDOWNNORMAL或SHUTDOWNTRANSACTIONAL或SHUTDOWNIMMEDIATE一致的數(shù)據(jù)庫(kù)（干凈的數(shù)據(jù)庫(kù)）關(guān)閉時(shí)：?執(zhí)行IMMEDIATE時(shí)，會(huì)回退未提交的更改數(shù)據(jù)庫(kù)緩沖區(qū)高速緩存，會(huì)寫(xiě)入到數(shù)據(jù)文件會(huì)

資源啟動(dòng)時(shí)：不用恢復(fù)實(shí)例關(guān)閉選項(xiàng)：示例SQL>shutdownSQL>shutdowntransactionalSQL>shutdownimmediateSQL>shutdownabort關(guān)閉選項(xiàng)在以下模式中：SHUTDOWN

ABORT或?qū)嵗e(cuò)誤或STARTUP

FORCE不一致的數(shù)據(jù)庫(kù)（灰數(shù)據(jù)庫(kù)）關(guān)閉時(shí)：修改過(guò)的緩沖區(qū)未寫(xiě)入數(shù)據(jù)文件不回退未提交的更改啟動(dòng)時(shí)：?使用聯(lián)機(jī)重做日志文件重新應(yīng)用更改使用還原段回退未提交的更改會(huì)

資源使用SQL*Plus

啟動(dòng)和關(guān)閉$

sqlplus

dba1

as

sysdbaPassword:SQL>

shutdownimmediateDatabase

closed.Database

dismounted.ORACLE

instance

shutdown.SQL>

startupORACLE

instance

started.Total

System

GlobalArea285212672bytesFixed

Size1218472bytesVariable

Size250177624bytesDatabase

Buffers33554432bytesRedo

Buffers262144bytesDatabase

mounted.Database

opened.SQL>管理查看

日志Database

Home

page

>

Related

Links

region

>Alert

Log

Content（“數(shù)據(jù)庫(kù)”主頁(yè)

>“相關(guān)”區(qū)域>

日志內(nèi)容）組件SQL*Plus初始化參數(shù)數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉>

日志性能視圖查看歷史記錄使用文件每個(gè)服務(wù)器和 進(jìn)程都可以寫(xiě)入關(guān)聯(lián)的文件。錯(cuò)誤信息寫(xiě)入相應(yīng)的

文件中。??自動(dòng)

資料是系統(tǒng)范圍的數(shù)據(jù)庫(kù)庫(kù)(ADR)和事件記錄數(shù)據(jù)，例如：資料庫(kù)——日志—

健康監(jiān)視報(bào)表動(dòng)態(tài)性能視圖用于

實(shí)例內(nèi)存結(jié)構(gòu)的不斷變化的狀態(tài)的信息會(huì)話(huà)數(shù)據(jù)等待事件內(nèi)存分配運(yùn)行SQLUNDO

使用率打開(kāi)的游標(biāo)重做日志使用率…等等Oracle

實(shí)例組件SQL*Plus初始化參數(shù)數(shù)據(jù)庫(kù)啟動(dòng)數(shù)據(jù)庫(kù)關(guān)閉日志>性能視圖動(dòng)態(tài)性能視圖：用法示例v$lockSQL>

SELECT

sid,

ctime

FROMWHERE

block

>

0;v$sessionSQL>

SELECT

*

FROM WHERE

machine

='EDRSR9P1'

and

logon_time

>

SYSDATE

-

1;v$sqlSQL>

SELECT

sql_text,

executions

FROMWHERE

cpu_time

>

200000;abc動(dòng)態(tài)性能視圖：注意事項(xiàng)??這些視圖由SYS

用戶(hù)擁有。不同的時(shí)間可使用不同的視圖：實(shí)例已啟動(dòng)。數(shù)據(jù)庫(kù)已裝載。數(shù)據(jù)庫(kù)已打開(kāi)。???可以通過(guò)查詢(xún)

V$FIXED_TABLE

查看所有視圖名稱(chēng)。這些視圖通常稱(chēng)為“V-$

視圖”。因?yàn)閿?shù)據(jù)是動(dòng)態(tài)的，所以在這些視圖上不能保證一致性。數(shù)據(jù)字典：概覽SELECT

*

FROM

dictionary;表索引視圖用戶(hù)方案過(guò)程…等