oracle 管理入门笔记

来源：互联网发布：货源软件编辑：程序博客网时间：2024/04/28 02:23

一、修改sys密码:SQL>alter user sys identified by "key";
    oracle 远程sqlplus 连接只能用 sys连接
    sysdba “sys”
    sysoper “public”

二、windows 客户端和secureCRT SQLPLUS乱码问题;如数据库oracle server用的是al32utf8
1、windows客户端:设置注册表 NLS_LANG=american_america.utf8/(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)
2. linux端 :vi .bash_profile添加 export NLS_LANG=american_america.utf8

说明;在客户端和服务器端都为utf-8时，假如你正确存入了汉字信息到数据库服务器上，一般来说汉字的UTF8的编码为三个byte。然后你的客户端也是utf-8，有2个byte. 所以你的AMERICAN_AMERICA.ZHS16GBK 只能选择ZHS16GBK，永远不要选择为UTF8,只有你想执行数据库的字符集转换的时候，你在exp和imp的时候，为了exp数据的字符集变化，你可以临时让客户端和数据库段一致。但是几乎很少有从utf8这种unicode转换到非

三、同一个计算机上的两个进程
   ipc inter process communication
   local loop back
   客户机、服务器
   tcp、ip

四、mandatory background process
   1、ora_dbw0-n_ak47 database buffer cache->data files (n<=20)
   2、ora_lgwr_ak47   redo log buffer ->redo log files (before dbwn write)
   3、ora_smon_ak47   instance recovery for:uncammitted transactions/cola trash
   4、ora_pmon_ak47 clean up after failed processes by for:restarting dead dippatcher
   5、ora_ckpt_ak47   update control file
   6、ora_arc0-n_ak47 archived redo log files

五、logicalstructure
   one server -1 database
   tablespaces segments extents collection blocks smallest
   1 tablespaces -n datafile

六、1、Oracle database 启动顺序
     spfilesid.ora
     default spfile ->spfile.ora
     initsid.ora PFILE
     spfilesid.ora SPFILE

2、startup pfile=$oracle_home/dbs/spfile.ora
     spfile 的查看方式;strings spfile.ora

七、oracle 数据库的状态
   shutdown --> nomount --> mount --> open
   example: alter database open;
    oracle启动;
   startup [force] 强制启动
   startup restrict

   受限模式;只有少数特权用户可以连接
   启动的时候;startup restrict

   启动之后在open状态下; 如果已有非特权的用户连接也可以断掉连接;
   alter system enable restricted session;
取消受限模式;
alter system disable restricted session;

八、在sqlplus下创建新用户和授权
create user test identified by test;
grant connect,resource to test;
CONNECT角色: --是授予最终用户的典型权利
ALTER SESSION --修改会话
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立数据库链接
CREATE SEQUENCE --建立序列
CREATE SESSION --建立会话
CREATE SYNONYM --建立同义词
CREATE VIEW --建立视图
RESOURCE角色: --是授予开发人员的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立过程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立触发器
CREATE TYPE --建立类型

   另外补充:
   1、grant update(sname) on student to tech1 with admin option;
   这个是给用户tech1以update表student列sname的权限，并使tech1可以把这个权限授予别人
   grant insert(sname,sid) on student to tech1;
2、grant alter on test_tab to user1; --给用户user1修改表test_tab的权限
   grant alter any table to user1; --给用户user1修改任意表的权限
/*
   Grant/Revoke system privileges
   grant select any dictionary to db_user1 with admin option;
   grant select any table to db_user1;
   grant unlimited tablespace to db_user1;
*/

查询用户连接的session
select sid,serial#,username from v$session;

examp: sid serial# username
        11    3      system
        15    6      sys

杀掉用户SESSION
   alter system kill seeion '15,6';s

   1、 startup nomount; 启动实例、读取spfile文件、启动oracle进程、分配SGA内存空间、不加载数据文件。一般用来创建数据库。

   2、 startup mount;打开控制文件。一般用来维护数据库。

   3、startup open; 打开数据文件，日志文件。正常使用模式。
