月份：2020年11月

DM7使用DMRAMN执行归档恢复

Posted on by YongJing(敬勇)

使用DMRAMN执行归档恢复
使用RECOVER命令完成数据库恢复工作，可以是基于备份集的恢复工作，也可以是使用本地归档日志的恢复工作。如果还原后，数据已经处于一致性状态了，则可以使用更新DB_MAGIC方式恢复，前提是不需要重做日志。语法如下：

RECOVER DATABASE '' []
[USE DB_MAGIC ] [UNTIL TIME '< 时间串>'] [UNTIL LSN ]; |
RECOVER DATABASE '' FROM BACKUPSET '< 备份集目录>'[DEVICE TYPE DISK|TAPE[PARMS '< 介质参数>']] [IDENTIFIED BY
< 密码> [ENCRYPT WITH < 加密算法>]]
; |
RECOVER DATABASE '' UPDATE DB_MAGIC;
 ::=
WITH ARCHIVEDIR '< 归档日志目录>'{,'< 归档日志目录>'}

DATABASE：指定还原库目标的dm.ini文件路径。
USE DB_MAGIC：指定本地归档日志对应数据库的DB_MAGIC，若不指定，则默认使用目标恢复数据库的DB_MAGIC。
UNTIL TIME：恢复数据库到指定的时间点。
UNTIL LSN：恢复数据库到指定的LSN。
BACKUPSET：指定用于还原目标数据库的备份集目录。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。DISK表示存储备份集到磁盘，TAPE表示存储到磁带。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
IDENTIFIED BY：指定备份时使用的加密密码，供还原过程解密使用。
ENCRYPT WITH：指定备份时使用的加密算法，供还原过程解密使用，若未指定，则使用默认算法。
WITH ARCHIVEDIR：本地归档日志搜索目录，若未指定，则仅使用目标库配置本地归档目录，RAC环境还会取REMOTE归档目录。

数据库恢复有三种方式：更新DB_MAGIC恢复、从备份集恢复和从归档恢复。

从归档恢复
从归档恢复是利用重做本地归档日志来恢复数据的过程。从归档恢复允许恢复到指定的时间点及指定的LSN值。若同时指定了时间点和LSN，则以较早的为结束点。

由于从本地归档恢复允许使用USE DB_MAGIC指定待收集归档的DB_MAGIC，那么就会存在跨库恢复情况，所以，归档日志的正确使用完全由用户保证。因此，为了保证用户能正确使用从归档恢复，除了下文这两种情况，其他情况（可能导致数据被破坏）不建议用户使用从本地归档恢复。

可以使用从归档恢复的情况：
1、执行过从备份集还原，以及执行过从备份集还原恢复的库，本地归档日志属于备份集备份时的库，本地归档日志的DB_MAGIC与备份集中记录DB_MAGIC值；
2、未执行过还原的库，但恢复目标库与本地归档日志来源于同一个库，且目标库与原来的库分离后没有再单独执行过操作或者故障重启过。比如一个数据库A因故障关闭后，拷贝一份作为数据库B，此时数据库A和B完全相同。此后，数据库A故障重启，并正常执行其他操作，生成了新的本地归档日志。如果想利用A的本地归档日志去将B恢复到A的状态，那么可以利用DMRMAN工具执行从本地归档恢复。若中间B启动过或者执行过其他操作，则均不能再使用A的归档日志进行恢复操作。

利用归档恢复数据库至最新状态的完整示例如下：

1) 启动DIsql联机备份数据库，以及备份到检查点结束为止的本地归档日志。
查看动态视图V$RLOG中的CKPT_LSN列，可以得出当前检查点LSN。

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3

used time: 83.467(ms). Execute id is 866393.
SQL> select ckpt_lsn from v$rlog;

LINEID     CKPT_LSN
---------- --------------------
1          19738189

used time: 0.817(ms). Execute id is 866466.
SQL> backup archivelog until lsn 19738189 backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_lsn_to_19738189';
executed successfully
used time: 00:00:43.255. Execute id is 866619.
SQL> backup database full backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_bak_recover_arch';
executed successfully
used time: 00:00:12.873. Execute id is 866805.

2) 准备目标库，可以使用备份库，也可以重新生成库。
如果使用原备份库，且备份库故障，需要先执行目标库归档修复：

RMAN>REPAIR ARCHIVELOG DATABASE 'opt/dmdbms/data/dm.ini';

如果使用新生成的库，生成之后需要先重启一下服务器，才可以被还原。重新生成的库不需要执行归档修复（repair）操作。重新生成库操作如下：

[dmdba@dmks ~]$ dminit path=/dm_home/dmdbms/data db_name=dameng_for_recover auto_overwrite=1 port_num=5336
initdb V7.1.6.46-Build(2018.02.08-89107)ENT
db version: 0x7000a
file dm.key not found, use default license!
License will expire in 14 day(s) on 2020-06-30

 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover01.log


 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover02.log

write to dir [/dm_home/dmdbms/data/dameng_for_recover].
create dm database success. 2020-06-16 16:03:18


[root@dmks root]# ./dm_service_installer.sh  -i /dm_home/dmdbms/data/dameng_for_recover/dm.ini -p dmrc -t dmserver
Move the service script file(/dm_home/dmdbms/bin/DmServicedmrc to /etc/rc.d/init.d/DmServicedmrc)
Finished to create the service (DmServicedmrc)

[root@dmks root]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]


[dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 10.727(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          450923536

used time: 1.042(ms). Execute id is 5.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          1009602608

used time: 1.332(ms). Execute id is 6.
3) 启动RMAN，校验备份。
RMAN> check backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
check backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
CMD END.CODE:[0]
check backupset successfully.
time used: 5.986(ms)
RMAN> check backupset '/dm_home/dmdbms/backup/arch_bak';
check backupset '/dm_home/dmdbms/backup/arch_bak';
CMD END.CODE:[0]
check backupset successfully.
time used: 7.154(ms)

4) 还原数据库。

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
file dm.key not found, use default license!
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
RESTORE DATABASE  CHECK......
RESTORE DATABASE , dbf collect......
RESTORE DATABASE , dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/db_bak_recover_arch] START......
total 22 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 26 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 68070.231(ms)

5)还原归档日志

RMAN> restore archive log from backupset '/dm_home/dmdbms/backup/arch_bak'to archivedir '/dm_home/dmdbms/backup/arch';
restore archive log from backupset '/dm_home/dmdbms/backup/arch_bak' to archivedir '/dm_home/dmdbms/backup/arch';
RESTORE ARCHIVE CHECK......
RESTORE ARCHIVE, dbf collect......
RESTORE ARCHIVE, dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/arch_bak] START......
total 60 packages processed...
total 61 packages processed...
total 62 packages processed!
CMD END.CODE:[0]

restore successfully.
time used: 38325.097(ms)

6)查看备份集的数据库信息，获取源库的DB_MAGIC。

RMAN> show backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
show backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';




system path:           /dm_home/dmdba/dmdbms/data/jydm
db magic:              708657636
permanent magic:       1250320462
rac node:              1
page check:            0
rlog encrypt:          0
external cipher[id/name]:  0/
external hash[id/name]:  0/
length in char:        0
use new hash:          1
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
unicode_flag/charset:  0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
archive flag:          1
blank_pad_mode:        0
crc_check:             TRUE


backupset sig:         BA
backupset version:     4009
database name:         jydm
backup name:           DB_FULL_jydm_20200617_172643_000246
backupset description:
backupset ID :         1563647712
parent backupset ID:     -1
META file size :       94720
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          database
mpp_timestamp:         1592386003
ddl_clone:             FALSE
mpp_flag:              FALSE
backup level:          online
backup type:           full
without log:           FALSE
START_LSN:             19751828
START_SEQ:             7739639
END_LSN:               19752212
END_SEQ:               7739836
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-17 17:26:55
min trx start lsn:     16553039
min exec ver:          0x0701060C
pkg size:              0x02000000


backupset directory: /dm_home/dmdbms/backup/db_bak_recover_arch
backupset name:        db_bak_recover_arch
backup data file num:  9
backup piece num:      2


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |407807    |db_bak_recover_arch.bak                                 |DATA
1         |104       |db_bak_recover_arch_1.bak                               |LOG


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len
1         |0         |SYSTEM                           |0         |/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF|                                 |24117248
2         |1         |ROLL                             |0         |/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF|                                 |208666624
3         |4         |MAIN                             |0         |/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF|                                 |197132288
4         |5         |BOOKSHOP                         |0         |/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF|                                 |157286400
5         |6         |DMHR                             |0         |/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF|                                 |134217728
6         |7         |USERS                            |0         |/dm_home/dmdba/dmdbms/data/jydm/users01.dbf|                                 |52428800
7         |9         |SYSAUX                           |0         |/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF|                                 |175112192
8         |10        |FG_PERSON                        |0         |/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF|                                 |134217728


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time
9         |                                 |101376              |19751827            |7739639             |8                   |19752212            |7739836             |0000-00-00 00:00:00 |0000-00-00 00:00:00


show backupsets successfully.
time used: 5.320(ms)

RMAN> show backupset '/dm_home/dmdbms/backup/arch_bak';
show backupset '/dm_home/dmdbms/backup/arch_bak';




system path:           /dm_home/dmdba/dmdbms/data/jydm
db magic:              708657636
permanent magic:       1250320462
rac node:              1
page check:            0
rlog encrypt:          0
external cipher[id/name]:  0/
external hash[id/name]:  0/
length in char:        0
use new hash:          1
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
unicode_flag/charset:  0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
archive flag:          1
blank_pad_mode:        0
crc_check:             TRUE


backupset sig:         BA
backupset version:     4009
database name:         jydm
backup name:           ARCH_20200617_173005_000673
backupset description:
backupset ID :         -99299676
parent backupset ID:     -1
META file size :       135680
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          archivelog
mpp_timestamp:         1592386205
arch start lsn:        13775970
arch end lsn:          19752806
backup level:          online
backup type:           archive
without log:           TRUE
START_LSN:             19752785
START_SEQ:             7740126
END_LSN:               19752968
END_SEQ:               7740221
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-17 17:30:45
min trx start lsn:     16553039
min exec ver:          0x0701060C
pkg size:              0x02000000


