constraints operation

• 1.报告约束异常
• 如果校验约束时存在异常,则返回一个错误,且完整性约束仍保持未被校验状态.当约束存在异常时,一个语句就不能正确执行,则此语句被回滚.如果存在异常,必须要更新或删除了约束的所有异常后,才可以校验约束.
• 但是在使用ALTER TABLE语句不能确定哪一行违反约束,为了确定哪一行,在发布ENABLE子句中带有EXCEPTION选项的ALTER TABLE语句.
• EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中.在启用约束前,硬创建一个合适的异常报告表,用来接收ENABLE子句的EXCEPTION选项信息,可以直接执行
• '?\rdbms\admin\utlexcpt.sql'
• 或
• '?\rdbms\admin\utlexcpt1.sql'
• 脚本来进行创建.注意:这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型.
• 　　
• 使用的语法如下:
• ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
• 或者
• alter table team disable validate constraint id_num EXCEPTIONS INTO EXCEPTIONS;
• --这个方法不创建索引
  　　
• 如果存在异常,则直接查询 SELECT * FROM EXCEPTIONS;
• 即可.如果需要更加详细的信息,则可以使用ROWID与原表的ROWID进行关联,这样就可以查出原始表中当前行的所有信息,并进行修改.
•  
• 如何利用EXCEPTIONS来处理实践中的一些问题:
• 　　(1)在已有的constraint上加载数据
  　　先disable,再加载数据,然后enable and EXCEPTIONS INTO EXCEPTIONS,
•     根据EXCEPTIONS表的纪录delete,最后再enable.
  　　(2)新建constraint,原有的数据违反了这个约束
  　　先create disable,再enable and EXCEPTIONS INTO EXCEPTIONS
  　　以下是一个新建外键的例子:
  　　
• alter table team add constraint id_num foreign key(id) references   games(scores) INITIALLY disable;
  　　alter table team enable constraint id_num EXCEPTIONS INTO EXCEPTIONS;
  　　select * from EXCEPTIONS;
• 　　2.关于contraint的一些常见操作
  　　定义约束:
  　　alter table t add constraint ch_100 unique(id) disable ;
  　　alter table t add constraint ch_100 unique(id);
  　　alter table t add constraint ch_100 unique(id) deferred deferrable novalidate;
• CREATE TABLE dept_20　(employee_id NUMBER(4),　commission_pct NUMBER(7,2), department_id, CONSTRAINT fk_deptno
  　　FOREIGN KEY (department_id)　REFERENCES departments(department_id)　on delete/set null cascade);