alter database mount/open;
   数据库的只读模式,不影响用户的查询;
alter database open read only/read write;
切换到正常模式
shutdown immediate;
startup open;

九、数据库的关闭
shutdown abort/immediate/transaction/normal
shutdown normal 等到所有已连接上的用户退出
shutdown transaction 所有的事务都提交之后、等到所有已连接上的用户退出
shutdown abort 立即强制关闭没有提交的事务回滚掉

十、常用命令
查看用户信息:select username,account_status from dba_users;
解锁并修改密码;
alter user hr account unlock;
alter user hr identified by hr;
drop user username cascade;
显示参数
show parameter [dump]
修改参数;
内存session;alter session set sql_trace=true;
系统文件;alter system set sql_trace=true;
查看日志文件;tail -f xxxx.log
spfile 地址;$ORACLE_HOME/dbs/spfileak47.ora

十一、修改用户默认根目录:
   1、.bashrc和.bash_profile 原来系统默认的用户所在目录里复制到新更改的目录里
   2、修改/etc/passwd

十二、oracle 文件结构

oracle 程序文件;
/u01/oracle/dbs/: pilfeak47.ora spfileak47.ora init.ora
/u01/oracle/network/admin/:   listener.ora、tnsmanes.ora、sqlnet.ora

oracle 数据库数据文件;
/u01/oradata/sid/:   system.dbf 、control01.ctl 、redo01.log
/u01/admin/sid/: dump 、dpdump、 pfile
system administrator login oracle database must set :$oracle_home / $path / $oracle_sid

十三、oracle 登陆认证;

     本地1、systemchangedata集成认证只要是在dba组里面的系统管理员都可以登陆 oracle
       conn / @ak47 as sysdba
       2、sysdba组的oracle管理员可以登陆
       conn sys/oracle@ak47 as sysdba
     远程
       1、tcp 用oracle管理员认证
       2、tcps是用系统集成认证

十四、创建数据库1
     dbca 方式;修改创建数据库system administrator 的
      ORACLE_SID=NEW_SID
      ORACLE_BSE=/u01/
      ORACLE_HOME=/u01/oracle
      ORA_NL33
      PATH
      LD_LIBRARY_PATH
    删除数据库;
     1、dbca方式;自动
     2、手动方式;# cd /u01/oradata/    #rm -fR s_id/
              # cd /u01/admin/     #rm -fR s_id/
              # cd /u01/oracle/dbs #rm -f spfileS_ID.ora 、lkS_ID、orapwS_ID 、..
       /u01/oracle / 是 oracle 的程序文件