backupset directory: /dm_home/dmdbms/backup/arch_bak
backupset name:        arch_bak
backup data file num:  20
backup piece num:      1


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |1501420   |arch_bak.bak                                            |LOG


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time
1         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003546144_0.log|314496512           |13775970            |4737535             |8                   |14461220            |0                   |2020-05-26 00:35:46 |2020-05-26 00:36:31
2         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003632028_0.log|271943680           |14461221            |5351778             |8                   |14942037            |0                   |2020-05-26 00:36:31 |2020-05-28 23:07:46
3         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200528230749046_0.log|18832384            |14942037            |5882910             |8                   |15088441            |0                   |2020-05-28 23:07:46 |2020-05-29 18:43:51
4         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529184351533_0.log|444416              |15088442            |5919684             |8                   |15092081            |0                   |2020-05-29 18:43:51 |2020-05-29 19:37:55
5         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193755551_0.log|5632                |15092082            |5920544             |8                   |15092086            |0                   |2020-05-29 19:37:55 |2020-05-29 19:39:25
6         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193925775_0.log|16965120            |15092086            |5920547             |8                   |15220690            |0                   |2020-05-29 19:39:25 |2020-05-30 14:28:56
7         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200530142857037_0.log|152148480           |15220691            |5953674             |8                   |16361831            |0                   |2020-05-30 14:28:56 |2020-06-03 19:51:50
8         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200603195207512_0.log|88146944            |16361832            |6250831             |8                   |17046028            |0                   |2020-06-03 19:51:50 |2020-06-06 02:00:09
9         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606020009584_0.log|6746112             |17046029            |6422985             |8                   |17088792            |0                   |2020-06-06 02:00:09 |2020-06-06 06:13:36
10        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606061513930_0.log|24576               |17088793            |6436153             |8                   |17088875            |0                   |2020-06-06 06:13:36 |2020-06-06 06:24:48
11        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606062454805_0.log|7680                |17088876            |6436193             |8                   |17088886            |0                   |2020-06-06 06:24:48 |2020-06-06 06:26:17
12        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606062617837_0.log|7168                |17088886            |6436200             |8                   |17088896            |0                   |2020-06-06 06:26:17 |2020-06-06 06:27:51
13        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606062942967_0.log|7680                |17088896            |6436206             |8                   |17088905            |0                   |2020-06-06 06:27:51 |2020-06-06 06:29:43
14        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606064235117_0.log|15872               |17088906            |6436213             |8                   |17088950            |0                   |2020-06-06 06:29:43 |2020-06-06 13:11:01
15        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606142912805_0.log|5120                |17088951            |6436236             |8                   |17088956            |0                   |2020-06-06 13:11:01 |2020-06-06 14:29:13
16        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200606220653158_0.log|314339328           |17088957            |6436238             |8                   |18425990            |0                   |2020-06-06 14:29:13 |2020-06-13 21:00:00
17        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200613210000757_0.log|314571264           |18425991            |7050174             |8                   |19599505            |0                   |2020-06-13 21:00:00 |2020-06-17 09:35:23
18        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200617093523748_0.log|33190912            |19599505            |7664563             |8                   |19731163            |0                   |2020-06-17 09:35:23 |2020-06-17 16:18:01
19        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200617161801000_0.log|4020224             |19731163            |7729381             |8                   |19747260            |0                   |2020-06-17 16:18:01 |2020-06-17 17:01:01
20        |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200617170101954_0.log|1499136             |19747260            |7737225             |8                   |19752806            |0                   |2020-06-17 17:01:01 |2020-06-17 17:30:05


show backupsets successfully.
time used: 25.316(ms)

若还原后，立即执行执行恢复，可以不用获取源库DB_MAGIC。因为DMRMAN执行库级备份集还原后，会将备份集中的DB_MAGIC刷入还原后的库中。

7>利用归档恢复数据库。由步骤6显示的备份集信息可知，源库的DB_MAGIC值为1447060265。

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/arch' use db_magic 708657636;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/arch' use db_magic 708657636;
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 19751827

EP[0] Recover LSN from 19751828 to 19752806.
Recover from archive log finished, time used:0.165s.
recover successfully!
time used: 7051.292(ms)

8)检查目标库中的表t1确认数据与源数据库一致。

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4

used time: 9.369(ms). Execute id is 6.

9)在源数据库的t1表中插入一行数据

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4

used time: 0.779(ms). Execute id is 879030.
SQL> insert into t1 values(5,5);
affect rows 1

used time: 1.188(ms). Execute id is 879204.
SQL> commit;
executed successfully
used time: 13.572(ms). Execute id is 879210.
SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5

used time: 0.590(ms). Execute id is 879218.

10)在源数据库中备份归档日志，从lsn为19752806开始进行备份，因为之前备份的归档日志最大lsn为19752806,将将备份文件传到目标数据库上

SQL> backup archivelog from lsn 19752806 backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_bak_from_19752806';
executed successfully
used time: 00:00:01.850. Execute id is 879461.


[dmdba@shard1 bak]$ scp -r arch_bak_from_19752806 dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
arch_bak_from_19752806.bak                                                                                                                                                                                100%   10MB   9.8MB/s   00:00
arch_bak_from_19752806.meta                                                                                                                                                                               100%   61KB  60.5KB/s   00:00
[dmdba@shard1 bak]$

11)检查备份的归档日志

RMAN> check backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806';
check backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806';
CMD END.CODE:[0]
check backupset successfully.
time used: 7.121(ms)

12)查看备份集信息，获取源库的DB_MAGIC

RMAN> show backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806';
show backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806';




system path:           /dm_home/dmdba/dmdbms/data/jydm
db magic:              708657636
permanent magic:       1250320462
rac node:              1
page check:            0
rlog encrypt:          0
external cipher[id/name]:  0/
external hash[id/name]:  0/
length in char:        0
use new hash:          1
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
unicode_flag/charset:  0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
archive flag:          1
blank_pad_mode:        0
crc_check:             TRUE


backupset sig:         BA
backupset version:     4009
database name:         jydm
backup name:           ARCH_20200617_190859_000032
backupset description:
backupset ID :         -1402072600
parent backupset ID:     -1
META file size :       61952
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          archivelog
mpp_timestamp:         1592392139
arch start lsn:        19747260
arch end lsn:          19787571
backup level:          online
backup type:           archive
without log:           TRUE
START_LSN:             19786788
START_SEQ:             7756899
END_LSN:               19787571
END_SEQ:               7757301
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-17 19:09:00
min trx start lsn:     16553039
min exec ver:          0x0701060C
pkg size:              0x02000000


backupset directory: /dm_home/dmdbms/backup/arch_bak_from_19752806
backupset name:        arch_bak_from_19752806
backup data file num:  2
backup piece num:      1


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |10052     |arch_bak_from_19752806.bak                              |LOG


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time
1         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200617170101954_0.log|1499136             |19747260            |7737225             |8                   |19752806            |0                   |2020-06-17 17:01:01 |2020-06-17 17:30:05
2         |/dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200617173005734_0.log|8788480             |19752806            |7740145             |8                   |19787571            |0                   |2020-06-17 17:30:05 |2020-06-17 19:08:59


show backupsets successfully.
time used: 6.440(ms)

13)还原最新生成的备份归档日志

RMAN> restore archive log from backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806' to archivedir '/dm_home/dmdbms/backup/arch';
restore archive log from backupset '/dm_home/dmdbms/backup/arch_bak_from_19752806' to archivedir '/dm_home/dmdbms/backup/arch';
RESTORE ARCHIVE CHECK......
RESTORE ARCHIVE, dbf collect......
RESTORE ARCHIVE, dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/arch_bak_from_19752806] START......
total 1 packages processed...
total 2 packages processed!
CMD END.CODE:[0]

restore successfully.
time used: 317.745(ms)

14)使用之前的数据库备份集还原目标数据库

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_recover_arch';
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
RESTORE DATABASE  CHECK......
RESTORE DATABASE , dbf collect......
RESTORE DATABASE , dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/db_bak_recover_arch] START......
total 22 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 26 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 29783.391(ms)

15)使用归档日志执行恢复

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/arch' use db_magic 708657636;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/arch' use db_magic 708657636;
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 19751827

total redo pages:2920


EP[0] Recover LSN from 19751828 to 19787571.
Recover from archive log finished, time used:1.503s.
recover successfully!
time used: 8275.787(ms)
RMAN>

16)启动数据数据库检查表t1的数据

[root@dmks init.d]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5

used time: 1.662(ms). Execute id is 5.

17) 若执行后归档恢复过程中，出现归档不足的错误，则利用归档校验工具dmrachk，查看归档目录中归档连续性情况，然后再利用备份的
本地归档日志，还原到归档目录后，再次执行恢复操作

