《Oracle视频教程》

william750214

浏览: 303851 次
性别:
来自: 上海

最近访客更多访客>>

wenmin92

谁说我不是会员

buralin

LSSss

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle Database Server

Oracle SQL SQL Server OS Cache

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认证。

Using Password File Authentication
$ 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
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string SHARED

[oracle@jumper dbs]$ strings orapw
]\[Z
ORACLE Remote Password file
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A

注意这里仅记录着INTERNAL/SYS的口令

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 时

[oracle@jumper dbs]$ strings orapw
]\[Z
ORACLE Remote Password file
HSJF
INTERNAL
AB27B53EDC5FEF41
8A8F025737A9097A
[oracle@jumper 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

Post run scripts in %ORACLE_HOME/rdbms/build_db.sql

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

Create 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

Undo Tablespace
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

Extent Allocation and Deallocation
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 Management
SQL> create tablespace xxx ... SEGMENT SPACE MANAGEMENT AUTO;

Manual Segment Space Management (pctfree,pctused,freelist)