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 <<EOF
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;

转载于:https://blog.51cto.com/3938853/2160324