[dmdba@dmks backup]$ dmrachk arch_path=/dm_home/dmdbms/backup/arch
rachk V7.1.6.46-Build(2018.02.08-89107)ENT
the database db_magic: 708657636
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200526003546144_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 614243
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 13775970
arch_seq       : 4737535
clsn           : 14461220
next_seq       : 5351777
file len       : 314496512
file free      : 314496512
create time    : 2020-05-26 00:35:46
close time     : 2020-05-26 00:36:31
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200526003632028_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 531132
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 14461221
arch_seq       : 5351778
clsn           : 14942037
next_seq       : 5882909
file len       : 271943680
file free      : 271943680
create time    : 2020-05-26 00:36:31
close time     : 2020-05-28 23:07:46
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200528230749046_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 36774
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 14942037
arch_seq       : 5882910
clsn           : 15088441
next_seq       : 5919683
file len       : 18832384
file free      : 18832384
create time    : 2020-05-28 23:07:46
close time     : 2020-05-29 18:43:51
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200529184351533_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 860
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 15088442
arch_seq       : 5919684
clsn           : 15092081
next_seq       : 5920543
file len       : 444416
file free      : 444416
create time    : 2020-05-29 18:43:51
close time     : 2020-05-29 19:37:55
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200529193755551_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 3
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 15092082
arch_seq       : 5920544
clsn           : 15092086
next_seq       : 5920546
file len       : 5632
file free      : 5632
create time    : 2020-05-29 19:37:55
close time     : 2020-05-29 19:39:25
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200529193925775_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 33127
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 15092086
arch_seq       : 5920547
clsn           : 15220690
next_seq       : 5953673
file len       : 16965120
file free      : 16965120
create time    : 2020-05-29 19:39:25
close time     : 2020-05-30 14:28:56
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200530142857037_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 297157
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 15220691
arch_seq       : 5953674
clsn           : 16361831
next_seq       : 6250830
file len       : 152148480
file free      : 152148480
create time    : 2020-05-30 14:28:56
close time     : 2020-06-03 19:51:50
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200603195207512_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 172154
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 16361832
arch_seq       : 6250831
clsn           : 17046028
next_seq       : 6422984
file len       : 88146944
file free      : 88146944
create time    : 2020-06-03 19:51:50
close time     : 2020-06-06 02:00:09
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606020009584_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 13168
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17046029
arch_seq       : 6422985
clsn           : 17088792
next_seq       : 6436152
file len       : 6746112
file free      : 6746112
create time    : 2020-06-06 02:00:09
close time     : 2020-06-06 06:13:36
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606061513930_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 40
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088793
arch_seq       : 6436153
clsn           : 17088875
next_seq       : 6436192
file len       : 24576
file free      : 24576
create time    : 2020-06-06 06:13:36
close time     : 2020-06-06 06:24:48
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606062454805_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 7
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088876
arch_seq       : 6436193
clsn           : 17088886
next_seq       : 6436199
file len       : 7680
file free      : 7680
create time    : 2020-06-06 06:24:48
close time     : 2020-06-06 06:26:17
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606062617837_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 6
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088886
arch_seq       : 6436200
clsn           : 17088896
next_seq       : 6436205
file len       : 7168
file free      : 7168
create time    : 2020-06-06 06:26:17
close time     : 2020-06-06 06:27:51
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606062942967_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 7
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088896
arch_seq       : 6436206
clsn           : 17088905
next_seq       : 6436212
file len       : 7680
file free      : 7680
create time    : 2020-06-06 06:27:51
close time     : 2020-06-06 06:29:43
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606064235117_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 23
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088906
arch_seq       : 6436213
clsn           : 17088950
next_seq       : 6436235
file len       : 15872
file free      : 15872
create time    : 2020-06-06 06:29:43
close time     : 2020-06-06 13:11:01
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606142912805_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 2
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088951
arch_seq       : 6436236
clsn           : 17088956
next_seq       : 6436237
file len       : 5120
file free      : 5120
create time    : 2020-06-06 13:11:01
close time     : 2020-06-06 14:29:13
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200606220653158_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 613936
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 17088957
arch_seq       : 6436238
clsn           : 18425990
next_seq       : 7050173
file len       : 314339328
file free      : 314339328
create time    : 2020-06-06 14:29:13
close time     : 2020-06-13 21:00:00
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200613210000757_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 614389
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 18425991
arch_seq       : 7050174
clsn           : 19599505
next_seq       : 7664562
file len       : 314571264
file free      : 314571264
create time    : 2020-06-13 21:00:00
close time     : 2020-06-17 09:35:23
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200617093523748_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 64818
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 19599505
arch_seq       : 7664563
clsn           : 19731163
next_seq       : 7729380
file len       : 33190912
file free      : 33190912
create time    : 2020-06-17 09:35:23
close time     : 2020-06-17 16:18:01
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200617161801000_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 7844
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 19731163
arch_seq       : 7729381
clsn           : 19747260
next_seq       : 7737224
file len       : 4020224
file free      : 4020224
create time    : 2020-06-17 16:18:01
close time     : 2020-06-17 17:01:01
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200617170101954_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 2920
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 19747260
arch_seq       : 7737225
clsn           : 19752806
next_seq       : 7740144
file len       : 1499136
file free      : 1499136
create time    : 2020-06-17 17:01:01
close time     : 2020-06-17 17:30:05
crc_check      : TRUE
/*******************************************************************/
/*******************************************************************/
archive file /dm_home/dmdbms/backup/arch/ARCHIVE_LOCAL1_20200617173005734_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 17157
db_magic       : 708657636
pemnt_magic    : 1250320462
arch_lsn       : 19752806
arch_seq       : 7740145
clsn           : 19787571
next_seq       : 7757301
file len       : 8788480
file free      : 8788480
create time    : 2020-06-17 17:30:05
close time     : 2020-06-17 19:08:59
crc_check      : TRUE
/*******************************************************************/

The SUMMARY(seqno[0]):
total files: 21
okey files: 21
fail file: 0
repeat file: 0

the rachk tool running cost 5.413 ms
[dmdba@dmks backup]$

DMRMAN查看归档备份的范围：

RMAN> show backupset '/dm_home/dmdbms/backup/arch_bak' info meta;
show backupset '/dm_home/dmdbms/backup/arch_bak' info meta;




backupset sig:         BA
backupset version:     4009
database name:         jydm
backup name:           ARCH_20200617_173005_000673
backupset description:
backupset ID :         -99299676
parent backupset ID:     -1
META file size :       135680
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          archivelog
mpp_timestamp:         1592386205
arch start lsn:        13775970
arch end lsn:          19752806
backup level:          online
backup type:           archive
without log:           TRUE
START_LSN:             19752785
START_SEQ:             7740126
END_LSN:               19752968
END_SEQ:               7740221
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-17 17:30:45
min trx start lsn:     16553039
min exec ver:          0x0701060C
pkg size:              0x02000000


show backupsets successfully.
time used: 7.527(ms)

如果有丢失部分归档日志可以执行归档还原后，再次执行利用本地归档恢复操作。

RMAN>restore archive log from backupset '/dm_home/dmdbms/backup/arch_bak' ALL TO ARCHIVEDIR '/dm_home/dmdbms/backup/arch';

​

Oracle如何管理带约束的B树索引

Posted on by YongJing(敬勇)

管理带约束的B树索引
B树索引与主键和唯一键约束是分不开的。这是因为Oracle使用索引来强制执行主键和唯一键约束。若没有相关联的B树索引，将不能启用主键或唯一约束。

在创建主键或唯一键约束时，你可以选择让Oracle自动创建相应索引。在这种情况下，如果删除或禁用约束，Oracle也将自动删除相应的索引。

单独创建索引和约束也是可以的。当单独创建索引和约束时，允许删了或禁用约束，而不会自动删除相应的索引。如果有大量数据需要处理，你可能希望禁用约束，而不删除相应索引。

因为在定义外键约束时，Oracle不会自动创建索引，所以必须手动在与外键约束相关的列上创建索引。在大多数情况下，在外键列上创建B树索引是有益的，因为它有助于避免锁定问题，并提高通过主键和外键列连接父/子表的查询的性能。

1 在主键列上创建B树索引
主键约束保证在一列(或列的组合)中的值可用于唯一标识表内的记录。每个表只能有一个主键约束。主键约束不能包含空值。主键约束可以被看做是唯一(unique)约束和非空(NOT NULL)约束的结合。为每个表创建主键索引有如下几个很好的理由。
.强制执行了主键列在表内必须是唯一的这一业务需求。是的，在某些情况下，可能有一个不需要主键的表(比如日志表)，但在大多数情况下，主键对每个表都是必需的。
.主键中的许多列，在访问应用程序的查询的where子句中被频繁使用。这些列上的索引将会改善查询的性能。
.除非已定义父表主键或唯一键约束，否则Oracle将不允许创建子表的外键约束。因此，如果需要外键约束，就必须使用主键或唯一键约束。

对任何启用的主键，Oracle都需要一个与之对应的索引。有几种技术可用来创建主键约束及其对应的索引。
.首先创建表。然后在单独的alter table语句中添加主键约束。alter table语句同时创建了主键约束和索引。
.在create table 语句中内联(与列一起)或在单独的部分中指定主键约束。
.首先创建表，然后使用create index语句创建包含主键列的索引，最后使用alter table … add constraint语句添加主键约束。

1.1.使用alter table来创建主键约束和索引
下面介绍的这种技术是创建主键约束和相关联的索引最可取的方法。这种方法允许对表的创建与约束和索引的定义分别进行管理。如果你使用的应用程序包含数千个表，约束和索引，那么将创建表与建立相应的约束和索引分开，可以使用管理和诊断安装问题变得更容易。这不是死板的规定，相反，它是从诊断问题演变而来的一种偏好。

在这个例子中，表和主键约束是分别创建的。首先，创建表时没有定义任何约束。

SQL> create table cust2
  2  (
  3  cust_id number,
  4  first_name varchar2(200),
  5  last_name varchar2(200)
  6  ) tablespace reporting_data;

Table created.

然后添加主键约束

SQL> alter table cust2 add constraint cust2_pk primary key(cust_id) using index tablespace reporting_index;

Table altered.

此代码示例使用alter table … add constraint语句同时创建主键约束和唯一索引。约束和索引都被命名为CUSTS_PK。

1.2.使用create table创建主键约束和索引
另一种常见的方法是用create table语句创建主键约束和索引。可以内联(和列一起)直接指定一个约束。这种方法的优点是简单。如果在开发或测试环境中进行试验，这种方法是快速且有效的。但这种方法也有一个缺点，它不允许在多个列上定义主键。例如:

