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;