《Oracle****》
1. Oracle Architectural Components
SQL> show sga
SQL> set wrap off
SQL> set linesize 1000
SQL> select * from v$bgprocess;
paddr <> '00' 必须的后台进程
SQL> select * from v$controlfile;
SQL> select * from v$datafile;
SQL> select * from v$logfile;
SQL> show parameter xxx;
SGA_MAX_SIZE
alter system set db_cache_size=100m; 总大小不能超过sga最大size
alter system set shared_pool_size=100m;
alter session set nls_language=american; "SIMPLIFIED CHINESE"
Library Cache
LRU算法，包括shared SQL area和PL/SQL area，命中率要求大于99%
Dictionary Cache
Database Buffer Cache，两套参数
（1）db_16k_cache_size db_block_size db_block_buffers
(2) db_cache_size db_keep_cache_size db_recycle_cache_size
DB_CACHE_ADVICE=on;
LOG_BUFFER
LARGE_POOL_SIZE: UGA(共享服务器模式session), I/O slaves and backup and restore，没有LRU list
JAVA_POOL_SIZE
PGA: stack space,sort area,cursor information,session(独占服务器模式)
Process Structure
User Process-----Server Process--------Background Process
2. Getting Start With the Oracle Server
Interactive Installation
$ ./runInstaller Non-Interactive Installation
$ ./runInstaller -responsefile myrespfile -silent DBCA Optional Flexible Architecture(OFA)
create OS user id: xxx
create OS group: ora_dba, ora_fox_dba, ora_oper, ora_fox_oper
add OS user id to ora_dba group
edit sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES= (NTS)
(1)windows，SQLNET.AUTHENTICATION_SERVICES必须设置为NTS或者ALL才能使用OS认证；不设置或者设置为其他任何值都不能使用OS认证。
(2)linux，SQLNET.AUTHENTICATION_SERVICES的值设置为ALL，或者不设置的情况下，OS验证才能成功；设置为其他任何值都不能使用OS认证。
$ orapwd file=xxx.ora password=admin entries=5
set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE in init.ora file
grant sysdba to fox
1.remote_login_passwordfile = NONE
此时停用口令文件验证，Oracle数据库不允许远程SYSDBA/SYSOPER身份登录无法通过远程进行数据库起停等操作管理
2.remote_login_passwordfile = exclusive
3.remote_login_passwordfile = SHARED
More than one database can use a password file. However, the only user recognized by the password file is SYS.
意思是说多个数据库可以共享一个口令文件,但是只可以识别一个用户:SYS很多人的疑问在于:口令文件的缺省名称是orapw<sid>,怎么能够共享? 实际上是这样的: Oracle数据库在启动时,首先查找的是orapw<sid>的口令文件,如果该文件不存在,则开始查找,orapw的口令文件，如果口令文件命名为orapw,多个数据库就可以共享. 那么你可能会有这样的疑问,多个Exclusive的数据库是否可以共享一个口令文件(orapw)呢?
SQL> show parameter password NAME TYPE VALUE [[email protected] dbs]$ strings orapw |
注意这里仅记录着INTERNAL/SYS的口令
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 时
[[email protected] dbs]$ strings orapw ]\[Z ORACLE Remote Password file HSJF INTERNAL AB27B53EDC5FEF41 8A8F025737A9097A [[email protected] dbs]$ exit exit |
注意这里,以EXCLUSIVE 方式启动以后,实例名称信息被写入口令文件.
此时如果有其他实例以Exclusive模式启动仍然可以使用这个口令文件,口令文件中的实例名称同时被改写.
也就是说,数据库只在启动过程中才读取口令文件,数据库运行过程中并不锁定该文件,类似于pfile/spfile文件.注意此时可以增加SYSDBA用户，并且这些信息可以被写入到口令文件. 一旦口令文件中增加了其他SYSDBA用户,此文件不再能够被其他Exclusive的实例共享.
SQL > select * from v$pwfile_users;
SQL > show user
配置企业管理控制台
OEMREP（OEMREP/OEMREP)
Manager Server Service 和Agent必须启动，另外还要有一个管理的schema
sysman/oem_temp必须以这个用户登陆OMS
3.Manage a Oracle Instance
Init Parameter Start Sequence
spfile<sid>.ora--------spfile.ora-----------init<sid>
select * from v$system_parameter 查看所有参数
alter system set name=value scope=memory(spfile,both)
desc v$system_parameter
ISSES_MODIFIABLE(true,false) ISSYS_MODIFIABLE(immediate,deferred,none) Create spfile
create spfile from pfile
Oracle Managed File (OMF)
DB_CREATE_FILE_DEST: for data files
DB_CREATE_ONLINE_LOG_DEST_N: redo, control files N maximun=5
drop tablespace xxx including contents and datafiles
Startup
startup [nomount|mount|open]
pfile=
restrict
recovery
force
alter database db01 mount
alter database db01 open read only
alter database open read only
alter system enable restricted session
grant restricted session to xxx
alter system disable restricted session shutdown
Normal transactional immediate abort
Diagnostic Files
alterSID.log (bdump) BACKGROUND_DUMP_DEST
Background trace files (bdump) BACKGROUND_DUMP_DEST
User trace files (udump) 缺省是关闭的 USER_DUMP_DEST MAX_DUMP_FILE_SIZE(default 10M)
sql_trace boolean FALSE
trace_enabled boolean TRUE
Session level: dbms_system.SET_SQL_TRACE_IN_SESSION
（1）execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(7, 1304, true);
（2）SELECT t.SID,t.SERIAL# FROM v$session t
4. Create a Database
Operating System Environment
ORACLE_BASE(NT) c:\oracle
ORACLE_HOME(NT) c:\oracle\ora90
ORACLE_SID
ORA_NLS33
PATH
LD_LIBRARY_PATH Preparing the Parameter File
(1) Create the new initSID.ora
$ cp init.ora $ORACLE_HOME/dba/initdb01.ora
(2) Modify the initSID.ora
db_name=db01
control_files
db_block_size=8192
#text_enable=true
(3) Create instance (Windows platform)
oradim -NEW -SID db01 -INTPWD admin -pfile='...'
(4) Create SPFILE
create spfile from pfile Starting the Instance
(1) connect as SYSDBA
(2) startup nomount
5. Data Dictionary Content and Usage
Data Dictionary(1) Data Dictionary tables
Base tables: 加密，只读
Data Dictionary View
(2) Dynamic performance tables Data Dictionary View Categories
(1) DBA
(2) ALL
(3) USER Query
(1) select * from dictionary
(2) select * from v$fixed_table
catalog.sql 创建数据字典视图
catproc.sql 创建存储过程
spool c:\temp.log
set serveroutput on
execute dbms_output.put_line('test string1');
spool off
6. Maintaining Control file
- select * from v$database;
- select * from v$tablespace;
- select * from v$archived_log;
- alter database backup controlfile to 'c:\controlfile.bak';
- alter database backup controlfile to trace; (存在udump中)
7. Maintaining Redo Log Files
SQL> select * from v$log (status: current/active/inactive/unused)
SQL> select * from v$logfile (status: blank(in use)/stale(incomplete)/delete)
Force log switch and CKPT
SQL> alter system checkpoint
SQL> alter system switch logfile
FAST_START_MTTR_TARGET (MTTR: meantime to recovery) (9i) 包含下面参数
FAST_START_IO_TARGET (8i)
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT Adding (drop) Online Redo Log Groups
SQL> alter database add logfile group 6 ('xxx1','xxx2') size 1m;
SQL> alter database drop logfile group 6;
不能删除：当前日志组，活动日志组，没有归档的日志组 Adding Online Redo Log Members
SQL> alter database add logfile member 'xxx1' to group 6;
SQL> alter database drop logfile member 'xxx1';
不能删除：最后一个 Clearing, Renaming and Relocation Online Redo Log
SQL> alter database clear logfile 'xxx' (reinit)
SQL> alter database clear unarchived logfile 'xxx' (reinit) need fullbackup database
SQL> alter database rename file 'xxx' to 'xxx1'
Archived Redo Log File (Recovery and online backup)SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog/noarchivelog; 切换日志模式
SQL> alter database open;
database full backup
SQL> archive log list
SQL> select * from v$archived_log;
SQL> alter system set log_archive_start=TRUE scope=spfile; 自动存档 LOG Minor
(1) set directory (UTL_FILE_DIR)
(2) create file
dbms_logmnr_d.build('aaa.ora','D:\oracle\admin\orcl9i\cdump');
(3) add/remove log file
dbms_logmnr.add_logfile(remove_logfile)('D:\oracle\oradata\orcl9i\REDO02.LOG',dbms_logmnr.new);
(4) start logmnr
dbms_logmnr.start_logmnr(dictfilename=>'D:\oracle\admin\orcl9i\cdump\aaa.ora');
(5) select * from v$logmnr_contents (sql_redo/sql_undo)
8. Managing Tablespaces and Data files
SQL> select * from v$tablespace;
SQL> select * from v$datafile;
SQL> select * from v$tempfile;
SQL> select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;
SQL > alter tablespace add datafile 'xxx' size 100m;
(1) System tablespace
(2) Non-System tablespace
SQL> create tablespace kong
datafile 'xxx' size 100m
extent management dictionary
default storage(
initial 100k
next 100k
pctincrease 10)
offline;
SQL> create tablespace kong
datafile 'xxx' size 100m
extent management local uniform size 256K(autoallocate);
autoextend on next 5M maxsize 200m(unlimited);
(1) Locally Managed tablespaces (推荐使用)
Free extends recorded in bitmap
Reduce contention on data dictionary table
No undo generated when space allocation
No coalescing required
(2) Dictionary-managed tablespaces
Free extends recorded in data dictionary tables
Each segment stored in the tablespace can have different storage clause
coalescing required
SQL> create undo tablespace undo1 datafile 'xxx' size 40m;
SQL> show parameter undo_management; Temporary Tablespace
Used for sort operations
Cannot contain any permanent objects
SQL> create temporary tablespace kong4
tempfile='xxx' size 5m extent mangement local;
SQL> alter database default temporary tablespace=kong4; Offline status
SQL> alter tablespace userdata offline(online) Tablespace Read Only
SQL> alter tablespace userdata read only;
SQL> alter tablespace userdata rename 'xxx'; Dropping Tablespace
SQL> drop tablespace userdata including contents and datafiles; Resizing Tablespace
(1) automatically (AUTOEXTEND ON)
(2) Manually SQL> alter database datafile 'xxx' resize 200m;
(3) add file SQL> alter tablespace kong add datafile 'xxx' size 100m; Moving Data Files SQL> alter tablespace userdata rename 'xxx' to 'xxx1'
9. Storage Structure and Relationships
SQL> alter table xxx allocation extent (size 1m datafile 'xxx');
SQL> alter table xxx deallocation unused;
Database Block
(1) set at tablespace creation
SQL> create tablespace kong2 datafile 'xxx' blocksize 8k; (必须指定db_8k_cache_size)
(2）DB_BLOCK_SIZE is the default block size
initrans maxtrans
pctfree: 导致row migration(系统开销）
pctused:通知可以再使用
Configuring Automatic Segment-Space ManagementSQL> create tablespace xxx ... SEGMENT SPACE MANAGEMENT AUTO; Manual Segment Space Management (pctfree,pctused,freelist)