SQL> create table cust3
  2  (
  3  cust_id number primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust3 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST3');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
SYS_C0023486                                                                                                                     INDEX                       1    1048576

在这段代码中，Oracle创建了主键约束和相应的唯一索引。Oracle自动生成像SYS_C0023486这样的随机名称(约束和索引也被赋予了相同的名称)。

如果想要明确地对约束和索引提供名称，就可以执行下面这样的语句:

SQL> create table cust4
  2  (
  3  cust_id number constraint cust4_pk primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust4 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST4');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
CUST4_PK                                                                                                                         INDEX                       1    1048576

也可以指定放置索引的表空间，如下所示:

SQL> create table cust5
  2  (
  3  cust_id number constraint cust5_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

还可以在create table语句单独的部分中定义主键约束(不和列在一起)。下面是在单独的部分定义主键约束的例子:

SQL> create table cust6
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  constraint cust6_pk primary key(cust_id) using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

这种技术被称为外联(out-of-line)，因为约束的声明与列定义之间是用逗号隔开的。该做法与内联的方法相比，具有可以为主键指定多列的优势。

1.3.分别创建B-tree索引和主键约束
还可以首先创建索引，然后改变表以应用主键约束。为了保持这个例子的完整性，这里也显示了create table语句。

SQL> create table cust7
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust7_pk on cust7(cust_id) tablespace reporting_index;

Index created.

SQL> alter table cust7 add constraint cust7_pk primary key(cust_id);

Table altered.

这种方法的优点是，可以独立于索引删除或禁用主键约束。在大型数据库环境中，出于数据加载时性能方面的原因，有时可能想要删除或禁用约束。有时可能需要能删除约束，但不删除索引的灵活性。在大型数据库环境中，重建索引会花费很长的时间并消耗大量的系统资源。

另一种稍微有些牵强的情况是，有可能创建一个与主键约束定义的列不同的列的索引。例如:

SQL> create table cust8
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create index cust8_pk on cust8(cust_id,first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust8 add constraint cust8_pk primary key(cust_id);

Table altered.

建议不要创建与约束列不同的列的主键索引，但这么做是可以的。你应该知道有这种情况，避免在诊断问题时感到困惑。

1.4.查看主键约束和索引的详细信息
用如下语句可以确认某个索引的详细信息:

SQL> select index_name,index_type,uniqueness from user_indexes where table_name='CUST7';

INDEX_NAME                                                                                                                       INDEX_TYPE                  UNIQUENES
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- ---------
CUST7_PK                                                                                                                         NORMAL                      UNIQUE

输出如上所示，为了验证约束的信息，可执行如下查询:

SQL> select constraint_name,constraint_type from user_constraints where table_name='CUST7';

CONSTRAINT_NAME                                                                                                                  CONSTRAINT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
CUST7_PK                                                                                                                         P

1.5.删除主键约束和索引
创建主键约束时自动创建的索引不能直接删除。在这种情况下，如果像下面这样直接删除索引:

SQL> drop index cust4_pk;

就会收到如下错误信息:

drop index cust4_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

要删除索引，必须首先删除或禁用主键约束。例如，下面的语句将删除创建约束时自动创建的索引:

SQL> alter table cust4 disable constraint cust4_pk;

Table altered.
SQL> alter table cust4 drop constraint cust4_pk;

Table altered.

或者

SQL> alter table cust4 drop primary key;

Table altered.

在删除或禁用主键约束时，可以选择不删除相关索引。可以使用drop/disable constraint子句的keep index子句来保留索引。例如:

SQL> alter table cust4 drop constraint cust4_pk keep index;

Table altered.

此代码指示Oracle删除约束，但保留索引。如果要处理的是很大的表，那么出于载入或操纵数据时性能方面的原因，可能要禁用或删除约束，而非索引，因为删除与一个大表相关联的索引后，可能需要相当长的时间和大量资源来重新创建它。

要注意的另一个方面是，如果主键或唯一键被已启用的外键引用，而试图删除父表上的约束，如下所示:

SQL> alter table cust1 drop primary key;

就会收到如下错误信息:

alter table cust1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

在这种情况下，需要先删除或禁用引用的外键，或使用cascade子句在主键约束已经被删除或禁用时，自动删除外键约束。例如:

SQL>alter table cust1 drop constraint cust1_pk cascade;
SQL>alter table cust1 disable constraint cust1_pk cascade;
SQL>alter table cust1 drop primary key cascade;

级联删除约束只删除了所有依赖外键的约束，但不从子表中删除任何数据。

2 在唯一键列上创建B树索引
唯一键约束的主要目的是强制地不属于主键一部分的列的唯一性。如果有非主键列在一个表中是唯一的这种业务需求，那么应该使用唯一约束。例如，你可能有定义在客户表的cust_id列上的主键，但可能还需要一个last_name和first_name列的组合上的唯一键约束。

唯一键与主键有两方面的差别。首先，唯一键可以包含NULL值，其次每个表可以定义多个唯一键(而每个表只能定义一个主键)。

如果需要为某个列创建唯一约束，那么可以通过下列几种不同的方式来实现这一需求。
.使用alter table语句来创建一个唯一约束。这将自动创建一个唯一的B树索引。
.使用create table语句来创建一个唯一约束。这也将自动创建一个唯一的B树索引。
.分别创建B树索引和约束。如果想要在禁用或删除约束时分别管理索引和约束，那么可以使用这种方法。
.只创建唯一B树索引，而不费心去创建唯一键约束。如果索引中的列不能被子表的外键引用，那么可以使用这种方法。

2.1.使用alter table来创建唯一约束和索引
这种方法是我们启用唯一键约束并创建相应索引的首选方法。正如在主键约束和索引部分中提到的，将创建表的语句与创建约束和索引的语句分离，往往更容易诊断安装问题。

下面的示例演示如何创建一个表，然后在非主键列上添加一个唯一键约束。

SQL> create table cust9
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

接下来，使用alter table语句在cust表的last_name和first_name列的组合上创建一个名为cust9_ux1的唯一约束。

SQL> alter table cust9 add constraint cust9_uk1 unique(last_name,first_name) using index tablespace reporting_index;

Table altered.

此语句创建了该唯一约束。此外，Oracle会自动创建一个具有相同名称的相关索引。

2.2.使用create table创建唯一约束和索引
使用create table方法的优点在于，它简单且可以把索引和约束的创建封装在一个语句中。在用create table语句定义唯一约束时，它可以用内网联方式，也可以用外联方式来定义。

第一个例子显示了如何在一个列上内联地创建唯一键约束和索引。由于内联唯一键约束只可以定义在列上，所以我们添加了SSN列，它以内联内式定义了唯一键约束。

SQL> create table cust10
  2  (
  3  cust_id number constraint cust10_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15) constraint cust10_uk1 unique using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

下一个例子使用外联技术在first_name和last_name列的组合上创建了一个唯一约束:

SQL> create table cust11
  2  (
  3  cust_id number constraint cust11_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15),
  7  constraint cust11_uk1 unique(first_name,last_name) using index tablespace reporting_index
  8  )
  9  tablespace reporting_data;

Table created.

外联方式的定义具有允许在多列上创建一个唯一键约束的优势。

2.3.分别创建B树索引和唯一键约束
如果需要分别管理索引和约束，那么可以先创建索引，然后再创建约束。例如:

SQL> create table cust12
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust12_uk1 on cust12(first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust12 add constraint cust12_uk1 unique(first_name,last_name);

Table altered.

分别创建索引和约束的好处是，可以在不删除底层索引的情况下删了或禁用约束。在处理大量数据时，可考虑使用这种方法。如果你有任何理由需要禁用约束，然后重新启用它，就可以这样做而不删除索引(因为重新创建大索引可能需要很长一段时间)。

2.4.只创建唯一索引
还可以只创建唯一索引而不添加唯一约束。如果你从来没有计划用外键引用一个唯一键，那么只创建一个唯一索引而不定义唯一约束也是可以的。下面是创建一个无关联约束的唯一索引的例子:

SQL> create table cust13
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust13_uk1 on cust13(first_name,last_name) tablespace reporting_index;

Index created.

在用上述语句明确地创建唯一索引时，Oracle虽然创建了一个唯一索引，但却没有在dba/all/user_constraints中为约束添加条目。为什么这很重要呢?，考虑这种情况:

SQL> insert into cust13 values(1,'JAMES','STARK');

1 row created.

SQL> insert into cust13 values(2,'JAMES','STARK');

下面是被抛出的相应错误消息提示:

insert into cust13 values(2,'JAMES','STARK')
*
ERROR at line 1:
ORA-00001: unique constraint (JY.CUST13_UK1) violated

如果让你来诊断这个问题的话，你首先会检查的地方是dba_constraints，你会按错误消息中显示的名称在其中查找一个约束。然而，没有查到任何信息。

SQL> select constraint_name from dba_constraints where constraint_name='CUST13_UK1';

no rows selected

没有相关记录可能会使用人困惑:向表中插入数据时，抛出的错误消息已经表明违反了唯一约束，但在与约束相关的数据字典视图中却没有它的信息。在这种情况下，必须在dba_indexes中查看已经创建的唯一索引的详细信息。例如:

SQL> select index_name,uniqueness from dba_indexes where index_name='CUST13_UK1';

INDEX_NAME                                         UNIQUENES
-------------------------------------------------- ---------
CUST13_UK1                                         UNIQUE

如果你希望能够使用与约束相关的数据字典视图来报告唯一键约束，就应该也定义一个约束。

2.5.删除唯一键约束和索引
如果索引是创建唯一键约束时自动创建的，那么不能直接删除该索引。在这种情况下，必须删除或禁用唯一键约束，而相关的索引会自动被删除。例如:

SQL> drop index cust11_uk1;
drop index cust11_uk1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>alter table cust11 drop constraint cust11_uk1;

这行语句同时删除约束和索引。如果想保留索引，那么可以指定keep index子句。

SQL>alter table cust drop constraint cust11_uk1 keep index;

如果分别创建索引和唯一键约束，或者如果没有唯一键约束与唯一索引相关联，那么可以直接删除该索引。

3 索引外键列
外键约束确保插入数据到子表时，相应的父表记录存在。这是一个保证数据符合父/子业务关系规则的机制。外键也被称为参照完整性约束。

不同于主键和唯一键约束，Oracle不会自动创建外键列上的索引。因此，必须在定义为外键约束的列的基础上手动创建一个外键索引。在大多数情况下，应该在与外键关联的列上创建索引。这里有两个原因。.Oracle经常可以利用外键列上的索引，来改善使用外键列来连接父表和子表的查询性能。.如果外键列上没有B树索引存在，在往子表插入数据或从子表删除数据时，它会锁定父表中的所有行。对于频繁修改父表和子表的应用程序，这将导致锁定和死锁问题。

首先讨论如何在一个外键列上创建B树索引，然后再介绍用来检测未被索引的外键列的一些技巧。

3.1.在外键列上实现索引
假设有这样的需求:必须为address1表的每条记录分配cust14表中存在的一个相应cust_id列。为了强制执行这种关系，在address1表上创建如下外键约束:

SQL> create table cust14(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;

Table created.



SQL> create table address1(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;

Table created.


SQL> alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id);
alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id)
                                                                                     *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

注意，外键列必须引用父表中定义了主键或唯一键约束的列。否则，就会收到错误提示信息”ORA-02270: 此列列表无匹配的唯一键或主键”。

因为在连接cust14表和address1表时，外键列被广泛使用，且外键列上的索引会提高性能，所以在这种情况下，必须手动创建一个索引。例如，在address1表的cust_id外键列上创建普通的B树索引。

SQL>create index addr1_fk1 on address1(cust_id);

索引名不必与外键名称相同。是否这样做，只是一种个人喜好。我们觉得约束和相应的索引具有相同的名称时，维护环境更容易。

创建索引时，如果不指定表空间名称，Oracle会在用户的默认表空间放置索引。一般情况下，最好明确指定该表的索引的存放位置。例如:

create index addr1_fk1 on address1(cust_id) tablespace reporting_index;

注意，外键列上的索引，并不一定是B树类型的。在数据仓库环境中，经常在星型模式的事实表的外键列上使用位图索引。与B树索引不同，外键列上的位图索引不能解决父/子表锁定的问题。使用星型模式的应用程序通常不从事实表删除或修改子记录，因此在数据仓库环境中，在外键列上使用位图索引，锁定不是什么问题。

3.2.确定外键列是否已经被索引
如果你从头开始创建一个应用程序，那么创建程序代码，并确保每一个外键约束都有相应的索引很容易。但是，如果你继承了一个现成的数据库，就需要审慎地检查外键列是否已经被索引。

你可以使用数据字典视图来验证，外键约束的所有列上是否有相应的索引。其基本思路是检查每个外键约束，看它是否有一个相应的索引，这个任务并不像一开始看上去那么简单。用下面的查询作为例子，它可以用来指导你按正确的途径入手:

SQL> col owner for a30
SQL> col cons_name for a30
SQL> col tab_name for a30
SQL> col cons_column for a30
SQL> col ind_column for a30
SQL> select distinct
  2  a.owner owner,
  3  a.constraint_name cons_name,
  4  a.table_name tab_name,
  5  b.column_name cons_column,
  6  nvl(c.column_name,'***Check index***') ind_column
  7  from dba_constraints a,dba_cons_columns b,dba_ind_columns c
  8  where a.constraint_type='R'
  9  and a.owner=UpPER('&&user_name')
 10  and a.owner=b.owner
 11  and a.constraint_name=b.constraint_name
 12  and b.column_name=c.column_name(+)
 13  and b.table_name=c.table_name(+)
 14  and b.position=c.column_position(+)
 15  order by tab_name,ind_column;
old   9: and a.owner=UpPER('&&user_name')
new   9: and a.owner=UpPER('JY')

OWNER                          CONS_NAME                      TAB_NAME                       CONS_COLUMN                    IND_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
JY                             ADDR_FK1                       ADDRESS                        CUST_ID                        CUST_ID

虽然此查询简单并易于理解，但它并不能在所有情况下都正确地报告出未索引的外键。例如，在多列外键的情况下，以不同于索引列的顺序定义约束也不要紧，只要索引列在该索引中位于前面即可。换句话说，如果约束被定义为col1和col2，那么定义一个先是col2,然后是col1的B树索引也没关系。

另一方面，使用B树索引有助于避免锁定的问题，但位图索引却做不到这点。在这种情况下，查询还应该检查索引类型。

在这些情况下，需要用更复杂的查询来检测与外键列相关的索引问题。下面的例子是一个更复杂的查询，它使用listagg分析函数来比较外键约束列(作为字符串返回一行)与相应的索引列:

这个查询会先提示你输入一个模式名称，然后将显示没有相应的索引的外键约束。此查询还检查了索引类型，位图索引可以在外键列上存在，但它不能防止锁定问题。

SQL> select
  2   case when ind.index_name is not null then
  3     case when ind.index_type in('BITMAP') then
  4        '** Bitmp idx **'
  5     else
  6        'indexed'
  7     end
  8   else
  9     '** Check idx **'
 10   end checker,
 11   ind.index_type,
 12   cons.owner,cons.table_name,ind.index_name,cons.constraint_name,cons.cols
 13   from( select
 14         c.owner,c.table_name,c.constraint_name,
 15         listagg(cc.column_name,',') within group (order by cc.column_name) cols
 16         from dba_constraints c,dba_cons_columns cc
 17         where c.owner=cc.owner
 18         and c.owner=UPPER('&&schema')
 19         and c.constraint_name=cc.constraint_name
 20         and c.constraint_type='R'
 21         group by c.owner,c.table_name,c.constraint_name) cons
 22  left outer join
 23  (select
 24    table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols
 25    from(select
 26         ic.table_owner,ic.table_name,ic.index_name,ic.column_name,ic.column_position,i.index_type,
 27         connect_by_root(ic.column_name) cbr
 28         from dba_ind_columns ic,dba_indexes i
 29         where ic.table_owner=UPPER('&&schema')
 30         and ic.table_owner=i.table_owner
 31         and ic.table_name=i.table_name
 32         and ic.index_name=i.index_name
 33         connect by prior ic.column_position-1=ic.column_position
 34         and prior ic.index_name=ic.index_name)
 35  group by table_owner,table_name,index_name,index_type,cbr) ind
 36  on cons.cols=ind.cols
 37  and cons.table_name=ind.table_name
 38  and cons.owner=ind.table_owner
 39  order by checker,cons.owner,cons.table_name;
Enter value for schema: JY
old  18:        and c.owner=UPPER('&&schema')
new  18:        and c.owner=UPPER('JY')
old  29:        where ic.table_owner=UPPER('&&schema')
new  29:        where ic.table_owner=UPPER('JY')


CHECKER                        INDEX_TYPE                     OWNER                          TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                COLS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
** Check idx **                                               JY                             ADDRESS                                                       ADDR_FK1                       CUST_ID

表锁和外键
下面用一个简单的例子来演示外键列没有索引时的锁定问题。

首先创建两个表(dept和emp)并用一个外键约束把它们关联起来。

SQL> create table emp(emp_id number primary key,dept_id number);

Table created.

SQL> create table dept(dept_id number primary key);

Table created.

SQL> alter table emp add constraint emp_fk1 foreign key(dept_id) references dept(dept_id);

Table altered.

插入数据

SQL> insert into dept values(10);

1 row created.

SQL> insert into dept values(20);

1 row created.

SQL> insert into dept values(30);

1 row created.

SQL> insert into emp values(1,10);

1 row created.

SQL> insert into emp values(2,20);

1 row created.

SQL> insert into emp values(3,30);

1 row created.

SQL> commit;

Commit complete.

打开两个终端会话。在一个会话中，从子表删除一条记录，但不提交。

SQL> delete from emp where dept_id=10;

1 row deleted.

现在尝试(在另一个会话里)从父表中删除一些不受子表删除操作影响的数据。

SQL> delete from dept where dept_id=30;

对父表数据的删除操作会挂起，直到子表的事务初步提交(或回滚)。如果子表中的外键列上没有常规的B树索引，那么任何时间尝试往子表插入数据或删除子表的数据时，它都会在父表上放置一个全表锁，在子表的事务完成前，该全表锁会一直阻止删除或更新父表的数据。

回滚删除子表数据的操作

SQL> rollback;

Rollback complete.

当回滚删除子表数据的操作后，删除父表数据的操作报错，因为违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

现在额外创建一个子表外键列上的索引，并再次运行前面的操作。

SQL> create index emp_fk1 on emp(dept_id);

Index created.

再次执行删除操作，删除子表的数据

SQL> delete from emp where dept_id=10;

1 row deleted.

在另一个会话中删除父表数据不会挂起会立即报违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

这样就能够独立地运行前面列出的两个delete语句。当外键列上有B树索引时，如果从子表中删除数据，Oracle将不会过分地锁定父表中的所有行数据。

Oracle如何实现B树索引

Posted on by YongJing(敬勇)

实现B树索引
这里将介绍使用B树索引时会遇到的典型任务。典型的任务包括。
.创建索引
.报告索引
.显示重新创建索引需要的代码
.删除索引

1 创建B树索引
下面给出的是一个示例脚本，它创建一个表，并在单独的表空间创建与之相关的索引。表和索引从表空间继承存储属性，这是因为在create table或create index语句中没有指定存储参数。此外，你希望主键和唯一键约束自动创建B树索引。

SQL> create table cust1(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;

Table created.

SQL> alter table cust1 add constraint cust_pk primary key(cust_id) using index tablespace reporting_index;

Table altered.

SQL> alter table cust1 add constraint cust_uk1 unique(last_name,first_name) using index tablespace reporting_index;

Table altered.

SQL> create table address(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;

Table created.

SQL> alter table address add constraint addr_fk1 foreign key(cust_id) references cust1(cust_id);

Table altered.

SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index;

Index created.

此脚本创建了两个表。父表是cust1，它的主键是cust_id。子表是address，它的主键是address_id。在address表中，cust_id列作为外键存在，它映射到cust1表的cust_id列。

此脚本也创建了三个B树索引。其中第一个是创建主键约束时自动创建的。第二个索引是创建唯一约束时自动创建的。第三个索引是明确创建在address表中的cust_id外键列上的。所有这三个索引都是在reporting_index表空间中创建的，而表是在reporting_data表空间中创建的。

2 报告索引
上面的例子中创建的索引的详细信息可以通过查询数据字典来验证。

SQL> select index_name,index_type,table_name,tablespace_name,status from user_indexes where table_name in('CUST1','ADDRESS');

INDEX_NAME                                         INDEX_TYPE                  TABLE_NAME                                         TABLESPACE_NAME                                    STATUS
-------------------------------------------------- --------------------------- -------------------------------------------------- -------------------------------------------------- --------
ADDR_FK1                                           NORMAL                      ADDRESS                                            REPORTING_INDEX                                    VALID
CUST_PK                                            NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID
CUST_UK1                                           NORMAL                      CUST1                                              REPORTING_INDEX                                    VALID

运行以下查询来验证创建了索引的列:

SQL> select index_name,column_name,column_position from user_ind_columns where table_name in('CUST1','ADDRESS') order by index_name,column_position;

INDEX_NAME                                         COLUMN_NAME                    COLUMN_POSITION
-------------------------------------------------- ------------------------------ ---------------
ADDR_FK1                                           CUST_ID                                      1
CUST_PK                                            CUST_ID                                      1
CUST_UK1                                           LAST_NAME                                    1
CUST_UK1                                           FIRST_NAME                                   2

要显示区的数目和已使用的空间，可以运行以下查询:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');

no rows selected

请注意，这个例子的输出结果显示，没有为索引分配段，区或空间。

从Oracle 11g第2版开始，在创建表时，如果还没有往表中插入数据，相关的段(和区)将会初步推迟创建。这意味着直到数据行被插入到相关的表之后，才会为相关的索引创建段。为了说明这一点，给CUST1表插入一行，也给ADDRESS表插入一行，如下所示:

SQL> insert into cust1 values(1,'STARK','JIM');

1 row created.

SQL> insert into address values(100,1,'Vacuum Ave','Portland','OR');

1 row created.

SQL> commit;

Commit complete.

重新运行这个查询(段的使用报告)产生的输出如下:

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST1','ADDRESS');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
ADDR_FK1                                                                                                                         INDEX                       1     131072
CUST_PK                                                                                                                          INDEX                       1     131072
CUST_UK1                                                                                                                         INDEX                       1     131072

3 显示创建索引的代码
有时候可能需要删除一些索引。这些索引可能是由过时的应用程序建立的，也可能是你自己以前建立的，但已经用不到了。在删除索引之前，建议你首先生成重新创建索引所需的数据定义语言(DDL)。如果删除索引对性能有不利影响而需要重新创建它，就可以重新创建索引(就像没有删除它一样)。

可以使用dbms_metadata.get_ddl函数来显示对象的DDL。确保为LONG变量设置适当的值，使用返回的CLOB值能全部显示出来。例如:

SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;

下面是输出结果:

DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------

  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

这段代码显示了重新创建索引需要的所有内容。这些代码中的许多值反映了从索引表空间继承的默认设置或存储参数。

如果想要显示当前连接的用户的所有索引元数据，可以运行下面的代码:

SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------

  CREATE INDEX "JY"."CUST_IDX1" ON "JY"."CUST" ("LAST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE INDEX "JY"."CUST_IDX2" ON "JY"."CUST" ("FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


  CREATE UNIQUE INDEX "JY"."CUST_PK" ON "JY"."CUST1" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)


  CREATE UNIQUE INDEX "JY"."CUST_UK1" ON "JY"."CUST1" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"


  CREATE INDEX "JY"."ADDR_FK1" ON "JY"."ADDRESS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "REPORTING_INDEX"

如果当前连接的用户有很多索引，这个查询将会产生大量的输出。

4 删除B树索引
如果确定不再使用某个索引了，那么应该删除它。在删除索引之前，应采取必要的预防措施，以确保不会对性能产生不利影响。如果可能的话，最好的办法是在与生产环境同等条件(在硬件，数据，负载等方面)的测试环境中删除索引，确定对性能的不利影响。如果不可能进行彻底的测试，那么在删除前考虑先做以下工作。
.启用对索引的监测。
.使用索引不可见。
.使用索引不可用。

这样做是为了在实际删除之前，先确定该索引没有用于任何目的。监控索引会让你了解应用程序的select语句是否使用了它。但索引监控不会告诉你该索引是否被用于其他内部用途，如用来强制执行某个约束或防止锁定问题。

使用一个索引不可见需要Oracle 11g及以上版本。不可见索引仍然由Oracle维护，但查询优化器确定执行计划时不考虑它。请注意，不可见的索引仍然可以由Oracle在内部使用，用来避免锁定问题或强制执行约束。所以，使用索引不可见并不是用来确定该索引是否被使用的完全可靠的方法。

下面是使用索引 不可见的一个例子:

SQL> alter index addr_fk1 invisible;

Index altered.

此代码使用索引对查询优化器不可见，因此，它不能在查询中用来检索行。然而，当修改表中的记录时，该索引结构仍然由Oracle维护。如果确定该索引对性能非常关键，那么可以通过如下命令很容易地使用它再次对优化器可见。

SQL> alter index addr_fk1 visible;

Index altered.

删除索引之前的另一种选择是使其不可用。

SQL> alter index addr_fk1 unusable;

Index altered.

此代码使得索引不可用，但不会删除它。不可用表示，不但优化器不会使用索引，而且当DML语句操作它的表时，Oracle也不会维护该索引。此外，不可用的索引不能在内部使用，用于强制执行约束或避免锁定问题。

如果需要重新启用不可用的索引，那么就必须重建它。而重建一个大型的索引，会消耗大量的时间和资源。

SQL> alter index addr_fk1 rebuild;

Index altered.

当确信不需要某个索引后，就可以使用drop index语句来删除它。这个语句将永久删除该索引，找回该索引的唯一办法是重新创建它。

SQL> drop index addr_fk1;

Index dropped.

Oracle如何创建B树索引

Posted on by YongJing(敬勇)

创建B树索引之前，为了慎重起起见，有必要从架构层面考虑一些将影响可维护性和可用性的问题。以下是建立索引之前，应该考虑的架构性问题。
.在创建索引之前，首先对它的大小进行估计。
.考虑指定表索引的表空间(与表分离)。这使得分开管理表和索引变得更轻松，如备份和恢复任务。
.允许对象从它闪的表空间继承存储参数。
.定义创建索引时要使用的命令标准。

1 在创建索引前估计索引的大小
一张大表上创建索引之前，可能需要估计它将会占用的空间大小。预测索引大小最好的方法是在测试环境中创建它，测试环境中有生产环境的典型数据集。如果不能建立生产数据的完整副本，那么经常可以用数据的一个子集来推断在生产中所需索引空间的大小。如果你没有使用削减的生产数据的奢侈条件，还可以使用dbms_space.create_index_cost存储过程来估算索引的大小。例如，如下代码估算了在cust表的first_name列上创建索引的大小:

SQL> set serverout on
SQL> exec dbms_stats.gather_table_stats(user,'CUST');

PL/SQL procedure successfully completed.

SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost('create index cust_idx2 on cust(first_name)',:used_bytes,:alloc_bytes);

PL/SQL procedure successfully completed.

下面是这个例子的一些示例输出:

SQL> print :used_bytes

USED_BYTES
----------
      7490

SQL> print :alloc_bytes

ALLOC_BYTES
-----------
      65536

used_bytes变量给出了索引数据需要多少空间的估计。alloc_bytes变量提供了将在表空间内分配多大空间的估计。

下一步，创建索引。

SQL> create index cust_idx2 on cust(first_name);

Index created.

用如下查询显示所占用的空间的实际数额:

SQL> select bytes from user_segments where segment_name='CUST_IDX2';

     BYTES
----------
     65536

输出显示空间分配字节数的估计量等于实际使用量。

根据记录数，列数，数据类型和统计数据的准确性，输出的结果可能会有所不同。除初始大小之外，还要牢记随着记录插入到表中，该索引将增大。必须对索引占用的空间进行监控，并确保有足够的磁盘空间，以适应未来的增长需求。

2 为索引创建单独的表空间
对于关键的应用程序，必须提前考虑表和索引会消耗多少空间，以及它们增长的速度有多快。空间消耗和对象的增长对数据库可用性有直接影响。如果空间用尽了，那么数据库将变得不可用。最好的管理办法是，针对空间要求创建表空间，并在创建对象时明确指定表空间名。考虑到这一点，我们建议将表和索引分别保存到单独的表空间。考虑以下原因。
.支持采用不同的备份和恢复要求。你可能希望灵活地用与备份表不同的频率来备份索引。或者可以选择不备份索引，因为你知道可以重新创建它们。
.如果让表或索引从表空间继承它的存储特性，使用单独的表空间可以为表空间内创建的对象量身定制存储属性。表和索引往往有不同的存储要求(如区的大小，记录等)。
.运行维护报告时，如果报告针对不同的表空间具有不同的节(section)，有时管理表和索引会更容易。

如果这些原因出现在你的环境中，那么可能值得付出额外的努力，对表和索引采用不同的表空间。如果你没有前面提到的任何需要，那么把表和索引保存在相同的表空间是不错的选择。

DBA经常出于性能的原因，考虑把索引放置在单独的表空间。如果你有从头开始建立存储系统的奢侈条件，可以把挂载点(mount point)设置为有自己的磁盘和控制器，那么可能会看到把表和索引存储在不同表空间的一些IO上的好处。如今，存储管理员往往会分配给你的SAN中的一大片存储，并且无法保证数据和索引将存储在单独的磁盘和控制器上。因此，把表和索引存储在不同表空间的做法，通常对提高性能没什么帮助。换句话说，性能获得提高不是通过将表和索引存储到不同的表空间实现的，而是由于在所有可用的设备上均匀地分布IO实现的。

下面的代码显示的是为表和索引单独建立表空间的例子。它使用固定大小的区和自动段空间管理(ASSM)创建了本地管理的表空间。

SQL> create tablespace reporting_data datafile '+DATA/JYCS/reporting_data01.dbf' size 1G extent management local uniform size 1M
  2  segment space management auto;

Tablespace created.


SQL> create tablespace reporting_index datafile '+DATA/JYCS/reporting_index01.dbf' size 500M extent management local uniform size 128K
  2  segment space management auto nologging;

Tablespace created.

我们更倾向于使用统一大小的区，因为这确保了表空间内存的所有区大小相同，从而减少了对象创建和删除时的碎片。ASSM的功能允许Oracle自动管理存储属性，而以前这需要手动监测和由DBA维护。

3 从表空间继承存储参数
创建表或索引时，有几个与表空间相关的技术细节需要注意。例如，如果创建表和索引时不指定存储参数，则表和索引会继承表空间的存储参数。这是在大多数情况下所需的行为。这样就可以不必手动指定这些参数。如果需要创建一个具有与表空间不同的存储参数的对象，那么用create table/index语句来实现。

此外，请记住，如果不明确指定表侬间，默认情况下，表和索引创建在用户的默认表空间中。在开发和测试环境中，这是可以接受的。对于生产环境，则应该考虑在create table/index语句中明确命令表空间。

4 命令标准
在创建和管理索引时，制定一些命名标准是非常可取的。考虑以下因素.
.当错误消息中包含表示表，索引类型等的信息时，简化了对问题的诊断。
.显示索引信息的报告更容易被分组，因此更具可读性并更容易地发现其中的规律和问题。鉴于这些需求，这里有一些示例索引命名指南。
.主键索引名称应该包含表名和一个后缀，如_UKN，其中N是一个数字。
.外键列上的索引应包含外键表和一个后缀，如_FKN，其中N是一个数字。
.对于不用于约束的索引，使用表名和一个后缀，如_IDXN，其中N是一个数字。
.基于函数的索引的名称应包含表名和一个后缀，如_FCN，其中N是一个数字。

一些厂商在命名索引时使用前缀。例如，主键索引将被命名为PK_CUST(而不是CUST_PK)。所有这些不同的命名标准都是有效的。

Oracle中的B树索引

Posted on by YongJing(敬勇)

B树索引是Oracle的默认的索引类型。因为表中的行标识符(rowid)和相关的列值存储在一个平衡的树状结构的索引块中，所以该索引类型被称为B树索引。使用Oracle的B树索引有以下几个原因.
.提高SQL语句的性能
.强制执行主键和唯一键约束的唯一性
.减少通过主键和外键约束关联的父表和子表间潜在的锁定问题

Oracle如何使用B树索引
为了充分理解B树索引的内部实现，以便在建立数据库应用程序时能做出明智的索引决定。将举例说明，首先创建测试表cust

SQL> create table cust(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30));

Table created.

在last_name列上创建B树索引

SQL> create index cust_idx1 on cust(last_name);

Index created.

向表cust中插入数据

SQL> insert into cust(cust_id,last_name,first_name)
  2  select rownum rn ,a.last_name,a.first_name from hr.employees a
  3  union
  4  select rownum+(107*1) rn ,a.last_name,a.first_name from hr.employees a
  5  union
  6  select rownum+(107*2) rn ,a.last_name,a.first_name from hr.employees a
  7  union
  8  select rownum+(107*4) rn ,a.last_name,a.first_name from hr.employees a
  9  union
 10  select rownum+(107*5) rn ,a.last_name,a.first_name from hr.employees a
 11  union
 12  select rownum+(107*6) rn ,a.last_name,a.first_name from hr.employees a
 13  union
 14  select rownum+(107*7) rn ,a.last_name,a.first_name from hr.employees a
 15  union
 16  select rownum+(107*8) rn ,a.last_name,a.first_name from hr.employees a
 17  union
 18  select rownum+(107*9) rn ,a.last_name,a.first_name from hr.employees a
 19  union
 20  select rownum+(107*10) rn ,a.last_name,a.first_name from hr.employees a;

1070 rows created.

SQL> commit;

Commit complete.

SQL> select distinct last_name,first_name from cust where rownum<11;

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Banda                          Amit
Atkinson                       Mozhe
Bissot                         Laura
Ande                           Sundar
Bates                          Elizabeth
Bell                           Sarah
Bernstein                      David
Baer                           Hermann
Baida                          Shelli

10 rows selected.

插入数据后，确保该表的统计信息是最新的，以便为查询优化器提供足够的信息，从而做出如何检索数据的更好决定，执行如下命令收集表的统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'CUST',cascade=>true);

PL/SQL procedure successfully completed.

不建议使用analyze语句(带compute和estimate子句)来收集统计信息。提供此功能只是为了向后兼容。

当向表中插入数据时，Oracle将分配由物理数据库块组成的区。Oracle还将为索引分配数据块。对于每个插入到表中的记录，Oracle还将创建一个包含rowid和列值的索引条目(本例中是rowid和last_name列的值)。每个索引项的rowid指向存储该表的列值的数据文件和数据块号。

当从一个表及其对应的索引选择数据时，存在三种情况。
.SQL查询所需的所有表的数据都在索引结构中。因此，只需要访问索引块。不需要从表中读取数据块。
.查询所需的所有信息没有都包含在索引块中。因此，查询优化器选择既访问索引块也要访问表块来检索需要的数据，以满足查询条件。
.查询优化器选择不访问索引。因此只访问表块。

场景1.所有的数据位于索引块中
这里将介绍两种情况。在每种情况下，执行查询需要的所有数据，包括返回给用户的数据，以及在where子句中被评估的数据，都位于该索引中。
.索引范围扫描(index range scan):如果优化器确定它使用索引结构检索查询所需的多个行时是有效的，那么就使用这种扫描。索引范围扫描被广泛用于各种各样的情况。

.索引快速全扫描(index fast full scan):如果优化器确定表中的大部分行需要进行检索，那么就使用这种扫描。但所有需要的信息都存储在索引中。由于索引结构通常比表结构小，优化器确定全索引扫描(比全表扫描)更高效。这种情况对统计(count)值的查询是很常见的。

首先演示的是索引范围扫描。在这种情况下，运行下面的查询:

select last_name from cust where last_name='Austin';

为了在该查询中返回数据，Oracle最小需要读取多少块，也就是说为了满足此查询，访问物理块最有效的方式是什么，优化器可以选择读取表结构的每个块。然而，这会导致很大的IO开销，因此，它不是检索数据的最优化方法。

对于这个例子，检索数据最有效的方法是使用索引结构。要返回包含last_name列中值为Austin的行，Oracle将需要读取3个索引块。通过使用Oracle的autotrace(自动跟踪)实用程序，可以确认。

SQL> set autotrace on
SQL> select last_name from cust where last_name='Austin';

LAST_NAME
------------------------------
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    10 |    80 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CUST_IDX1 |    10 |    80 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("LAST_NAME"='Austin')

此输出显示，Oracle只需要使用cust_idx1索引来检索数据，以满足查询的结果集。不需要访问表中的数据块，只需要访问索引块。这对于给定的查询，这是特别高效的索引策略。当索引包含查询所需的所有列值时，它被称为覆盖索引。

下面列出为这个例子使用自动跟踪所显示的统计信息:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        653  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

一致获取(consistent gets)的值表示有三个读内存操作。数据库块获取(db block gets)加一致获取等于总的内存读取操作。由于索引块已经在内存中，因此返回此查询的结果集不需要物理读取。此外，有10行进行了处理，这与cust表中last_name为Austin的记录数相符。

下面显示导致执行索引快速全扫描的一个例子。

select count(last_name) from cust;

使用set autotrace on生成执行计划。下面是相应的输出:

SQL> select count(last_name) from cust;

COUNT(LAST_NAME)
----------------
            1070


Execution Plan
----------------------------------------------------------
Plan hash value: 2246355899

-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     8 |            |          |
|   2 |   INDEX FAST FULL SCAN| CUST_IDX1 |  1070 |  8560 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

此输出显示，确定表内的计数只用到了索引结构。在这种情况下，优化器确定采取索引快速全扫描比全表扫描更高效。

Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
        559  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

场景2.索引中不包含所有信息
考虑这样一种情况:假设需要从cust表获得更多信息。首先，回顾一下前面的查询语句，并且还要在查询结果中返回first_name列。现在，要获得新增的数据元素，就需要访问表本身。下面是新的查询语句:

select last_name,first_name from cust where last_name='Austin';

使用set autotrace on,并执行前面的查询语句:

SQL> alter system flush buffer_cache;

System altered.

SQL> select last_name,first_name from cust where last_name='Austin';

LAST_NAME                      FIRST_NAME
------------------------------ ------------------------------
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David
Austin                         David

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2100940648

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |    10 |   150 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUST      |    10 |   150 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_IDX1 |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_NAME"='Austin')

此输出信息指示，cust_idx1索引是通过一次索引范围扫描(index range scan)访问的。索引范围扫描标识出满足此查询结果所需的索引块。此外，表是过table access by index rowid batched来读取的。通过索引的rowid访问表，表示Oracle利用存储在索引中的rowid找到表块包含的相应行。把rowid映射到相应的表块，这些块中含有last_name值为Austin的数据。由于我们清空了buffer cache了，这样查询共执行了6次物理读取，9次内存读取。

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          6  physical reads
          0  redo size
        896  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

场景3.只有表块被访问
在某些情况下，即使有索引存在，Oracle也会确定只使用表块比通过索引访问更为有效。当Oracle检查表内的每一行时，这被称为全表扫描。
例如，执行此查询:

SQL> select * from cust;

下面是相应的执行计划和统计信息:

Execution Plan
----------------------------------------------------------
Plan hash value: 260468903

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1070 | 19260 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUST |  1070 | 19260 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        204  recursive calls
          9  db block gets
        389  consistent gets
         20  physical reads
       1080  redo size
      38869  bytes sent via SQL*Net to client
       1405  bytes received via SQL*Net from client
         73  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
       1070  rows processed

此输出显示，需要一致读取(consistent gets)389个块以及数据库块获取(db block gets)9个块和物理读取20个块。Oracle检索表中的每一行以返回满足查询所需的结果。在这种情况下，必须读取表中已使用的所有块，Oracle无法使用索引来加快数据检索。

DM7使用DMRAMN执行备份集恢复

Posted on by YongJing(敬勇)

使用DMRAMN执行备份集恢复
使用RECOVER命令完成数据库恢复工作，可以是基于备份集的恢复工作，也可以是使用本地归档日志的恢复工作。如果还原后，数据已经处于一致性状态了，则可以使用更新DB_MAGIC方式恢复，前提是不需要重做日志。语法如下：

RECOVER DATABASE '' []
[USE DB_MAGIC ] [UNTIL TIME '< 时间串>'] [UNTIL LSN ]; |
RECOVER DATABASE '' FROM BACKUPSET '< 备份集目录>'[DEVICE TYPE DISK|TAPE[PARMS '< 介质参数>']] [IDENTIFIED BY
< 密码> [ENCRYPT WITH < 加密算法>]]
; |
RECOVER DATABASE '' UPDATE DB_MAGIC;
 ::=
WITH ARCHIVEDIR '< 归档日志目录>'{,'< 归档日志目录>'}

DATABASE：指定还原库目标的dm.ini文件路径。
USE DB_MAGIC：指定本地归档日志对应数据库的DB_MAGIC，若不指定，则默认使用目标恢复数据库的DB_MAGIC。
UNTIL TIME：恢复数据库到指定的时间点。
UNTIL LSN：恢复数据库到指定的LSN。
BACKUPSET：指定用于还原目标数据库的备份集目录。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。DISK表示存储备份集到磁盘，TAPE表示存储到磁带。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
IDENTIFIED BY：指定备份时使用的加密密码，供还原过程解密使用。
ENCRYPT WITH：指定备份时使用的加密算法，供还原过程解密使用，若未指定，则使用默认算法。
WITH ARCHIVEDIR：本地归档日志搜索目录，若未指定，则仅使用目标库配置本地归档目录，RAC环境还会取REMOTE归档目录。

数据库恢复有三种方式：更新DB_MAGIC恢复、从备份集恢复和从归档恢复。

从备份集恢复
如果备份集在备份过程中生成了日志，且这些日志在备份集中有完整备份（如联机数据库备份），在执行数据库还原后，可以重做备份集中备份的日志，将数据库恢复到备份时的状态，即从备份集恢复。完整的示例如下：
1) 启动DIsql联机备份数据库。

SQL> backup database full to db_full_bak_recover_bakset backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_bakset_backupset';
executed successfully
used time: 00:00:07.673. Execute id is 247.
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          1250320462

used time: 48.106(ms). Execute id is 282.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          708657636

used time: 37.277(ms). Execute id is 315.

2) 准备目标库，可以使用备份库，也可以重新生成库。这里是在别一台服务器上创建新库，重新生成库操作如下：

[dmdba@dmks ~]$ dminit path=/dm_home/dmdbms/data db_name=dameng_for_recover auto_overwrite=1 port_num=5336
initdb V7.1.6.46-Build(2018.02.08-89107)ENT
db version: 0x7000a
file dm.key not found, use default license!
License will expire in 14 day(s) on 2020-06-30

 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover01.log


 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover02.log

write to dir [/dm_home/dmdbms/data/dameng_for_recover].
create dm database success. 2020-06-16 16:03:18


[root@dmks root]# ./dm_service_installer.sh  -i /dm_home/dmdbms/data/dameng_for_recover/dm.ini -p dmrc -t dmserver
Move the service script file(/dm_home/dmdbms/bin/DmServicedmrc to /etc/rc.d/init.d/DmServicedmrc)
Finished to create the service (DmServicedmrc)

[root@dmks root]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]


[dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 10.727(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          450923536

used time: 1.042(ms). Execute id is 5.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          1009602608

used time: 1.332(ms). Execute id is 6.

3)将备份集复制到目标库所在主机上

[dmdba@shard1 bak]$ scp -r db_full_bak_recover_bakset_backupset/ dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
db_full_bak_recover_bakset_backupset.bak                                                                                                                                                                  100%  211MB 105.3MB/s   00:02
db_full_bak_recover_bakset_backupset_1.bak                                                                                                                                                                100%  225KB 225.0KB/s   00:00
db_full_bak_recover_bakset_backupset.meta                                                                                                                                                                 100%   93KB  92.5KB/s   00:00
[dmdba@shard1 bak]$

4)启动RMAN，校验备份。

RMAN> check backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
check backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
CMD END.CODE:[0]
check backupset successfully.
time used: 6.293(ms)

5)还原数据库。

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
RESTORE DATABASE  CHECK......
RESTORE DATABASE , dbf collect......
RESTORE DATABASE , dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset] START......
total 22 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 24 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 29564.812(ms)

