oracle验证pfile起库,Oracle_052_lesson_p4

Managing the Database Instance

管理数据库

Oracle Database 11g Release 2 management framework components:

1、Database instance

2、Listener

3、Management interface:

Database Control

Management agent (when using Grid Control)

$ . oraenv

ORACLE_SID = [orcl] ? orcl

$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0

$ echo $ORACLE_SID

export $ORACLE_SID=tech1

em启动：emctl start dbconsole

em停止：emctl stop dbconsole

访问em：https://192.168.133.120:1158/em

管理工具：

sqlplus、sql developer 、 PL/SQL、shell 脚本

例：

sqlplus hr/hr

#Name of this file: batch_sqlplus.sh

#Count employees and give raise.

sqlplus hr/hr <

select count() from employees;

update employees set salary = salary1.10;

commit;

quit

EOF

$ ./batch_sqlplus.sh

例：

script.sql:

select * from departments where location_id = 1400;

quit

$ sqlplus hr/hr @script.sql

Initialization Parameter Files

data二进制文件：spfileorcl.ora

text文本文件：initorcl.ora

DB启动顺序：

cd $ORACLE_HOME/dbs/

(1) spfile'+SID'.ora 先查找是否有此文件，有则读取并启动，不再查找。没有则向下查找

(2) pfile.ora 查找是否有此文件，有则读取并启动，不再查找。没有则向下查找

(3) init'+SID'.ora 查找是否有此文件，有则读取并启动，不再查找。没有则启动失败

cat inittech1.ora -->可见指针：spfile='+DATA/tech1/spfiletech1.ora'

SQL>show parameter spfile

SQL> create pfile from spfile; 改变为文本启动

SQL>create spfile from pfile; 改变为服务参数启动

SQL>create spfile from memory;

SQL>startup force ; 强制重启

ASMCMD工具

desc v$parameter (内存里)

desc v$spparameter (启动后的启动参数)

show parameter db_files;

show parameter processes;

show parameter memory_target;

简单的初始化参数：

basic：

CONTROL_FILES

DB_BLOCK_SIZE

PROCESSES

UNDO_TABLESPACE

…

Advanced：

DB_CACHE_SIZE

DB_FILE_MULTIBLOCK_READ_COUNT

SHARED_POOL_SIZE

…

PGA.......... target 总用户大小

修改初始化参数

1、static 参数

SQL> alter system set processes=350 scope=spfile; scope=spfile必须加，修改后也要重启才生效。

2、dynamic 参数

select ...... scope=spfile;

select ...... scope=memory;

select ...... scope=both;

select后不加 scope,表示both;

SQL> SELECT name , value FROM V$PARAMETER;

SQL>SHOW PARAMETER SHARED_POOL_SIZE;

SQL> show parameter para

Static parameters:

Can be changed only in the parameter file

Require restarting the instance before taking effect

Account for about 110 parameters

Dynamic parameters:

Can be changed while database is online

Can be altered at:

Session level

System level

Are valid for duration of session or based on SCOPE setting

Are changed by using ALTER SESSION and ALTER SYSTEM commands

Account for about 234 parameters

SQL> ALTER SESSION SET NLS_DATE_FORMAT ='mon dd yyyy';

Session altered.

SQL> SELECT SYSDATE FROM dual;

SYSDATE

jun 18 2009

SQL> ALTER SYSTEM SET SEC_MAX_FAILED_LOGIN_ATTEMPTS=2 COMMENT='Reduce from 10 for tighter security.' SCOPE=SPFILE;

System altered.

数据库起停

shutdown --> nomount --> mount --> open

1、shutdown abort ; 强制关闭DB;

2、startup nomount 或加pfile='....'

ps -ef |grep ora_

3、SQL> show parameter control_files;

SQL> select from v$instance;

SQL>select from v$database; 此时会报错

4、SQL> alter database mount;

起停顺序：

startup nomount ;

startup mount; 此时会验证控制文件；

startup open;

select name from v$datafile;

select number from v$logfile;

5、SQL>alter database open; 此时开启数据库，验证数据文件；

SQL> select status from v$instance;

如果直接startup ,则直接进入OPEN状态；

安装了GRID，才会有srvctl 工具， 使用srvctl工具来启停DB；

srvctl start database -d tech1 -o mount;

srvctl status database -d tech1;

srvctl stop database -d tech1 -o immediate;

关闭DB ，查看会话用户数：select count(*) from v$session;

常用shutdown immediate 关闭DB；

日志log

trace目录下，有LOG文件，日志及trace file;

ADR诊断知识库， .trc和.trm文件

oracle 用户下： adrci工具

show alert -tail -f 实时告警监控

Dynamic Performance Views 动态性能视图

位于shared pool 共享池中

v$开头的视图，gv$开头的全局视图

拥有者:sys用户

v$fixed_table;通过v$fixed_table 可查询所有动态性能视图名称

SQL> SELECT sql_text, executions FROM v$sql WHERE cpu_time > 200000;

SQL> SELECT * FROM v$session WHERE machine = 'EDRSR9P1' and logon_time > SYSDATE - 1;

SQL> SELECT sid, ctime FROM v$lock WHERE block > 0;

静态性能视图

dictionary 数据字典

SELECT * FROM dictionary;

DBA：所有的

ALL ：用户自己的，及授权访问的

USER_：用户自己的

SELECT table_name, tablespace_name

FROM user_tables;

SELECT sequence_name, min_value, max_value, increment_by

FROM all_sequences

WHERE sequence_owner IN ('MDSYS','XDB');

SELECT USERNAME, ACCOUNT_STATUS

FROM dba_users

WHERE ACCOUNT_STATUS = 'OPEN';

DESCRIBE dba_indexes;