• ALTER TABLE dept_20　
• ADD CONSTRAINT fk_empid_hiredate　
• FOREIGN KEY (employee_id, hire_date) REFERENCES hr.job_history(employee_id, start_date)　
• EXCEPTIONS INTO EXCEPTIONS;
•  
• CREATE TABLE divisions 
• (
•    div_no NUMBER CONSTRAINT check_divno　
•        CHECK (div_no BETWEEN 10 AND 99)  DISABLE , 
•    div_name VARCHAR2(9) CONSTRAINT check_divname　
•        CHECK (div_name = UPPER(div_name　DISABLE,　
•    office VARCHAR2(10) CONSTRAINT check_office　
•        CHECK (office IN ('DALLAS','BOSTON',　'PARIS','TOKYO'))　DISABLE
• );
• 　　注:在使用外键参考了PRIMARY或UNIQUE键时,不能停用或删除被参考约束
  　　延迟校验的启用停用:
  　　SET CONSTRAINT(s) unq_num/all immediate;
  　　SET CONSTRAINT(s) unq_num/all deferred;
  　　停用:
  　　ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
  　　ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
  　　DISABLE UNIQUE (dname, loc) KEEP INDEX;
  　　启用非校验:
  　　ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
  　　ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
  　　ENABLE NOVALIDATE UNIQUE (dname, loc);
  　　启用校验:
  　　ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;
  　　ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
  　　若要停用/删除相关的FOREIGN KEY约束,则:
  　　ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
  　　删除:
  　　ALTER TABLE dept DROP UNIQUE (dname, loc);
  　　ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,
  　　DROP CONSTRAINT dept_fkey;

rman commander

• 一、list常用命令总结备忘
  
  list命令列出控制文件,rman恢复目录中备份信息， 是我们对所有可见的数据库备份文件的一个最直观的了解的方法
  
  list incarnation;
• list backup summary;
  list backup of database summary;
  list backup of tablespace summary;
  list backup of datafile n,n summary;
  list archivelog all summary;
  
  list backup by file;
  list backup;
  list expired backup;
  list copy;
  
  list backup of spfile;
  list backup of controlfile;
  list backup datafile n,n,n;
  list backup tablespace tablespace_name;
  list backup of archivelog all;
  list backup of archivelog from scn ...;
  list backup of archivelog until scn ...;
  list backup of archivelog from sequence ..;
  list backup of archivelog until time 'sysdate-10';
  list backup of archivelog {all, from, high, like, logseq, low, scn, sequence, time, until};
  
  1. List 当前RMAN所备份的数据库：
  RMAN>list incarnation;
  汇总查询：--如果备份文件多的话多用这两个list命令可以对备份文件有个总体了解。
  1.1.list backup summary; --概述可用的备份
  B 表示backup
  A 表示Archivelog、 F 表示full backup、 0,1,2 表示incremental level备份
  A 表示可用AVALIABLE、 X 表示EXPIRED
  
  这个命令可以派生出很多类似命令，例如
  list backup of database summary
  list backup of archivelog all summary
  list backup of tablespace users summary;
  list backup of datafile n,n,n summary
  
  这些命令可以让我们对已有的备份文件有一个整体，直观的了解。
  
  1.2.list backup by file;--按照文件类型分别列出
  分别为：数据文件列表、归档日志列表、控制文件列表、SPFILE列表
  
  1.3.list backup;
  这个命令列出已有备份集的详细信息。
  
  1.4.list expired backup;
  列出过期的备份文件
  
  1.5.list copy;
  列出copy文件
  list copy of database;
  list copy of controlfile;
  list copy of tablespace users;
  list copy of datafile n,n,n;
  list copy of archivelog all;
  list copy of archivelog from scn 10000;
  list copy of archivelog until sequence 12;
  
  2. List 相关文件的信息
  list backup of {archivelog, controlfile, database, datafile, spfile, tablespace};
  list backup of database; --full,incremental,tablespace,datafile
  
  2.1服务器参数文件:
  list backup of spfile;
  
  2.2 控制文件：
  list backup of controlfile;
  
  2.3 数据文件：
  list backup of datafle n,n,n,n;
  
  2.4表空间：
  list backup of tablespace tablespace_name;--表空间对应的backup
  
  2.5归档日志：list backup of archivelog {all, from, high, like, logseq, low, scn, sequence, time, until};
  list backup of archivelog all;
  list backup of archivelog until time 'sysdate-1';
  list backup of archivelog from sequence 10;
  list backup of archivelog until sequence 10;
  list backup of archivelog from scn 10000;
  list backup of archivelog until scn 200000;
  
  list archivelog from scn 1000;
  list archivelog until scn 2000;
  list archivelog from sequence 10;
  list archivelog until sequence 12;
• 二、report常用命令总结备忘
• report用于判断数据库当前可恢复状态、以及数据库已有备份的信息。
  最常使用的是report obsolete; report schema;
  report {device, need, obsolete, schema, unrecoverable}
  
  report schema;
  report obsolete;
  report unrecoverable;
  report need backup;
  report need backup days=3; --报告最近3天内没有备份的文件
  report need backup redundancy=3; --报告冗余次数小于3的数据文件。
  report need backup recovery window of 2 days;
• 2.1.report schema;
  报告数据库模式
  
  22.report obsolete;
  报告已丢弃的备份集(配置了保留策略)。
  
  2.3.report unrecoverable;
  报告当前数据库中不可恢复的数据文件(即没有这个数据文件的备份、或者该数据文件的备份已经过期)
  
  2.4.report need backup;
  报告需要备份的数据文件(根据条件不同)
  report need backup days=3;
  --最近三天没有备份的数据文件(如果出问题的话，这些数据文件将需要最近3天的归档日志才能恢复)
  report need backup incremental=3;
  --需要多少个增量备份文件才能恢复的数据文件。(如果出问题，这些数据文件将需要3个增量备份才能恢复)
  report need backup redundancy=3;
  --报告出冗余次数小于3的数据文件
  --例如数据文件中包含2个数据文件system01.dbf和users01.dbf.
  --在3次或都3次以上备份中都包含system01.dbf这个数据文件，而users01.dbf则小于3次
  --那么，报告出来的数据文件就是users01.dbf
  --即，报告出数据库中冗余次数小于 n 的数据文件
  report need backup recovery window of 2 days;
  --报告出恢复需要2天归档日志的数据文件
  三、backup常用命令总结备忘
• 1.设置备份标记
  backup database tag='full_bak1';
  注：每个标记必须唯一，相同的标记可以用于多个备份只还原最新的备份。
  
  2.设置备份集大小(一次备份的所有结果为一个备份集，要注意备份集大小)
  backup database maxsetsize=100m tag='datafile1';
  注：maxsetsize限定备份集的大小。所以必须大于数据库总数据文件的大小，否则会报错。
  RMAN-06183: datafile or datafile copy larger than MAXSETSIZE: file# 1 /data/oradata/system01.dbf
  
  3.设置备份片大小(磁带或文件系统限制)
  run {
  allocate channel c1 type diskmaxpicecsize 100mformat '/data/backup/full_0_%U_%T';
  backup database tag='full_0';
  release channel c1;
  }
  可以在allocate子句中设定每个备份片的大小，以达到磁带或系统限制。
  也可以在configure中设置备份片大小。Configure channel device type disk maxpiecesize 100 m;
  configure channel device type disk clear;
  
  4.备份集的保存策略backup database keep forever; --永久保留备份文件
  backup database keep until time='sysdate+30'; --保存备份30天
  
  
  5.重写configure exclude命令
  backup databas noexclude keep forever tag='test backup';
  
  6.检查数据库错误
  backup validate database;
  使用RMAN来扫描数据库的物理/逻辑错误，并不执行实际备份。
  
  7.跳过脱机，不可存取或只读文件backup database skip readonly;
  backup database skip offline;
  backup database skip inaccessible;
  backup database ship readonly skip offline ship inaccessible;
  
  8.强制备份
  backup database force;
  
  9.基于上次备份时间备份数据文件
  1>只备份添加的新数据文件
  backup database not backed up;
  2>备份"在限定时间周期内"没有被备份的数据文件
  backup database not backed up since time='sysdate-2';
  
  10.备份操作期间检查逻辑错误backup check logical database;
  backup validate check logical database;
  11.生成备份副本
  backup database copies=2;
  12.备份控制文件
  backup database device type disk includ current controlfile;
• 四、configure常用命令总结备忘
• 4.1 显示当前的配置信息
  4.1.1 RMAN>show all;
  
  RMAN 配置参数为:
  
  CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
  CONFIGURE BACKUP OPTIMIZATION OFF; # default
  CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
  CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
  CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  
  CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
  
  NCFDBA.ORA'; # default
  
  4.1.2 查询RMAN设置中非默认值:
  SQL>select name,value from v$rman_configuration;
  
  4.2. 常用的configure选项
  
  4.2.1 保存策略 (retention policy)configure retention policy to recovery window of 7 days;
  configure retention policy to redundancy 5;
  configure retention policy clear;
  CONFIGURE RETENTION POLICY TO NONE;
• 第一种recover window是保持所有足够的备份，可以将数据库系统恢复到最近七天内的任意时刻。任何超过最近七天的数据库备份将被标记为obsolete。
  第二种redundancy 是为了保持可以恢复的最新的5份数据库备份，任何超过最新5份的备份都将被标记为redundancy。它的默认值是1份。
  
  第三四：NONE 可以把使备份保持策略失效，Clear 将恢复默认的保持策略
  
  一般最安全的方法是采用第二种保持策略。
  
  4.2.2 备份优化 backup optimizationconfigure backup optimization on;
  configure backup optimization off;
  configure backup optimization clear;
  默认值为关闭，如果打开，rman将对备份的数据文件及归档等文件进行一种优化的算法。
  
  4.2.3 默认设备 default device typeconfigure default device type to disk;
  configure default device type to sbt;
  configure default device type clear;
  是指定所有I/O操作的设备类型是硬盘或者磁带，默认值是硬盘
  磁带的设置是CONFIGURE DEFAULT DEVICE TYPE TO SBT;
  
  4.3.4 控制文件 controlfileconfigure controlfile autobackup on;
  configure controlfile autobackup format for device type disk to '/cfs01/backup/conf/conf_%F';
  configure controlfile autobackup clear;
  configrue controlfile autobackup format for device type disk clear;
  configrue snapshot controlfile name to '/cfs01/backup/snapcf/scontrofile.snp';
  
  --是配置控制文件的快照文件的存放路径和文件名，这个快照文件是在备份期间产生的，用于控制文件的读一致性。
  configrue snapshot controlfile name clear;
  
  强制数据库在备份文件或者执行改变数据库结构的命令之后将控制文件自动备份，默认值为关闭。这样可以避免控制文件和catalog丢失后，控制文件仍然可以恢复。
  
  4.3.5 并行数(通道数)device type disk|stb pallelism n;
  configure device type disk|stb parallelism 2;
  configure device type disk|stb clear; --用于清除上面的信道配置
  configure channel device type disk format 'e/:rmanback_%U';
  configure channel device type disk maxpiecesize 100m
  configure channel device type disk rate 1200K
  configure channel 1 device type disk format 'e/:rmanback_%U';
  configure channel 2 device type disk format 'e/:rmanback_%U';
  configure channel 1 device type disk maxpiecesize 100m
  
  配置数据库设备类型的并行度。
  
  4.3.6 生成备份副本 datafile|archivelog backup copiesconfigure datafile backup copies for device type disk|stb to 3;
  configure archivelog backup copies for device type disk|stb to 3;
  
  --是设置数据库的归档日志的存放设备类型
  configure datafile|archivelog backup copies for device type disk|stb clear
  BACKUP DEVICE TYPE DISK DATABASE
  FORMAT '/disk1/backup/%U', '/disk2/backup/%U', '/disk3/backup/%U';
  
  是配置数据库的每次备份的copy数量，oracle的每一次备份都可以有多份完全相同的拷贝。
• 4.3.7 排除选项 exclude
• configure exclude for tablespace 'users';
  configrue exclude clear;
  
  此命令用于将指定的表空间不备份到备份集中， 此命令对只读表空间是非常有用的。
  
  
  4.3.8 备份集大小 maxsetsizeconfigure maxsetsize to 1G|1000M|1000000K|unlimited;
  configure maxsetsize clear;
  4.3.9 其它选项 auxiliary
  CONFIGURE AUXNAME FOR DATAFILE 1 TO '/oracle/auxfiles/aux_1.f';
  CONFIGURE AUXNAME FOR DATAFILE 2 TO '/oracle/auxfiles/aux_2.f';
  CONFIGURE AUXNAME FOR DATAFILE 3 TO '/oracle/auxfiles/aux_3.f';
  CONFIGURE AUXNAME FOR DATAFILE 4 TO '/oracle/auxfiles/aux_4.f';
  -
  CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR;
  CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;
  CONFIGURE AUXNAME FOR DATAFILE 3 CLEAR;
  CONFIGURE AUXNAME FOR DATAFILE 4 CLEAR;
• Rman的format格式中的%
• %c 备份片的拷贝数
  %d 数据库名称
  %D 位于该月中的第几天 (DD)
  %M 位于该年中的第几月 (MM)
  %F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为
  日期，QQ是一个1-256的序列
  %n 数据库名称，向右填补到最大八个字符
  %u 一个八个字符的名称代表备份集与创建时间
  %p 该备份集中的备份片号，从1开始到创建的文件数
  %U 一个唯一的文件名，代表%u_%p_%c
  %s 备份集的号
  %t 备份集时间戳
  %T 年月日格式(YYYYMMDD)

误删除数据文件，没关闭数据库时，的恢复方法

若有意外被删除了整个目录中的数据文件，操作系统级别的删除，然而幸运的是这个数据库没有崩溃，仍然处于open状态的时候，还可以恢复所有数据文件。
在Linux下大致重新演示一下恢复的过程，恢复的步骤与数据库版本没有太大关系，与操作系统的不同会有所不同。
1. 在数据库open的时候，直接删除users表空间中的数据文件。

SQL> select name from v$datafile; 
NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/ORCL/datafile/o1_mf_system_555wqbnk_.dbf
/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_555wqxgl_.dbf
/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_555wr5p6_.dbf
/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf
 
SQL> host rm /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf

2. 尝试在users表空间中创建表，开始报错。

SQL> create table t tablespace users as select * from dual;
create table t tablespace users as select * from dual
                                                 *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

在告警日志中，同样也可以看到类似信息。

Mon Dec 19 21:48:17 CST 2011
Errors in file /app/oracle/admin/orcl/bdump/orcl_m000_3897.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

3. 检查dbwr的进程PID

$ ps -ef|grep dbw0|grep -v grep
oracle    2879     1  0 21:38 ?        00:00:00 ora_dbw0_orcl

4. dbwr会打开所有数据文件的句柄。在proc目录中可以查到，目录名是进程PID，fd表示文件描述符。

$ cd /proc/2879/fd
$ ls -l
total 0
lr-x------ 1 oracle dba 64 Dec 19 21:50 0 -> /dev/null
lr-x------ 1 oracle dba 64 Dec 19 21:50 1 -> /dev/null
lr-x------ 1 oracle dba 64 Dec 19 21:50 10 -> /dev/zero
lr-x------ 1 oracle dba 64 Dec 19 21:50 11 -> /dev/zero
lr-x------ 1 oracle dba 64 Dec 19 21:50 12 -> /app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle dba 64 Dec 19 21:50 13 -> /app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
lrwx------ 1 oracle dba 64 Dec 19 21:50 14 -> /app/oracle/product/10.2.0/db_1/dbs/lkORCL
lrwx------ 1 oracle dba 64 Dec 19 21:50 15 -> /app/oracle/oradata/ORCL/controlfile/o1_mf_555wq3ng_.ctl
lrwx------ 1 oracle dba 64 Dec 19 21:50 16 -> /app/oracle/oradata/ORCL/datafile/o1_mf_system_555wqbnk_.dbf
lrwx------ 1 oracle dba 64 Dec 19 21:50 17 -> /app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_555wqxgl_.dbf
lrwx------ 1 oracle dba 64 Dec 19 21:50 18 -> /app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_555wr5p6_.dbf
lrwx------ 1 oracle dba 64 Dec 19 21:50 19 -> /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf (deleted)
lr-x------ 1 oracle dba 64 Dec 19 21:50 2 -> /dev/null
lrwx------ 1 oracle dba 64 Dec 19 21:50 20 -> /app/oracle/oradata/ORCL/datafile/o1_mf_temp_555wrbnz_.tmp
lr-x------ 1 oracle dba 64 Dec 19 21:50 21 -> /app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle dba 64 Dec 19 21:50 3 -> /dev/null
lr-x------ 1 oracle dba 64 Dec 19 21:50 4 -> /dev/null
l-wx------ 1 oracle dba 64 Dec 19 21:50 5 -> /app/oracle/admin/orcl/udump/orcl_ora_2871.trc
l-wx------ 1 oracle dba 64 Dec 19 21:50 6 -> /app/oracle/admin/orcl/bdump/alert_orcl.log
lrwx------ 1 oracle dba 64 Dec 19 21:50 7 -> /app/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)
l-wx------ 1 oracle dba 64 Dec 19 21:50 8 -> /app/oracle/admin/orcl/bdump/alert_orcl.log
lrwx------ 1 oracle dba 64 Dec 19 21:50 9 -> /app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat

注意其中"/app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf (deleted)"字样，表示该文件已经被删除，如果是Solaris操作系统，ls命令不会有如此清晰的显示，为了在Solaris系统中确认哪个句 柄对应哪个文件，则需要使用lsof程序。
5. 直接cp该句柄文件名回原位置。

cp 19 /app/oracle/oradata/ORCL/datafile/o1_mf_users_555wrj4o_.dbf

6. 进行数据文件recover

SQL> alter database datafile 4 offline; 
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.

完成数据文件恢复。
恢复的原理是，在Linux操作系统中，如果文件从操作系统级别被rm掉，之前打开该文件的进程仍然持有相应的文件句柄，所指向的文件仍然可以读 写，并且该文件的文件描述符可以从/proc目录中获得。但是要注意的是，此时如果关闭数据库，则此句柄会消失，那么除了扫描磁盘进行文件恢复之外就没有 其它方法了，因此在数据库出现问题的时候，如果不确认情况的复杂程度，千万不要随便关闭数据库。重启数据库往往是没有意义的，甚至是致命的。

oracle 命中率

• 在数据库启动2小时后，可以通过以下SQL来测试数据库性能
• 1. 缓冲区命中率:
• 缓冲区命中率表示在不需要进行磁盘访问的情况下在内存结构中找到常用数据块的频率
• select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
  from v$sysstat;
• 大于98%为最佳
• 2.数据字典缓存命中率：
• 数据字典缓存命中率显示了对数据字典和其他对象的内存读操作所占的百分比。
• select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;
• 大于98%为最佳
  3.库缓存命中率：
• 库缓存命中率显示了对实际语句和PL/SQL对象的内存读操作所占的百分比。注意，很高的命中率并不总是一件好事。
• select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;
• 大于98%为最佳
• 4.PGA内存排序命中率
• 自动PGA内存管理简 化了分配PGA内存的方法。Oracle动态调整工作区PGA内存的大小(以SGA内存大小的20%为基础)。在自动PGA内存管理模式下运行时，所有会 话的工作区大小都是自动的。实例中活动工作区可用的PGA内存总量自动由SORT_AREA_SIZE或PGA _ AGGREGATE_ TARGET(首选)初始化参数导出。PGA内存排序率的值应该大于98%。依据初始化参数PGA_AGGREGATE_TARGET(或者用于向后兼容 的SORT _AREA _ SIZE)的值，用户排序可能在内存或者在指定的临时表空间中的磁盘上完成，如果这个初始化参数不是太高的话。
• select a.value "Disk Sorts", b.value "Memory Sorts",round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)"Pct Memory Sorts" from v$sysstat a, v$sysstat b where a.name = 'sorts (disk)'and b.name = 'sorts (memory)';
• 5. 空闲的数据缓冲区的比例
• 从 您首次启动Oracle数据库的那一天开始，用户们的查询就开始使用内存。空闲的记录数除以X$BH表中的记录总数(即所分配的数据块缓冲区的总数)就得 到这个百分比。同时请注意，您必须以SYS的权限来运行该查询。此外，拥有众多的空闲缓冲区并不一定是就最佳环境。5%-10% 为最佳。当空闲比例高于25%时，数据缓冲区设置得太大了，可能会浪费资源。
• select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK STATUS",count(*) from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);
• 6. 最浪费内存的前10个语句占所有语句的比例
  在没有调整的情况下，大多数系统中10个最常使用的SQL语句的访问量占了整个系统中内存读操作的50%以上。本节测量了最影响性能的代码对整个系统所造成危害的严重性，以百分比表示。