6)恢复数据库。

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset';
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 19945240
RESTORE RLOG  CHECK......
RESTORE RLOG ,gen tmp file......
RESTORE RLOG FROM BACKUPSET [/dm_home/dmdbms/backup/db_full_bak_recover_bakset_backupset] START......
total 0 packages processed...
total 2 packages processed!
CMD END.CODE:[0]

EP[0] Recover LSN from 19945241 to 19947077.
Recover from archive log finished, time used:0.322s.
recover successfully!
time used: 7027.238(ms)

7)检查db_magic,db_maigc从708657636变成了1572156104

[root@dmks init.d]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]

[dmdba@dmks ~]$ disql SYSDBA/xxzx7817600@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 12.530(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          1250320462

used time: 1.288(ms). Execute id is 4.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          1572156104

used time: 1.845(ms). Execute id is 5.

8)检查数据

SQL>  select * from t1;

LINEID     ID          NAME
---------- ----------- ----
1          1           jy
2          2           hy

used time: 1.286(ms). Execute id is 5.

DM7使用DMRAMN执行更新DB_MAGIC恢复

Posted on by YongJing(敬勇)

使用DMRAMN执行更新DB_MAGIC恢复
使用RECOVER命令完成数据库恢复工作，可以是基于备份集的恢复工作，也可以是使用本地归档日志的恢复工作。如果还原后，数据已经处于一致性状态了，则可以使用更新DB_MAGIC方式恢复，前提是不需要重做日志。语法如下：

