Oracle_052_lesson_p4
Managing the Database Instance管理数据库
Oracle Database 11g> 1、Database instance
2、Listener
3、Management interface:
Database Control
Management agent (when using Grid Control)
$ . oraenv
ORACLE_SID = ? orcl
$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control> $ echo$ORACLE_SID
export$ORACLE_SID=tech1
em启动：emctlstartdbconsole
em停止：emctlstopdbconsole
访问em：https://192.168.133.120:1158/em
管理工具：
sqlplus、sql developer 、 PL/SQL、shell 脚本
例：
sqlplushr/hr
#Name of this file: batch_sqlplus.sh
#Count employees and give raise.
sqlplus hr/hr showparameterspfile
SQL> createpfilefromspfile;改变为文本启动
SQL>createspfilefrom pfile;改变为服务参数启动
SQL>createspfilefrom memory;
SQL>startupforce ;强制重启
ASMCMD工具
descv$parameter（内存里）
descv$spparameter(启动后的启动参数)
show parameterdb_files;
show parameterprocesses;
show parametermemory_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>> 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> Session level
System level
Are valid for duration of session or based on SCOPE setting
Are changed by using> Account for about 234 parameters
SQL>>
Session> SQL> SELECT SYSDATE FROM dual;
SYSDATE
jun 18 2009
SQL>>
System> 数据库起停
shutdown --> nomount --> mount --> open
1、shutdownabort ; 强制关闭DB;
2、startupnomount或加pfile='....'
ps -ef|grepora_
3、SQL> showparameter control_files;
SQL> select fromv$instance;
SQL>select fromv$database;此时会报错
4、SQL>> 起停顺序：
startup nomount;
startup mount;此时会验证控制文件；
startup open;
select name from v$datafile;
select numberfromv$logfile;
5、SQL>alter databaseopen;此时开启数据库，验证数据文件；
SQL> selectstatus fromv$instance;
如果直接startup ,则直接进入OPEN状态；
安装了GRID，才会有srvctl 工具， 使用srvctl工具来启停DB；
srvctlstartdatabase-dtech1-omount;
srvctl statusdatabase-dtech1;
srvctl stop database-dtech1-oimmediate;
关闭DB ，查看会话用户数：selectcount(*)fromv$session;
常用shutdownimmediate 关闭DB；
日志log
trace目录下，有LOG文件，日志及trace file;
ADR诊断知识库，.trc和.trm文件
oracle 用户下： adrci工具
showalert-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$lockWHERE 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;
页:
[1]