十五、创建数据库2
    manually;
    1、创建环境变量; ORACLE_BASE /ORACLE_HOME/ORACLE_SID/PATH/ORACLE_NLS33
    2、创建pfile:
             (1)manually; create #vi initSID.ora
             (2) auto create;strings spfileak47.ora -> initbbk.ora
             vi initbbk.ora :%s/ak47/bbk/g 把 ak47 替换为bbk
           技巧; vi 下的替换 :%s/*/.//g tips: .是特殊字符需要转义
            (3) 创建密码文件 orapwd file=orapwbbk password=oracle entries=10
    3、创建目录:/u01/oradata/bbk
            /u01/admin/bbk
     4、准备 createdbase.sql
     5、修改环境变量:1、chomd +X bbk.env   2、. ./bbk.env
     6、登录 oracle; conn / as sysdba
     7、创建spfile;create spfile from pfile;
     8、启动oracle; startup nomount
     9、执行;createdbase.sql @$ ~/dbs/createdbase.sql *脚本中间不要有空行*
十六、创建数据库3
     10、建立数据字典的文件是;
     $ORACLE_HOME/rdbms/admin/catalog.sql
     $ORACLE_HOME/rdbms/admin/catproc.sql
     $ORACLE_HOME/rdbms/admin/catexp.sql
     11、创建用户表空间

    create tablespace mytabs datafile '/u01/oradata/bbk/mytabs01.dbf' size 25m reuse autoextend on next 1024k maxsize unlimited extent management local uniform size 500K;
    createdatabase.sql
    CREATE DATABASE "bbk"
    MAXDATAFILES 500
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 32
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    CHARACTER SET "UTF8"
    NATIONAL CHARACTER SET AL16UTF16
    ARCHIVELOG
    DATAFILE '/u01/oradata/bbk/system01.dbf'
    SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    SYSAUX DATAFILE '/u01/oradata/bbk/sysaux01.dbf'
    SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL
    DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u01/oradata/bbk/tempts01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    UNDO TABLESPACE "UNDOTBS1"
    DATAFILE '/u01/oradata/bbk/undotbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
    LOGFILE
    GROUP 1 ( '/u01/oradata/bbk/redo01a.rdo','/u01/oradata/bbk/redo01b.rdo' )SIZE 100M,
    GROUP 2 ( '/u01/oradata/bbk/redo02a.rdo','/u01/oradata/bbk/redo02b.rdo' )SIZE 100M,
    GROUP 3 ( '/u01/oradata/bbk/redo03a.rdo','/u01/oradata/bbk/redo03b.rdo' )SIZE 100M
    USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle";

十九、dynamic performance table 动态性能表 gathered 当前数据库内存中的状态和控制文件
     主要的 dynamic performace table
      1、v$parameter
      2、v$database    dbid,name,created.....
      3、v$thread     thread#,status,instance.........
      4、v$instance     instance_name,instance_number,host_name
      5、v$datafile    list the name of the datafiles
      6、v$session    Session信息: username,status....client 信息;osuer，machine,terminal,process,
      示例;
      1.查找你的session信息 SELECT SID,OSUSER,USERNAME,MACHINE,PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID');
       2.当machine已知的情况下查找session SELECT SID,OSUSER,USERNAME,MACHINE,TERMINAL FROM $SESSION
      7、v$tablespace
      8、v$controlfile
     other dba_data_files,dba_free_space,dba_segments,dba_users

      cd $ORACLE_HOME/rdbms/
      cat*.sql    catalog and data dictionary information
      dbms*.sql database package specifications
      prvt*.plb   wrapped database package code
      utl*.sql   views and tables for database utilities

二十、control file
    SHUTDOWN ->NOMOUNT->MOUNT->OPEN
     nomount: instance started reading the initialization file from $ORACLE_HOME/dbs find first spfileSID.ora then initSID.ora,allocating SGA .starting the background processes,open alterSID.log file and trace files;

     mount: control file opened for this instance .associating a database with a previously started instace,then locating and open control.file from spfile.ora. obtain the names and status of the datafiles and redologfiles.

      open:all files opened as described by the control file for this instance. open the online data files and open the online redo log files.

      control file contents: databse name and identifier,time stamp of database creation,tablespace names,names and locationns of datafiles and online redo log files,current online redo log file sequence number,checkpoint information,begin and end of undo segments,redo log archive information,backup information.

     在spfile 上设置了三个控制文件，oracle对它们同时读写，最多可以弄八个，最好放到不同的物理硬盘上，以防丢失。
      for example;$ORACLE_HOME/oradata/u01/ctrl01.ctl
              $ORACLE_HOME/oradata/u02/ctrl02.ctl
               .......................................................................
      使用 pifle 直接修改 vi init_sid.ora
      使用spfile: alter system set control_files='$ORACLE_HOEM/u01/ctrl01.ctl','$ORACLE_HOME/u01/ctrl01.ctl'
      scope=spfile;

二十三、online redo log file

      至少两组online redo log file 放在不同的磁盘上
      log switch:一个redo 文件写满换到另一组写-->check point
      forcing a log switch; alter system switch logfile;
      forcing check point: fast_start_mttr_target=600 check point 在十分钟内必须触发一次 alter system checkpoint 立即执行 check poing 避免所有online redo file 写满了还没有触发 checkpoint的特殊情况。
      set redo file group;
        alter database add logfile group 4
           (
              '$ORACLE_BASE/oradata/ak47/rdolog4.rdo',
             '$ORACLE_BASE/oradata/ak47/rdologB4.rdo'
            ) size 10M;
       add logfile group member:
         alter database add logfile member
             '$ORACLE_BASE/oradata/ak47/rdo01b.rdo' to group 1,
             '$ORACLE_BASE/oradata/ak47/rdo01c.rdo' to group 1,
             '$ORACLE_BASE/oradata/ak47/rdo02b.rdo' to group 2,
             '$ORACLE_BASE/oradata/ak47/rdo02c.rdo' to group 2;

二十四、drop redofile
      删除 redo log file 成员每个组必须留一个;
      alter database drop logfile member '/u01/oradata/ak47/redo4a.rdo'; //spfile中的配置改变logfile没有删除。
       删除redo log file group;
      alter database drop logfile group 4;
      rm -f /u01/oradata/ak47/redo4a.rdo      //物理删除
       正在active 的不能删除;
       alter databaser drop logfile group;
       查看redologfile的使用状态: select * from v$log;
       inactive 已经发生过check point
       activer 使用过了但是没有checkpoing
       current 正在使用的日志文
二十五、redo log file configuration;
      将同组redologfile的不同成员放到不同的磁盘中可以提高磁盘的读写性能和提高系统的可靠性。

       v$log 中的 thread# 在单进程对单数据库时值为一，在RAC 环境中将对应不同的值。
      in the RAC system,each instance has to have its own redo log groups,each instance has its own redo thread.
      Archived Redo Log Files: ARCHIVELOG mod 1、recovery 2、backup
      by default,the database is set no archived mod.
         #select archiver from v$instance;
       #select log_mode from v$database;
        question : resize the redo log file? oracle not haver the resize command,therefore,add the new and drop the old.
        ADD A GROUP:      alter database add logfile group 4;
        DROP OLD GROUP: alter system switch logfile;
                     alter database add logfile group 4
                     ( '$ORACLE_BASE/oradata/ak47/rdolog4.rdo',
                        '$ORACLE_BASE/oradata/ak47/rdologB4.rdo'
                      ) size 10M;
         alter system 针对内存中当前启动的实例，alter database 针对数据库的修改

chapter8 managing tablespace & data files

二十六、tablespaces & datafiles

         types of tablespaces: permanent,undo,temp.
         system tablespace:created with the database,contains the data dictionary,contains the system undo segment.
         non-system tablespace:separate segments,ease spacea admintrator

          create tablespace:
         #create [BIGFILE/SMALLFILE] tablespace userdata datafile '/u01/oradata/SID/userdata01.dbf' size 100M;
       view the tablespace and the datafiel:
         #select * from v$tablespace;
           select file_name,tablespace_name from dba_data_files;

          space mgmt in tablespaces :locally managed tablespace,dictionary-managed tablespace(before 8.1.5).

二十七、locally managed tablespace
     user DBCA create database default is locally managed tablespace.
     change dictionary managed to local managed: DBMS_SPACE_ADMIN.TABALE SPACE_MIGRATE_TO_LOCAL('tablespace name'); in restricted mode.
       create undo tablespace :used to store undo segments,can not contain any other objects,extents are locally managed,can only use the datafile and extent.
     create undo tablespace datafile undo01 '/u01/oradata/sid/undo01.dbf' size 40m;
      create temporary tablespace:used for sort operations,can be shared by multiple users,cannot contain any permanent objects,locally managed extents recommended.
      create temporary tablespace temp TEMPFILE '/u01/oradata/temp01.dbf' size 20M extent management local uniform size 4M;

二十八、READ-ONLY TABLESPACE
        alter tablespace userdata read only;
      causes a checkpoint,data available only for read operations,objects can be dropped from tablespace;
      drop 一个表时候是可以的因为表示是system表空间里面的。
       exmple: 测试 alter tablespace wenxin readonly;(必须在对表空间的操作完成提交之后)
      创建实例表空间;create tablespace wenxin datafile '/u01/oradata/ak47/wenxin01.dbf' size 20m;
      创建用户并设定表空间;create user ak47 identified by 123456 default tablespace wenxin;
      赋予权限; grant connect,resource to ak47;
      使用ak47 用户登录; conn ak47/123456 创建表，插入数据，commit 一条，第二条不commit
      使用system用户登录; alter tablespace wenxin read only；当第二条数据commit之后执行成功。
      tablespace offline/online:
      alter tablespace wenxin offline [normal/temporary/immediate]; 可以在操作没 commit的情况下执行。
      alter tablespace wenxin online;

二十九、change storage settings
     dictionary managed: alter tablespace userdata minimum extent 2M;X
   change tablespace size three way:1、AUTOEXTEND 2、MANUALLY : alter database 3、ADD A DATA FILE: alter tablespace
       example :on the tablespace create
     create tablespace user_data datafile '/u01/oradata/user_data01.dbf' size 20M autoextend on next 10M maxsize 500M;
    alter database datafile'/u01/oradata/ak47/wenxin.dbf' autoextend on next 10m maxsize 100m;
     alter database datafile'/u01/oradata/ak47/wenxin.dbf' resize 100m;
     view the DBA_DATA_FILES to determine whether autoextent is enabled;
     view the tablespace used:
     SELECT a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes-b.bytes)/a.bytes)*100,2)percent_used
     FROM (SELECT tablespace_name,sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name)a,
     (SELECT tablespace_name,sum(bytes) bytes,max(bytes) largest FROM dba_free_space GROUP BY tablespace_name)b
     WHERE a.tablespace_name =b.tablespace_name ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC;

三十、CATION OF DATASPACE
    RESIZING DATA FILE:
     manually resizing date file:
      alter database datafile '/u01/oradata/ak47/wenxin_data01.ora' 200M;
     ADDING DATA FILES TO A TABLESPACE:
     alter tablespace wenxin_data
       add datafile '/u01/oradata/userdata03.dbf' size 200M;
   moving DATA FILES TO A TABLESPACE:
     1、sql>alter tablespace wenxin_data offline;
        linux>mv /u01/oradata/userdata01.dbf /u02/ak47/
        sql>alter tablespace wenxin_data rename datefile '/u01/oradata/ak47/wenxin_data01.dbf' to '/u02/ak47/wenxin_data01.dbf';

    2、sql>shutdown immediate
       linux>mv /u01/oradata/userdata01.dbf /u02/ak47/
       sql>startup mount
       sql>alter database rename datefile '/u01/oradata/ak47/wenxin_data01.dbf' to '/u02/ak47/wenxin_data01.dbf';
       sql>alter database open
    DROP TABLESPACE:
     cannot drop : system tablespace or has active segments
       drop tablespace wenxin_data including contents and datafiles;
    GET TABLESPACE INFORMATION
     TABLESPACE INFORMATION:
      --DBA_TABLESPACES
    --V$TABLESPACE
    DATA FILE INFORMATION:
    --DBA_DATA_FILES
    --V$DATAFILE
     TEMP FILE INFORMATION:
     --DBA_TEMP_FILES
     --V$TEMPFILE
                 chaper 9 storage structure & relationships

三十一、storage & relationship struture

database blocksize

create no-standard block size tablespace
create tablespace tabs_1 datafile '/u01/oradata/ak47/tabs_01.dbf' size 10M blocksize 16284k;