RECOVER DATABASE '' []
[USE DB_MAGIC ] [UNTIL TIME '< 时间串>'] [UNTIL LSN ]; |
RECOVER DATABASE '' FROM BACKUPSET '< 备份集目录>'[DEVICE TYPE DISK|TAPE[PARMS '< 介质参数>']] [IDENTIFIED BY
< 密码> [ENCRYPT WITH < 加密算法>]]
; |
RECOVER DATABASE '' UPDATE DB_MAGIC;
 ::=
WITH ARCHIVEDIR '< 归档日志目录>'{,'< 归档日志目录>'}

DATABASE：指定还原库目标的dm.ini文件路径。
USE DB_MAGIC：指定本地归档日志对应数据库的DB_MAGIC，若不指定，则默认使用目标恢复数据库的DB_MAGIC。
UNTIL TIME：恢复数据库到指定的时间点。
UNTIL LSN：恢复数据库到指定的LSN。
BACKUPSET：指定用于还原目标数据库的备份集目录。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。DISK表示存储备份集到磁盘，TAPE表示存储到磁带。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
IDENTIFIED BY：指定备份时使用的加密密码，供还原过程解密使用。
ENCRYPT WITH：指定备份时使用的加密算法，供还原过程解密使用，若未指定，则使用默认算法。
WITH ARCHIVEDIR：本地归档日志搜索目录，若未指定，则仅使用目标库配置本地归档目录，RAC环境还会取REMOTE归档目录。