• select sum(pct_bufgets) "Percent" from 
• (select rank() over ( order by buffer_gets desc ) as rank_bufgets ,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sqlarea ) where rank_bufgets < 11;
• 小于5%为最佳。
• 7.调整滥用磁盘读操作的主要语句
  我发现在没有作调整的情况下，在绝大多数的系统中，访问量占前25位的语句的磁盘读操作将占用整个系统所有磁盘和/或内存读操作的75%。
• select disk_reads, substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc;
  8.表和与它们相关联的索引应当放置在不同的物理磁盘上，以便减少文件I/O。

DataGuard常见维护命令

• Oracle DataGuard 常用维护命令参考手册

• Oracle DataGuard Standby database ID mismatch错误
• Oracle Dataguard备库失败与主库响应测试
• DataGuard ORA-01111文件创建失败问题解决
• DataGuard的STANDBY_ARCHIVE_DEST参数
• HDS Truecopy实现原理及项目的选择
• Start Standby Database

• startup nomount
  alter database mount standby database;
  alter database recover managed standby database disconnect;

• 
  Disable/Enable archive log destinations

• alter system set log_archive_dest_state_2 = 'defer';
  alter system set log_archive_dest_state_2 = 'enable';

• To remove a delay from a standby

• alter database recover managed standby database cancel;
  
  alter database recover managed standby database nodelay disconnect;