数据库恢复有三种方式：更新DB_MAGIC恢复、从备份集恢复和从归档恢复，

更新DB_MAGIC恢复
当备份集为脱机备份即备份过程中无日志生成，那么此时还原后的数据库中数据与备份时数据状态一致。在不需要重做归档日志恢复数据的情况下，可以直接更新DB_MAGIC完成数据库恢复。完整的示例如下：

1) 启动RMAN备份数据库，保证服务器处于脱机状态。

RMAN> backup database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' full backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic';
backup database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' full backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic';
file dm.key not found, use default license!
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running...[4].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running...[3].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running...[2].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running...[1].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running...[0].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running, write dmrman info.
EP[0] max_lsn: 19945240
BACKUP DATABASE [jydm], execute......
CMD CHECK LSN......
BACKUP DATABASE [jydm], collect dbf......
CMD CHECK ......
DBF BACKUP SUBS......
total 1 packages processed...
total 7 packages processed...
total 13 packages processed...
total 14 packages processed...
total 15 packages processed...
total 16 packages processed...
total 21 packages processed...
total 22 packages processed...
DBF BACKUP MAIN......
BACKUPSET [/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic] END, CODE [0]......
META GENERATING......
total 26 packages processed...
total 26 packages processed!
CMD END.CODE:[0]
backup successfully!
time used: 15113.537(ms)

RMAN> show backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic';
show backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic';




system path:           /dm_home/dmdba/dmdbms/data/jydm
db magic:              708657636
permanent magic:       1250320462
rac node:              1
page check:            0
rlog encrypt:          0
external cipher[id/name]:  0/
external hash[id/name]:  0/
length in char:        0
use new hash:          1
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
unicode_flag/charset:  0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
archive flag:          1
blank_pad_mode:        0
crc_check:             TRUE
backupset sig:         BA
backupset version:     4009
database name:         jydm
backup name:           DB_FULL_jydm_20200617_192816_000091
backupset description:
backupset ID :         -1041017958
parent backupset ID:     -1
META file size :       86528
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          database
mpp_timestamp:         1592393290
ddl_clone:             FALSE
mpp_flag:              FALSE
backup level:          offline
backup type:           full
without log:           FALSE
START_LSN:             19945241
START_SEQ:             7798402
END_LSN:               19945240
END_SEQ:               7798401
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-17 19:28:25
min trx start lsn:     19945241
min exec ver:          0x0701060C
pkg size:              0x02000000


backupset directory: /dm_home/dmdba/dmdbms/data/jydm/bak/db_full_bak_recover_dbmagic
backupset name:        db_full_bak_recover_dbmagic
backup data file num:  8
backup piece num:      1


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |205431    |db_full_bak_recover_dbmagic.bak                         |DATA


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len
1         |0         |SYSTEM                           |0         |/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF|                                 |24117248
2         |1         |ROLL                             |0         |/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF|                                 |208666624
3         |4         |MAIN                             |0         |/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF|                                 |197132288
4         |5         |BOOKSHOP                         |0         |/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF|                                 |157286400
5         |6         |DMHR                             |0         |/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF|                                 |134217728
6         |7         |USERS                            |0         |/dm_home/dmdba/dmdbms/data/jydm/users01.dbf|                                 |52428800
7         |9         |SYSAUX                           |0         |/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF|                                 |176160768
8         |10        |FG_PERSON                        |0         |/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF|                                 |134217728


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time


show backupsets successfully.
time used: 3.792(ms)

将源库的数据库备份集传输到目标主机上

[dmdba@shard1 bak]$ scp -r db_full_bak_recover_dbmagic/ dmdba@10.13.13.187:/dm_home/dmdbms/backup/
dmdba@10.13.13.187's password:
db_full_bak_recover_dbmagic.bak                                                                                                                                                                           100%  201MB 100.3MB/s   00:02
db_full_bak_recover_dbmagic.meta                                                                                                                                                                          100%   85KB  84.5KB/s   00:00
[dmdba@shard1 bak]$

2) 准备目标库，可以使用备份库，也可以重新生成库。这里是在别一台服务器上创建新库，重新生成库操作如下：

./dminit path=/opt/dmdbms/data db_name=DAMENG_FOR_RESTORE auto_overwrite=1

但我这里目标库就是源库

3) 校验备份。

RMAN> check backupset '/dm_home/dmdbms/backup/db_full_bak_recover_dbmagic';
check backupset '/dm_home/dmdbms/backup/db_full_bak_recover_dbmagic';
CMD END.CODE:[0]
check backupset successfully.
time used: 6.093(ms)

4) 还原数据库。

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_dbmagic';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_recover_dbmagic';
file dm.key not found, use default license!
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
RESTORE DATABASE  CHECK......
RESTORE DATABASE , dbf collect......
RESTORE DATABASE , dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/db_full_bak_recover_dbmagic] START......
total 22 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 24 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 49968.844(ms)

5) 恢复数据库。

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' update db_magic;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' update db_magic;
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 19945240
recover successfully!
time used: 7041.721(ms)

6)检查db_magic值，可以确定在没有指定update db_magic选项时其实也是更新了db_maigc值。db_maigc值从708657636变成了-616506144

[root@dmks init.d]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]

[dmdba@dmks ~]$ disql SYSDBA/xxzx7817600@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 11.667(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46

SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          1250320462

used time: 0.305(ms). Execute id is 5.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          -616506144

used time: 1.948(ms). Execute id is 6.
Proudly powered by WordPress | Indrajeet by Sus Hill.