• Stop and Start of Logical standby apply

• alter database stop logical standby apply;
  alter database start logical standby apply;

• Physical Standby switchover:
  In Primary Database：

• ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
  SHUTDOWN IMMEDIATE;
  STARTUP NOMOUNT;
  ALTER DATABASE MOUNT STANDBY DATABASE;

• In standby Database:

• ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
  SHUTDOWN IMMEDIATE;
  STARTUP;

• In Primary Database:

• ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

• If the primary Database is down,we can use fllowing step to active standby database:

• Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
  Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
  SHUTDOWN IMMEDIATE;
  STARTUP;

• 
  Register missing archive log file
  Find archive log gap by query:

• SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

• register using:

• ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

没分区的大表到分区表的转换

1.创建一个与原来大表一样结构的表
-- Create table
create table text
(
y0 NUMBER(10),
y1 NUMBER(10),
y2 NUMBER(10),
y3 NUMBER(10),
y4 NUMBER(10),
y5 NUMBER(10),
y6 NUMBER(10),
y7 NUMBER(10)
) tablespace text
partition by range (start_time) interval(numtodsinterval(1,'day'))
(
   partition p1 values less than (to_date('2000-05-07 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace text ,
   partition p2 values less than (to_date('2012-05-10 00:00:00','YYYY-MM-DD HH24:MI:SS')) tablespace text
);
/
-- 交换数据
alter table text exchange partition p2 with table text1 ;

-- 改原表名 为text1
alter table text RENAME TO text1 ;

-- 改新表text1 为 text
alter table text1 rename to text ;
-- drop table text PURGE ;

把大表 改成 自动分区的 语句 ：
ALTER TABLE text1 SET INTERVAL(numtodsinterval(1,'DAY'));

一些对分区的操作命令：
alter table text1 add partition p2
values less than (to_date('2012-05-10 00:00:00','YYYY-MM-DD HH24:MI:SS'))    
tablespace text ;
ALTER TABLE text1 TRUNCATE PARTITION SYS_P177143 ;
alter table text1 drop partition p2 ;
alter table text1 truncate partition p2 ;

interval分区和range分区的互相转换

• INTERVAL分区其实是一种比较特殊的范围分区，因此可以很方便的将RANGE分区表转化为 INTERVAL分区表，同样可以将INTERVAL分区表转化为RANGE分区表。

login.sql profile

login.sql内容：
/******************************************************/
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
select lower(user)||'@'||decode(global_name,'ORACLE8.WORLD','8.0','ORA8I.WORLD','8i',global_name) global_name from global_name;
set sqlprompt '&gname>'
set termout on
/*******************************************************/
部署位置：$ORACLE_HOME/sqlplus/admin/ --在linux环境下。

oracle sqlerrm . sqlcode

1.oracle sqlcode函数

sqlcode函数用来返回pl/sql执行内部异常代码。
语法：sqlcode
oracle sqlerrm函数
sqlerrm函数返回指定错误代码的错误信息。
语法：SQLERRM [(error_number)]
2.sqlerrm如何使用？
sqlcode和sqlerrm是不能直接在sql语句中使用，必须先将其赋给变量后，才能在sql语句中使用，如下：

dw@dw>
declare
  v_sqlcode number;
  v_sqlcode1 number;
  v_sqlerrm varchar2(510);
 begin
  v_sqlcode :=sqlcode;
  select v_sqlcode into v_sqlcode1 from dual;
 end;
  /

PL/SQL 过程已成功完成。

sqlerrm用得最多的是在pl/sql编程中来捕获异常的详细信息，特别是在when others 中使用非有用。
如下：
view plaincopy to clipboardprint?

set serveroutput on
declare
   v_count pls_integer;
begin
   begin
   select 2 into v_count from dual where 1>2;
   exception
   when others then
   dbms_output.put_line(sqlerrm);
end;
begin
select level into v_count
from dual
connect by level < 3;
end;
end;
/

dw@dw>set serveroutput on
dw@dw>declare
  v_count pls_integer;
 begin
  begin
  select 2 into v_count
  from dual where 1>2;
  exception
  when others then
  dbms_output.put_line(sqlerrm);
  end;
begin
  select level into v_count from dual
  connect by level < 3;
  end;
 end;
  /
ORA-01403: 未找到任何数据
declare
*
第 1 行出现错误:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 line 12

3.sqlerrm的最大长度是多少？
其实这个问题很好解答，自定义一个异常，然后用sqlerrm去捕获即可。
view plaincopy to clipboardprint?

set serveroutput on
begin
  raise_application_error(-20001,
  'sqlerrm的最大长度是多少' || lpad('我', 10000, '我'));
  exception
  when others then
  dbms_output.put_line(lengthb(sqlerrm));
end;
/
dw@dw>
begin
  raise_application_error(-20001,
  'sqlerrm的最大长度是多少' || lpad('我', 10000, '我'));
  exception
  when others then
  dbms_output.put_line(lengthb(sqlerrm));
 end;
  /

PL/SQL 过程已成功完成。
dw@dw>
从输出结果来看，oracle sqlerrm的最在长度为510个字节。
4.oracle ora-错误信息手册

从oracle的参考文档中，可以发现自定义异常从－20000开始，也就是说，
－20000前的数据都是oracle内部使用的，
可以通过下面这段匿名块查出oracle所有ora-错误信息。
view plaincopy to clipboardprint?

set serveroutput on
exec dbms_output.enable(10000000);
begin
   for i in 0..20000 loop
   dbms_output.put_line(sqlerrm(-i));
   end loop;
end;
/

子分区和子分区模板

复合分区可以是：范围－列表，范围－HASH 

11g加：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST

1.子分区创建(范围－列表)

CREATE TABLE mylong

(MYDATE DATE,ATYPE NUMBER,ABC NUMBER)

PARTITION BY RANGE(MYDATE)

SUBPARTITION BY list(ATYPE)

SUBPARTITION TEMPLATE

(SUBPARTITION "A" VALUES(1) TABLESPACE users,

SUBPARTITION "B" VALUES(3) TABLESPACE users)

(

PARTITION MY_20111117 VALUES less than (TO_DATE('20111118','yyyymmdd')),

PARTITION MY_20111118 VALUES less than (TO_DATE('20111119','yyyymmdd')),

PARTITION MY_20111119 VALUES less than (TO_DATE('20111120','yyyymmdd'))

);

2.子分区查询

select * from MYLONG SUBPARTITION(MY_20111117_A);

3.子分区信息

select table_name,partition_name,SUBPARTITION_NAME from user_tab_subpartitions;

4.11g下可以(范围-范围)

CREATE TABLE mylong( MYDATE DATE,ATYPE NUMBER,ABC NUMBER)

PARTITION BY RANGE(MYDATE)

SUBPARTITION BY RANGE(ATYPE)

SUBPARTITION TEMPLATE

(SUBPARTITION "2" VALUES less than(2) TABLESPACE users,

SUBPARTITION "max" VALUES less than(MAXVALUE) TABLESPACE users

)

(

PARTITION MY_20111117 VALUES less than (TO_DATE('20111118','yyyymmdd')),

PARTITION MY_20111118 VALUES less than (TO_DATE('20111119','yyyymmdd')),

PARTITION MY_20111119 VALUES less than (TO_DATE('20111120','yyyymmdd'))

);

add partitioned table`s subpartition

在oracle 11g之前，复合分区支持range-hash,range-list
对这两种复合分区,均可以增加父分区和子分区
range-hash
add partition --增加父分区
modify partition p_父 add subpartition --增加子分区
range-list
add partition --增加父分区
modify partition p_父 add subpartiton values(val_list) --增加子分区


----alter table test_part split partition all_data AT (to_date('20070101','YYYYMMDD')) into ( partition before_2007 , partition after_2007 ) ;

linux delete your oracle software

# userdel -r oracle
# groupdel dba

# touch /opt/rmoracle.sh
# vi /opt/rmoracle.sh
rm -rf /opt/oracle
rm -rf /opt/ORCLfmap
rm -f /etc/oraInst.loc
rm -f /etc/oratab
rm -f /usr/local/bin/oraenv
rm -f /usr/local/bin/coraenv
rm -f /usr/local/bin/dbhome

# chmod 755 /opt/rmoracle.sh
# /opt/rmoracle.sh

move index.table.lob

• Move表、索引、LOB/Long
• move一个表到另外一个表空间时,索引不会跟着一起move，而且会失效。（LOB类型例外）
• 表move，我们分为：普通表move ；分区表move ；LONG,LOB大字段类型
• move来进行测试和说明。
• 索引的move，我们通过rebuild来实现
  SQL> select * from v$version;
  Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
  PL/SQL Release 9.2.0.6.0 - Production
  CORE 9.2.0.6.0 Production
  TNS for Solaris: Version 9.2.0.6.0 - Production
  NLSRTL Version 9.2.0.6.0 - Production
• 一：move普通表、索引基本语法：
  alter table tab_name move tablespace tbs_name;
• move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败，其他如外键，非空约束，缺省值等不会失效。
• 我们需要重新创建主键或索引，基本语法为：
  alter index index_name rebuild;
  alter index pk_name rebuild;
• 如果我们需要move索引，则使用rebuild语法：
  alter index index_name rebuild tablespace tbs_name;
  alter index pk_name rebuild tablespace tbs_name;
• 提示：查询表所具有的索引，可以使用user_indexes视图（索引和主键都在这个视图里可找到）。
• 二：move分区表及索引和普通表一样，索引会失效，区别的仅仅是语法而已。
• 分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。
  如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);如：
  ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);
  ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);
  ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);
• 移动表的某个分区：
  ================
  ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;
• 重建全局索引：
  ================
  ALTER INDEX global_index REBUILD;或
  ALTER INDEX global_index REBUILD tablespace tbs_name;
  [xsb注: 分区操作时可以带上with update global indexes选项更新全局索引。]
• 重建局部索引：
  ================
  ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;或
  ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;
• 提示：
  USER_PART_TABLES
  USER_IND_PARTITIONS
  USER_IND_SUBPARTITIONS
  USER_LOB_PARTITIONS
  USER_LOB_SUBPARTITIONS
  USER_PART_INDEXES
  USER_PART_LOBS可查询分区相关内容，同时，分区对象，也是segment，所以也可在dba_segments里查的到。
• 三：move LONG,LOB类型（据说DBMS_REDEFINITION包可以提供一些方便，没用过。）
• I:LONG类型
  long类型不能通过MOVE来传输特别提示，尽量不要用LONG类型，特难管理。
• 
  1，LONG不能使用insert into ... select ...等带select的模式。如
  create table t123 (id int,en long);则
  insert into t123(id,en) select * from t123;
• 1. 报告错误，可以用pl/sql来帮助解决，如：
  declare
     cursor cur_t123 is select * from t123;
     use_t123 cur_t123%rowtype;
• begin
  open cur_t123;
  loop
  fetch cur_t123 into use_t123;
  exit when cur_t123%notfound;
  insert into t123(id,en) values (use_t123.id,use_t123.en);
  end loop;
  close cur_t123;
  end;
  /
• 对有LONG类型字段的表的转移，可以使用：
  create新表的方法。
  * create一个新的表，存储在需要转移的表空间。
  * 创建新的索引（使用tablespace 子句指定新的表空间）。
  * 把数据转移过来
  方法一：用COPY的方法：
  insert t123(id,en) using select id,en from t123;
  方法二：PL/SQL（如上）
  方法三：直接就把LONG转换成CLOB类型
  create table t321(id int,en clob) tablespace users;
  insert into t321(id,en) select id,to_lob(en) from t123;
  方法四：exp/imp
  exp bigboar/bigboar file=a.dat tables=t123
  imp bigboar/bigboar file=a.dat full=y IGNORE =y
  * drop掉旧表。
  * rename 新表为旧表表名。
• II: LOB类型在建立含有lob字段的表时，oracle会自动为lob字段建立两个单独的segment,一个用来存放数据（segment_type= LOBSEGMENT），另一个用来存放索引（segment_type=LOBINDEX）。默认它们会存储在和表一起的表空间。我们对表MOVE时， LOG类型字段和该字段的索引不会跟着MOVE，必须要单独来进行MOVE，语法如下如：
  alter table t321 move tablespace users;
  alter table t321 move lob(en) store as (tablespace users);