YongJing(敬勇) – 第15页 – 但行好事莫问前程

DM7使用DMRMAN执行归档修复

Posted on 2021年1月8日 by YongJing(敬勇)

使用DMRMAN执行归档修复
使用REPAIR命令完成指定数据库的归档修复，归档修复会将目标库dmarch.ini中配置的所有本地归档日志目录执行修复。若目标库没有配置本地归档，则不执行修复。执行修复时，目标库一定不能处于运行状态。一般建议在数据库故障后，应立即执行归档修复，否则后续还原恢复导致联机日志中未刷入本地归档的REDO日志丢失，届时再利用本地归档恢复将无法恢复到故障前的最新状态。

语法如下：

REPAIR DATABASE < 'INI文件路径'>;

INI文件路径：指定待修复归档的数据库对应的dm.ini路径。
使用说明：
1) 单机环境时，确保目标库已经停止工作后，执行归档修复。

RMAN> repair archivelog database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini';
repair archivelog database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini';
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: 20383733
repair archive log successfully.
repair time used: 6027.359(ms)
time used: 6027.438(ms)

2) RAC环境时，需要每个节点停止工作后，且每个节点独立执行修复操作。对于两节点RAC01、RAC02执行修复如下。

RMAN> repair archivelog database '/dm7/data/rac0_config/dm.ini';
repair archivelog database '/dm7/data/rac0_config/dm.ini';
checking if the RAC database under system path [+DMDATA/data/rac] is running....
EP [0] is checking....
EP [1] is checking....
EP[0] max_lsn: 80140
repair archive log successfully.
repair time used: 2237.302(ms)
time used: 2237.565(ms)

RMAN> repair archivelog database '/dm7/data/rac1_config/dm.ini';
repair archivelog database '/dm7/data/rac1_config/dm.ini';
file dm.key not found, use default license!
checking if the RAC database under system path [+DMDATA/data/rac] is running....
EP [0] is checking....
EP [1] is checking....
EP[1] max_lsn: 80023
EP[1] adjust cur_lsn from [80023] to [80140]
repair archive log successfully.
repair time used: 2262.513(ms)
time used: 2262.719(ms)

DM7使用DMRMAN执行归档还原

Posted on 2021年1月8日 by YongJing(敬勇)

使用DMRMAN执行归档还原
使用RESTORE命令完成脱机还原归档操作，在还原语句中指定归档备份集。备份集可以是脱机归档备份集，也可以是联机归档备份集。
语法如下：

RESTORE FROM BACKUPSET '< 备份集目录>'
[DEVICE TYPE DISK|TAPE[PARMS '< 介质参数>']]
[IDENTIFIED BY < 密码> [ENCRYPT WITH < 加密算法>]]
[TASK THREAD < 任务线程数>] [NOT PARALLEL]
[ALL | [FROM LSN ] | [UNTIL LSN ] | [LSN BETWEEN < lsn值> AND < lsn值>] | [FROM TIME '时间串'] | [UNTIL TIME '时间串'] | [TIME BETWEEN '时间串' AND '时间串'] ]
TO < 还原目录>
[OVERWRITE level];
< 还原目录>：：=
ARCHIVEDIR '< 归档目录>' |
DATABASE ''

BACKUPSET：指定用于还原目标数据库的备份集目录。若指定为相对路径，会在默认备份目录下搜索备份集。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。DISK表示备份集存储介质为磁盘，TAPE表示存储介质为磁带。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
IDENTIFIED BY：指定备份时使用的加密密码，供还原过程解密使用。
ENCRYPT WITH：指定备份时使用的加密算法，供还原过程解密使用，若未指定，则使用默认算法。
TASK THREAD：指定还原过程中用于处理解压缩和解密任务的线程个数。若未指定，则默认为4；若指定为0，调整为1；若指定超过当前系统主机核数，则调整为当前核数。
NOT PARALLEL：指定并行备份集使用非并行方式还原。对于非并行备份集，不论是否指定该关键字，均采用非并行还原。
ALL：备份所有的归档。
FROM LSN， FROM TIME：指定备份的开始LSN或者开始的时间点。
UNTIL LSN， UNTIL TIME：指定备份的截止LSN或者截止的时间点。
BETWEEN … AND …：指定备份的区间，仅仅指备份区间内的归档文件。
< 还原目录>：用户可以指定还原到指定的目录ARCHIVEDIR，也可以指定还原指定的库的目录DATABASE。
OVERWRITE：还原归档时，如果遇到归档已经存在的处理，1：跳过已存在的归档日志，继续其他日志的还原。跳过的信息会生成一条日志记录在安装目录/log/dm_BAKRES_年月.log里；2：直接报错返回；3：强制覆盖存在的归档日志。缺省为1。

使用说明：
1) 联机备份归档，保证数据库运行在归档模式及OPEN状态。

SQL> backup archivelog all backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore';
executed successfully
used time: 00:00:44.498. Execute id is 208.

2) 校验备份，校验待还原备份集的合法性。校验备份有两种方式，联机和脱机，此处使用脱机校验。

RMAN> check backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore';
check backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore';
CMD END.CODE:[0]
check backupset successfully.
time used: 18.193(ms)

3) 还原归档。启动DMRMAN，设置OVERWRITE为2，如果归档文件已存在，会报错。

RMAN> restore archivelog from backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore' to database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' overwrite 2;
restore archivelog from backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore' to database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' overwrite 2;
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].
the database under system path [/dm_home/dmdba/dmdbms/data/jydm] is running.
[-102]:DM server is already running

[root@shard1 jydm]# service DmServicejydm stop
Redirecting to /bin/systemctl stop  DmServicejydm.service


RMAN> restore archivelog from backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore' to database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' overwrite 2;
restore archivelog from backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore' to database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' overwrite 2;
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.
RESTORE ARCHIVE CHECK......
RESTORE ARCHIVE, dbf collect......
RESTORE ARCHIVE, dbf refresh ......
CMD END.CODE:[-8354],DESC:[for the archive file conflicted, restore failed]
[-8354]:for the archive file conflicted, restore failed

或者
RMAN> restore archivelog from backupset ‘/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore’ to archivedir ‘/dm_home/dmdba/dmdbms/data/jydm/bak/arch’ overwrite 2;
restore archivelog from backupset ‘/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore’ to archivedir ‘/dm_home/dmdba/dmdbms/data/jydm/bak/arch’ overwrite 2;
RESTORE ARCHIVE CHECK……
RESTORE ARCHIVE, dbf collect……
RESTORE ARCHIVE, dbf refresh ……
RESTORE BACKUPSET [/dm_home/dmdba/dmdbms/data/jydm/bak/arch_all_for_restore] START……
total 30 packages processed…
total 67 packages processed…
total 68 packages processed!
CMD END.CODE:[0]

restore successfully.
time used: 28142.800(ms)

DM7使用DMRAMN对多次故障恢复后使用不同数据库的归档进行恢复

Posted on 2021年1月6日2021年1月6日 by YongJing(敬勇)

使用DMRAMN对多次故障恢复后使用不同数据库的归档进行恢复
在实际应用中可能会遇到以下还原场景：
1) 创建一个数据库D1。
2) 操作数据库并执行数据库备份B1。
3) 继续操作数据库的过程中数据库故障，此时生成的归档为A1。
4) 利用备份B1和归档A1将数据库D1恢复到目标库D2，此时数据库D2为D1故障前的状态。
5) 启动数据库D2，操作数据库过程中数据库第二次故障，此时生成的归档为A2。
归档A1、A2属于不同的数据库，使用备份B1和归档A1、A2是否可以恢复数据库到第二次故障发生前的状态呢?下面将举例详细说明如何使用不同数据库的归档恢复数据库到最新状态，具体步骤如下：

1) 创建源库D1，即待备份的数据库，然后启动数据库并配置归档，归档目录为1) 创建源库D1，即待备份的数据库，然后启动数据库并配置归档，归档目录为/dm_home/dmdba/dmdbms/data/d1_arch。

[dmdba@shard1 data]$ dminit path=/dm_home/dmdba/dmdbms/data db_name=d1 instance_name=d1 port_num=5237 unicode_flag=0
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 on 2020-07-03

 log file path: /dm_home/dmdba/dmdbms/data/d1/d101.log


 log file path: /dm_home/dmdba/dmdbms/data/d1/d102.log

write to dir [/dm_home/dmdba/dmdbms/data/d1].
create dm database success. 2020-06-19 16:47:51


[root@shard1 root]# ./dm_service_installer.sh -i /dm_home/dmdba/dmdbms/data/d1/dm.ini -p d1 -t dmserver
ln -s '/usr/lib/systemd/system/DmServiced1.service' '/etc/systemd/system/multi-user.target.wants/DmServiced1.service'
Finished to create the service (DmServiced1)
[root@shard1 root]# service DmServiced1 start
Redirecting to /bin/systemctl start  DmServiced1.service

–连接数据库配置归档

[dmdba@shard1 ~]$ disql SYSDBA/SYSDBA@localhost:5237

Server[localhost:5237]:mode is normal, state is open
login used time: 9.972(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> alter database mount;
executed successfully
used time: 00:00:01.845. Execute id is 0.
SQL> alter database add archivelog 'dest=/dm_home/dmdba/dmdbms/data/d1_arch,type=local,file_size=256,space_limit=10240';
executed successfully
used time: 22.257(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 31.716(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 823.762(ms). Execute id is 0.
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          -836921674

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

LINEID     DB_MAGIC
---------- -----------
1          -811309012

used time: 1.365(ms). Execute id is 808.

2) 操作数据库的同时备份数据库，备份集为B1。此处以向表中循环插入数据为例操作数据库。

SQL> create table tab_for_recover_01(c1 int);
executed successfully
used time: 22.960(ms). Execute id is 809.
SQL> begin
2   for i in 1 .. 100000 loop
3    insert into tab_for_recover_01 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL executed successfully
used time: 00:00:01.612. Execute id is 810.

插入数据的同时，另外一个会话备份数据库

SQL> backup database full to b1 backupset '/dm_home/dmdba/dmdbms/backup/b1' device type disk backupinfo 'dameng database full backup online' maxpiecesize 2048;
executed successfully
used time: 00:00:01.920. Execute id is 813.

3) 继续向数据库插入数据一段时间后杀掉服务器，模拟服务器故障。从备份后到服务器故障这段时间的生成的归档称为A1。

SQL> begin
2   for i in 100001 .. 199999 loop
3    insert into tab_for_recover_01 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL executed successfully
used time: 00:00:01.633. Execute id is 814.

[root@shard1 root]# ps -ef | grep dmserver
dmdba     9749     1  1 16:49 ?        00:00:09 /dm_home/dmdba/dmdbms/bin/dmserver /dm_home/dmdba/dmdbms/data/d1/dm.ini -noconsole
root     13497  9392  0 17:03 pts/11   00:00:00 grep --color=auto dmserver
[root@shard1 root]# kill -9 9749
[root@shard1 root]# ps -ef | grep dmserver
root     13598  9392  0 17:04 pts/11   00:00:00 grep --color=auto dmserver

4) 创建目标库D2，即待还原的库，并配置归档（参考3.1.2小节），归档目录与数据库D1相同。

[dmdba@shard1 data]$ dminit path=/dm_home/dmdba/dmdbms/data db_name=d2 instance_name=d2 port_num=5238 unicode_flag=0
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 on 2020-07-03

 log file path: /dm_home/dmdba/dmdbms/data/d2/d201.log


 log file path: /dm_home/dmdba/dmdbms/data/d2/d202.log

write to dir [/dm_home/dmdba/dmdbms/data/d2].
create dm database success. 2020-06-19 17:11:54

[root@shard1 root]# ./dm_service_installer.sh -i /dm_home/dmdba/dmdbms/data/d2/dm.ini -p d2 -t dmserver
ln -s '/usr/lib/systemd/system/DmServiced2.service' '/etc/systemd/system/multi-user.target.wants/DmServiced2.service'
Finished to create the service (DmServiced2)

[root@shard1 root]# service DmServiced2 start
Redirecting to /bin/systemctl start  DmServiced2.service


[dmdba@shard1 ~]$ disql SYSDBA/SYSDBA@localhost:5238

Server[localhost:5238]:mode is normal, state is open
login used time: 9.862(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> alter database mount;
executed successfully
used time: 00:00:01.844. Execute id is 0.
SQL> alter database add archivelog 'dest=/dm_home/dmdba/dmdbms/data/d2_arch,type=local,file_size=256,space_limit=10240';
executed successfully
used time: 40.105(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 31.716(ms). Execute id is 0.
SQL> alter database open;
executed successfully
used time: 675.370(ms). Execute id is 0.

5) 启动DMRMAN工具，利用备份集B1和归档A1恢复数据库D2到D1故障前的状态。

[root@shard1 root]# service DmServiced2 stop
Redirecting to /bin/systemctl stop  DmServiced2.service

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

total redo pages:32768

EP[0] Recover LSN from 134426 to 235404.
Recover from archive log finished, time used:0.910s.
recover successfully!
time used: 8038.102(ms)

6)启动目标库D2，向数据库中继续插入数据。

[root@shard1 root]# service DmServiced2 start
Redirecting to /bin/systemctl start  DmServiced2.service


[dmdba@shard1 ~]$ disql SYSDBA/SYSDBA@localhost:5238

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

LINEID     PERMANENT_MAGIC
---------- ---------------
1          -836921674

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

LINEID     DB_MAGIC
---------- -----------
1          1621992964

used time: 1.784(ms). Execute id is 4.

恢复后目标库的db_magic与源库的不一样，因为每次恢复后都会发生改变。

SQL> select count(*) from tab_for_recover_01;

LINEID     COUNT(*)
---------- --------------------
1          199999

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


SQL> begin
2   for i in 200000 .. 300000 loop
3    insert into tab_for_recover_01 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL executed successfully
used time: 00:00:01.274. Execute id is 7.

7) 插入数据一段时间后，杀掉服务器，模拟服务器第二次故障。数据库D2启动到第二次故障之间产生的归档为A2。

SQL> select count(*) from tab_for_recover_01;

LINEID     COUNT(*)
---------- --------------------
1          300000

used time: 0.387(ms). Execute id is 8.

[root@shard1 root]# ps -ef | grep dmserver
dmdba    23093     1  1 17:49 ?        00:00:01 /dm_home/dmdba/dmdbms/bin/dmserver /dm_home/dmdba/dmdbms/data/d2/dm.ini -noconsole
root     23629  9392  0 17:51 pts/11   00:00:00 grep --color=auto dmserver
[root@shard1 root]# kill -9 23093
[root@shard1 root]# ps -ef | grep dmserver
root     23701  9392  0 17:51 pts/11   00:00:00 grep --color=auto dmserver

8) 恢复目标库D2到第一次故障前的状态。恢复一次，目标库的DB_MAGIC都会改变，因此归档A1、A2的DB_MAGIC不同，属于不同的数据库。第一次恢复只能先重做归档A1，即恢复到第一次故障前的状态。

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

total redo pages:32768

total redo pages:50909


total redo pages:25326


EP[0] Recover LSN from 134426 to 339536.
Recover from archive log finished, time used:1.557s.
recover successfully!
time used: 8040.986(ms)

9) 查看归档A2的DB_MAGIC。

[dmdba@shard1 d2_arch]$ ls -lrt
总用量 12800
-rw-r--r-- 1 dmdba dinstall  13093376 6月  19 17:51 ARCHIVE_LOCAL1_20200619174912579_0.log
-rw-r--r-- 1 dmdba dinstall 268435456 6月  19 17:51 ARCHIVE_LOCAL1_20200619175139125_0.log
[dmdba@shard1 d2_arch]$ dmrachk arch_fil=/dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619174912579_0.log
rachk V7.1.6.46-Build(2018.02.08-89107)ENT
/*******************************************************************/
archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619174912579_0.log itemize.
version        : 0x7004
status         : INACTIVE
n_rpags        : 25565
db_magic       : 1621992964
pemnt_magic    : -836921674
arch_lsn       : 339537
arch_seq       : 0
clsn           : 442123
next_seq       : 25564
file len       : 13093376
file free      : 13093376
create time    : 2020-06-19 17:49:12
close time     : 2020-06-19 17:51:39
crc_check      : TRUE
/*******************************************************************/

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

the rachk tool running cost 0.691 ms
[dmdba@shard1 d2_arch]$ dmrachk arch_fil=/dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619175139125_0.log
rachk V7.1.6.46-Build(2018.02.08-89107)ENT
/*******************************************************************/
archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619175139125_0.log itemize.
version        : 0x7004
status         : ACTIVE
n_rpags        : 0
db_magic       : 1621992964
pemnt_magic    : -836921674
arch_lsn       : 0
arch_seq       : 0
clsn           : 0
next_seq       : 0
file len       : 268435456
file free      : 4096
create time    : 2020-06-19 17:51:39
close time     : 2020-06-19 17:51:39
crc_check      : TRUE
/*******************************************************************/
check archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619175139125_0.log, encounter 1 error, as follow:

the archive file /dm_home/dmdba/dmdbms/data/d2_arch/ARCHIVE_LOCAL1_20200619175139125_0.log encounter no data.


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

the rachk tool running cost 0.267 ms

10) 修改目标库D2的DB_MAGIC与归档A2一致。若要利用归档恢复，必须保证数据库的DB_MAGIC与归档的DB_MAGIC一致。由步骤8的查询可知，归档A2的DB_MAGIC为1449535319。修改数据库的DB_MAGIC需要修改SYSTEM.DBF和DAMENG_FOR_RES01.log两个文件，步骤如下：
–修改文件SYSTEM.DBF

[dmdba@shard1 d2_arch]$ dmmdf type=1 file=/dm_home/dmdba/dmdbms/data/d2/SYSTEM.DBF
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 db_magic=-779140962
2 next_trxid=1127
3 pemnt_magic=-836921674
**********************************************************
Please input which parameter you want to change(1-3), q to quit: 1
Please input new value: 1621992964
**********************************************************
1 db_magic=1621992964
2 next_trxid=1127
3 pemnt_magic=-836921674
**********************************************************
Do you want to save the change to file (y/n): y
Save to file success!

–修改文件d201.log

[dmdba@shard1 d2_arch]$ dmmdf type=2 file=/dm_home/dmdba/dmdbms/data/d2/d201.log
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 1
6 n_magic = 14
7 db_magic = -779140962
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 339536
15 clsn_off = 4096
16 arch_lsn = 0
17 pemnt_magic = -836921674
18 fil_id = 0
19 crc_check = 1
**********************************************************
You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18).
Please input the num which one you want to change, q to quit: 7
Input the new value: 1621992964
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 1
6 n_magic = 14
7 db_magic = 1621992964
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 339536
15 clsn_off = 4096
16 arch_lsn = 0
17 pemnt_magic = -836921674
18 fil_id = 0
19 crc_check = 1
**********************************************************
Do you want to quit and save the change to file (y/n): y
Save to file success!

–修改文件d202.log

[dmdba@shard1 d2_arch]$ dmmdf type=2 file=/dm_home/dmdba/dmdbms/data/d2/d202.log
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 14
7 db_magic = -779140962
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 0
15 clsn_off = 0
16 arch_lsn = 0
17 pemnt_magic = -836921674
18 fil_id = 1
19 crc_check = 1
**********************************************************
You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18).
Please input the num which one you want to change, q to quit: 7
Input the new value: 1621992964
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 14
7 db_magic = 1621992964
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 0
15 clsn_off = 0
16 arch_lsn = 0
17 pemnt_magic = -836921674
18 fil_id = 1
19 crc_check = 1
**********************************************************
Do you want to quit and save the change to file (y/n): y
Save to file success!

11) 利用归档A2恢复数据库至第二次故障前的状态。

RMAN> recover database '/dm_home/dmdba/dmdbms/data/d2/dm.ini' with archivedir '/dm_home/dmdba/dmdbms/data/d2_arch';
recover database '/dm_home/dmdba/dmdbms/data/d2/dm.ini' with archivedir '/dm_home/dmdba/dmdbms/data/d2_arch';
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running...[4].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running...[3].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running...[2].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running...[1].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running...[0].
checking if the database under system path [/dm_home/dmdba/dmdbms/data/d2] is running, write dmrman info.
EP[0] max_lsn: 339536

EP[0] Recover LSN from 339537 to 442123.
Recover from archive log finished, time used:1.077s.
recover successfully!
time used: 8048.396(ms)

12)验证数据

[root@shard1 root]# service DmServiced2 start
Redirecting to /bin/systemctl start  DmServiced2.service

SQL> select count(*) from tab_for_recover_01;

LINEID     COUNT(*)
---------- --------------------
1          300000

used time: 3.022(ms). Execute id is 3.

DM7使用DMRMAN执行RAC数据库恢复

Posted on 2020年12月24日 by YongJing(敬勇)

使用DMRMAN执行RAC数据库恢复
DM7支持RAC环境下的备份还原。RAC环境的备份还原与普通的单机不同的是，它包含多个节点（目前仅支持两个节点，下面以从归档恢复为例说明RAC环境下的备份恢复：
1) 搭建RAC环境。
2) 启动DIsql，联机备份数据库。备份其中任意一个节点即可备份整个RAC环境。

SQL> backup database full to db_full_bak_for_rac backupset '/dm7/backup/db_full_bak_for_rac';
executed successfully
used time: 00:00:01.271. Execute id is 45.

3)将备份文件与两个节点的归档日志传输到目标库

[dmdba@dmrac1 backup]$ scp -r db_full_bak_for_rac/ dmdba@10.13.30.187:/dm_home/dmdbms/backup/
dmdba@10.13.30.187's password:
db_full_bak_for_rac.bak                                                                                                                                                                                   100%   26MB  25.5MB/s   00:01
db_full_bak_for_rac_1.bak                                                                                                                                                                                 100% 5120     5.0KB/s   00:00
db_full_bak_for_rac.meta                                                                                                                                                                                  100%   81KB  80.5KB/s   00:00

[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.13.30.187:/dm_home/dmdbms/backup/
dmdba@10.13.30.187's password:
ARCHIVE_LOCAL1_20200617163125381_0.log                                                                                                                                                                    100%  256MB 128.0MB/s   00:02
ARCHIVE_LOCAL1_20200617154121539_0.log                                                                                                                                                                    100% 8704     8.5KB/s   00:00
ARCHIVE_LOCAL1_20200615164953273_0.log                                                                                                                                                                    100%  160KB 160.0KB/s   00:00
ARCHIVE_LOCAL1_20200615165648166_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200528202150715_0.log                                                                                                                                                                    100%  277KB 277.0KB/s   00:00
ARCHIVE_LOCAL1_20200608151836879_0.log                                                                                                                                                                    100%  158KB 157.5KB/s   00:00
ARCHIVE_LOCAL1_20200609150852829_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200608152638617_0.log                                                                                                                                                                    100%  159KB 158.5KB/s   00:00
ARCHIVE_LOCAL1_20200609170732487_0.log                                                                                                                                                                    100%  161KB 160.5KB/s   00:00
ARCHIVE_LOCAL1_20200615172117341_0.log                                                                                                                                                                    100%  159KB 158.5KB/s   00:00
ARCHIVE_LOCAL1_20200615171042444_0.log                                                                                                                                                                    100%  159KB 159.0KB/s   00:00
ARCHIVE_LOCAL1_20200605154214367_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200616164818700_0.log                                                                                                                                                                    100%  160KB 160.0KB/s   00:00
[dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.13.30.187:/dm_home/dmdbms/backup/
dmdba@10.13.30.187's password:
ARCH_REMOTE1_20200608152641970_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200609170737070_1.log                                                                                                                                                                      100%  159KB 159.0KB/s   00:00
ARCH_REMOTE1_20200609150857056_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200616164822181_1.log                                                                                                                                                                      100%  256MB 128.0MB/s   00:02
ARCH_REMOTE1_20200615172121038_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200608151838201_1.log                                                                                                                                                                      100% 5632     5.5KB/s   00:00
ARCH_REMOTE1_20200528202146001_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200615164957743_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200605154211189_1.log                                                                                                                                                                      100%  159KB 159.0KB/s   00:00
ARCH_REMOTE1_20200615165652504_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200615171046600_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00

4)还原数据库。还原数据库之前可选择对备份文件进行校验。需要注意的是，待还原的目标库可以单机库，也可以RAC库，且节点个数允许不同。这里目标库为单机。

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_for_rac';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_full_bak_for_rac';
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_for_rac] START......
total 5 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 6 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 16608.914(ms)

5)恢复数据库。假设节点1和节点2的归档日志在目标库中分别在/dm_home/dmdbms/backup/rac0_arch , /dm_home/dmdbms/backup/rac1_arch目录下。RAC库恢复要求各节点归档完整性由用户保证，即各节点的本地归档都能够访问到，若本地存在REMOTE归档，则可以使用REMOTE归档代替远程节点的本地归档。

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch';
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_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.
EP[0] max_lsn: 80023
EP 0's ckpt_lsn = 80135
min_ckpt_lsn = 80135
The RAC redo archive log 1 ptx
The RAC redo archive log 2 ptx
The RAC recover total redo 2 ptx
EP(0) slot ctl page(1, 0, 16) trxid = 10107
EP(1) slot ctl page(1, 0, 17) trxid = 8457
EP[0] Recover LSN from 80135 to 80136.
EP[1] Recover LSN from 80024 to 80023.
Recover from archive log finished, time used:0.025s.
recover successfully!
time used: 7039.164(ms)

6)验证数据

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

SQL> select * from tab_for_recover_01;
no rows

used time: 0.373(ms). Execute id is 4.
SQL> select * from tab_for_recover_02;

LINEID     C1
---------- -----------
1          1

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

DM7使用DMRMAN恢复数据库到指定时间点/LSN

Posted on 2020年12月11日 by YongJing(敬勇)

使用DMRMAN恢复数据库到指定时间点/LSN
恢复数据库到指定时间点/LSN是从归档恢复的一种方式，也称为不完全恢复。从归档恢复允许恢复到指定的时间点及指定的LSN值。若同时指定了时间点和LSN，则以较早的为结束点。用户可以通过指定一个时间点/LSN，使数据库恢复到这个指定的时间点/LSN。

例如，用户在下午5点做了一个误操作，删除了某些重要数据；我们可以指定恢复时间点到下午4:59分，恢复被误删除的数据。
下面以联机数据库备份为例说明如何恢复数据库到指定的时间点/LSN。
1) 准备数据。

SQL> create table tab_for_recover_01(c1 int);
executed successfully
used time: 62.140(ms). Execute id is 29.
SQL> insert into tab_for_recover_01 values(1);
affect rows 1

used time: 1.765(ms). Execute id is 30.
SQL> commit;
executed successfully
used time: 1.582(ms). Execute id is 31.

2) 备份数据库。

SQL> backup database full to db_rac_full_bak_for_time_lsn backupset '/dm7/backup/dm_rac_full_bak_for_time_lsn';
executed successfully
used time: 00:00:01.687. Execute id is 32.

3) 正确操作数据库，产生一些归档。

SQL> create table tab_for_recover_02(c1 int);
executed successfully
used time: 12.213(ms). Execute id is 33.
SQL> insert into tab_for_recover_02 values(1);
affect rows 1

used time: 1.654(ms). Execute id is 34.
SQL> commit;
executed successfully
used time: 1.331(ms). Execute id is 35.

使用select sysdate命令查询此时的时间为：2020-06-19 15:23:17.809757

SQL> select sysdate;

LINEID     SYSDATE
---------- ---------------------------
1          2020-06-19 15:23:17.809757

used time: 1.571(ms). Execute id is 36.

使用select file_lsn from v$rlog命令查询此时的LSN为:80126

SQL> select file_lsn from v$rlog;

LINEID     FILE_LSN
---------- --------------------
1          80126

used time: 1.490(ms). Execute id is 37.

4) 误操作数据库。此步骤误删除了表tab_for_recover_01中数据。

SQL> delete from tab_for_recover_01;
affect rows 1

used time: 1.798(ms). Execute id is 38.
SQL> commit;
executed successfully
used time: 1.435(ms). Execute id is 39.

使用select sysdate命令查询此时的时间为：2020-06-19 15:25:56.982456

SQL> select sysdate;

LINEID     SYSDATE
---------- ---------------------------
1          2020-06-19 15:25:56.982456

used time: 0.898(ms). Execute id is 40.

使用select file_lsn from v$rlog命令查询此时的LSN为:80131

SQL> select file_lsn from v$rlog;

LINEID     FILE_LSN
---------- --------------------
1          80131

used time: 0.536(ms). Execute id is 41.

5)将备份文件与归档日志传输到目标库(这里不在源库进行恢复)

[dmdba@dmrac1 backup]$ scp -r dm_rac_full_bak_for_time_lsn/ dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
dm_rac_full_bak_for_time_lsn.bak                                                                                                                                                                          100%   25MB  25.5MB/s   00:00
dm_rac_full_bak_for_time_lsn_1.bak                                                                                                                                                                        100%   20KB  20.0KB/s   00:00
dm_rac_full_bak_for_time_lsn.meta                                                                                                                                                                         100%   81KB  80.5KB/s   00:00
[dmdba@dmrac1 backup]$

这里是RAC环境，需要将两个实例的归档都要传输到目标库

[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/
dmdba@10.18.10.187's password:
ARCHIVE_LOCAL1_20200617163125381_0.log                                                                                                                                                                    100%  256MB  85.3MB/s   00:03
ARCHIVE_LOCAL1_20200617154121539_0.log                                                                                                                                                                    100% 8704     8.5KB/s   00:00
ARCHIVE_LOCAL1_20200615164953273_0.log                                                                                                                                                                    100%  160KB 160.0KB/s   00:00
ARCHIVE_LOCAL1_20200615165648166_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200528202150715_0.log                                                                                                                                                                    100%  277KB 277.0KB/s   00:00
ARCHIVE_LOCAL1_20200608151836879_0.log                                                                                                                                                                    100%  158KB 157.5KB/s   00:00
ARCHIVE_LOCAL1_20200609150852829_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200608152638617_0.log                                                                                                                                                                    100%  159KB 158.5KB/s   00:00
ARCHIVE_LOCAL1_20200609170732487_0.log                                                                                                                                                                    100%  161KB 160.5KB/s   00:00
ARCHIVE_LOCAL1_20200615172117341_0.log                                                                                                                                                                    100%  159KB 158.5KB/s   00:00
ARCHIVE_LOCAL1_20200615171042444_0.log                                                                                                                                                                    100%  159KB 159.0KB/s   00:00
ARCHIVE_LOCAL1_20200605154214367_0.log                                                                                                                                                                    100%  157KB 157.0KB/s   00:00
ARCHIVE_LOCAL1_20200616164818700_0.log                                                                                                                                                                    100%  160KB 160.0KB/s   00:00
[dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.18.10.187:/dm_home/dmdbms/backup/
dmdba@10.18.10.187's password:
ARCH_REMOTE1_20200608152641970_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200609170737070_1.log                                                                                                                                                                      100%  159KB 159.0KB/s   00:00
ARCH_REMOTE1_20200609150857056_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200616164822181_1.log                                                                                                                                                                      100%  256MB  85.3MB/s   00:03
ARCH_REMOTE1_20200615172121038_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200608151838201_1.log                                                                                                                                                                      100% 5632     5.5KB/s   00:00
ARCH_REMOTE1_20200528202146001_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200615164957743_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200605154211189_1.log                                                                                                                                                                      100%  159KB 159.0KB/s   00:00
ARCH_REMOTE1_20200615165652504_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
ARCH_REMOTE1_20200615171046600_1.log                                                                                                                                                                      100%  158KB 158.0KB/s   00:00
[dmdba@dmrac1 data]$

6) 还原数据库。步骤4为误操作，因此我们需要将数据库恢复到步骤3的状态。首先需要还原数据库:

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/dm_rac_full_bak_for_time_lsn';
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/dm_rac_full_bak_for_time_lsn] START......
total 5 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 6 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 16581.139(ms)

7) 恢复数据库到指定时间点/LSN。还原后数据库的数据与备份时一致，如果要恢复数据库至步骤3的状态可以指定UNTIL TIME或UNTIL LSN参
数重做部分归档。
使用RECOVER DATABASE…UNTIL TIME命令恢复到指定的时间：

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757';
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until time '2020-06-19 15:23:17.809757';
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: 80023
EP 0's ckpt_lsn = 80054
min_ckpt_lsn = 80054
The RAC redo archive log 1 ptx
The RAC redo archive log 2 ptx
The RAC redo archive log 3 ptx
The RAC redo archive log 4 ptx
The RAC redo archive log 5 ptx
The RAC redo archive log 6 ptx
The RAC redo archive log 7 ptx
The RAC redo archive log 8 ptx
The RAC redo archive log 9 ptx
The RAC redo archive log 10 ptx
The RAC redo archive log 11 ptx
The RAC redo archive log 12 ptx
The RAC redo archive log 13 ptx
The RAC redo archive log 14 ptx
The RAC redo archive log 15 ptx
The RAC redo archive log 16 ptx
The RAC redo archive log 17 ptx
The RAC redo archive log 18 ptx
The RAC redo archive log 19 ptx
The RAC redo archive log 20 ptx
The RAC redo archive log 21 ptx
The RAC redo archive log 22 ptx
The RAC redo archive log 23 ptx
The RAC redo archive log 24 ptx
The RAC redo archive log 25 ptx
The RAC redo archive log 26 ptx
The RAC redo archive log 27 ptx
The RAC redo archive log 28 ptx
The RAC redo archive log 29 ptx
The RAC redo archive log 30 ptx
The RAC redo archive log 31 ptx
The RAC redo archive log 32 ptx
The RAC redo archive log 33 ptx
The RAC redo archive log 34 ptx
The RAC redo archive log 35 ptx
The RAC redo archive log 36 ptx
The RAC redo archive log 37 ptx
The RAC redo archive log 38 ptx
The RAC redo archive log 39 ptx
The RAC redo archive log 40 ptx
The RAC redo archive log 41 ptx
The RAC redo archive log 42 ptx
The RAC redo archive log 43 ptx
The RAC redo archive log 44 ptx
The RAC redo archive log 45 ptx
The RAC redo archive log 46 ptx
The RAC redo archive log 47 ptx
The RAC redo archive log 48 ptx
The RAC redo archive log 49 ptx
The RAC redo archive log 50 ptx
The RAC redo archive log 51 ptx
The RAC redo archive log 52 ptx
The RAC redo archive log 53 ptx
The RAC redo archive log 54 ptx
The RAC redo archive log 55 ptx
The RAC redo archive log 56 ptx
The RAC redo archive log 57 ptx
The RAC redo archive log 58 ptx
The RAC redo archive log 59 ptx
The RAC redo archive log 60 ptx
The RAC redo archive log 61 ptx
The RAC redo archive log 62 ptx
The RAC redo archive log 63 ptx
The RAC redo archive log 64 ptx
The RAC redo archive log 65 ptx
The RAC redo archive log 66 ptx
The RAC redo archive log 67 ptx
The RAC redo archive log 68 ptx
The RAC redo archive log 69 ptx
The RAC redo archive log 70 ptx
The RAC redo archive log 71 ptx
The RAC redo archive log 72 ptx
The RAC redo archive log 73 ptx
The RAC redo archive log 74 ptx
The RAC redo archive log 75 ptx
The RAC redo archive log 76 ptx
The RAC redo archive log 77 ptx
The RAC redo archive log 78 ptx
The RAC redo archive log 79 ptx
The RAC redo archive log 80 ptx
The RAC redo archive log 81 ptx
The RAC redo archive log 82 ptx
The RAC redo archive log 83 ptx
The RAC redo archive log 84 ptx
The RAC redo archive log 85 ptx
The RAC redo archive log 86 ptx
The RAC redo archive log 87 ptx
The RAC redo archive log 88 ptx
The RAC redo archive log 89 ptx
The RAC redo archive log 90 ptx
The RAC redo archive log 91 ptx
The RAC redo archive log 92 ptx
The RAC redo archive log 93 ptx
The RAC redo archive log 94 ptx
The RAC redo archive log 95 ptx
The RAC redo archive log 96 ptx
The RAC redo archive log 97 ptx
The RAC redo archive log 98 ptx
The RAC redo archive log 99 ptx
The RAC redo archive log 100 ptx
The RAC redo archive log 101 ptx
The RAC redo archive log 102 ptx
The RAC redo archive log 103 ptx
The RAC redo archive log 104 ptx
The RAC redo archive log 105 ptx
The RAC redo archive log 106 ptx
The RAC redo archive log 107 ptx
The RAC redo archive log 108 ptx
The RAC redo archive log 109 ptx
The RAC recover total redo 109 ptx
EP(0) slot ctl page(1, 0, 16) trxid = 10105
EP(1) slot ctl page(1, 0, 17) trxid = 8457
EP[0] Recover LSN from 80054 to 80130.
EP[1] Recover LSN from 80024 to 80023.
Recover from archive log finished, time used:0.147s.
recover successfully!
time used: 7027.267(ms)

或者使用RECOVER DATABASE…UNTIL TIME命令恢复到指定的LSN：

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch' until lsn 80126;
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: 80023
EP 0's ckpt_lsn = 80054
min_ckpt_lsn = 80054
The RAC redo archive log 1 ptx
The RAC redo archive log 2 ptx
The RAC redo archive log 3 ptx
The RAC redo archive log 4 ptx
The RAC redo archive log 5 ptx
The RAC redo archive log 6 ptx
The RAC redo archive log 7 ptx
The RAC redo archive log 8 ptx
The RAC redo archive log 9 ptx
The RAC redo archive log 10 ptx
The RAC redo archive log 11 ptx
The RAC redo archive log 12 ptx
The RAC redo archive log 13 ptx
The RAC redo archive log 14 ptx
The RAC redo archive log 15 ptx
The RAC redo archive log 16 ptx
The RAC redo archive log 17 ptx
The RAC redo archive log 18 ptx
The RAC redo archive log 19 ptx
The RAC redo archive log 20 ptx
The RAC redo archive log 21 ptx
The RAC redo archive log 22 ptx
The RAC redo archive log 23 ptx
The RAC redo archive log 24 ptx
The RAC redo archive log 25 ptx
The RAC redo archive log 26 ptx
The RAC redo archive log 27 ptx
The RAC redo archive log 28 ptx
The RAC redo archive log 29 ptx
The RAC redo archive log 30 ptx
The RAC redo archive log 31 ptx
The RAC redo archive log 32 ptx
The RAC redo archive log 33 ptx
The RAC redo archive log 34 ptx
The RAC redo archive log 35 ptx
The RAC redo archive log 36 ptx
The RAC redo archive log 37 ptx
The RAC redo archive log 38 ptx
The RAC redo archive log 39 ptx
The RAC redo archive log 40 ptx
The RAC redo archive log 41 ptx
The RAC redo archive log 42 ptx
The RAC redo archive log 43 ptx
The RAC redo archive log 44 ptx
The RAC redo archive log 45 ptx
The RAC redo archive log 46 ptx
The RAC redo archive log 47 ptx
The RAC redo archive log 48 ptx
The RAC redo archive log 49 ptx
The RAC redo archive log 50 ptx
The RAC redo archive log 51 ptx
The RAC redo archive log 52 ptx
The RAC redo archive log 53 ptx
The RAC redo archive log 54 ptx
The RAC redo archive log 55 ptx
The RAC redo archive log 56 ptx
The RAC redo archive log 57 ptx
The RAC redo archive log 58 ptx
The RAC redo archive log 59 ptx
The RAC redo archive log 60 ptx
The RAC redo archive log 61 ptx
The RAC redo archive log 62 ptx
The RAC redo archive log 63 ptx
The RAC redo archive log 64 ptx
The RAC redo archive log 65 ptx
The RAC redo archive log 66 ptx
The RAC redo archive log 67 ptx
The RAC redo archive log 68 ptx
The RAC redo archive log 69 ptx
The RAC redo archive log 70 ptx
The RAC redo archive log 71 ptx
The RAC redo archive log 72 ptx
The RAC redo archive log 73 ptx
The RAC redo archive log 74 ptx
The RAC redo archive log 75 ptx
The RAC redo archive log 76 ptx
The RAC redo archive log 77 ptx
The RAC redo archive log 78 ptx
The RAC redo archive log 79 ptx
The RAC recover total redo 79 ptx
EP(0) slot ctl page(1, 0, 16) trxid = 10104
EP(1) slot ctl page(1, 0, 17) trxid = 8457
EP[0] Recover LSN from 80054 to 80126.
EP[1] Recover LSN from 80024 to 80023.
Recover from archive log finished, time used:0.145s.
recover successfully!
time used: 7026.956(ms)

8)在目标库验证表tab_for_recover_01中的数据

[root@dmks init.d]# 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.592(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select * from tab_for_recover_01;

LINEID     C1
---------- -----------
1          1

used time: 2.879(ms). Execute id is 3.

DM7使用DMRMAN对数据库执行指定映射文件还原

Posted on 2020年12月7日 by YongJing(敬勇)

DMRMAN对数据库执行指定映射文件还原
指定映射文件还原,还原后的数据文件默认地生成到还原目标库的路径下，如果用户想生成数据文件到特定的路径，就需要指定映射文件参数来实现。

映射文件（mappedfile）用于指定存放还原目标路径，即备份集里面的数据文件的路径。可以手动修改自动生成的映射文件。当参数BACKUPSET和MAPPED FILE指定的路径不一致时，以MAPPED FILE中指定的路径为主。映射文件可用于库级脱机还原和表空间还原。

使用DUMP命令可以将指定备份集还原目标信息生成到目标映射文件中，该文件可被重新编辑后，用于数据库的还原过程。语法如下：

DUMP BACKUPSET '< 备份集目录>' [DEVICE TYPE DISK|TAPE [PARMS '介质参数']]
[DATABASE ''] MAPPED FILE '< 映射文件>';

BACKUPSET：待生成映射文件的备份集目录。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
DATABASE：指定目标还原库的dm.ini文件路径。
MAPPED FILE：生成映射文件路径。若指定DATABASE参数，则生成内容调整为与指定数据库相适应的数据文件目标还原路径；否则，仅将备份集中备份时记录的路径输出。

下面以脱机备份还原为例说明使用映射文件还原的具体步骤。
1) 启动RMAN，脱机备份数据库。

RMAN> backup database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_bak_for_map_01';
backup database '/dm_home/dmdba/dmdbms/data/jydm/dm.ini' backupset '/dm_home/dmdba/dmdbms/data/jydm/bak/db_bak_for_map_01';
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: 20379861
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 22 packages processed...
total 23 packages processed...
DBF BACKUP MAIN......
BACKUPSET [/dm_home/dmdba/dmdbms/data/jydm/bak/db_bak_for_map_01] END, CODE [0]......
META GENERATING......
total 27 packages processed...
total 27 packages processed!
CMD END.CODE:[0]
backup successfully!
time used: 16318.143(ms)

[dmdba@shard1 bak]$ scp -r db_bak_for_map_01/ dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
db_bak_for_map_01.bak                                                                                                                                                                                     100%  210MB 104.9MB/s   00:02
db_bak_for_map_01.meta                                                                                                                                                                                    100%   85KB  84.5KB/s   00:00
[dmdba@shard1 bak]$

2) 生成映射文件map_file_01.txt至/dm_home/dmdbms/backup目录。此处指定生成映射文件中的数据文件路径与数据库/dm_home/dmdbms/data/dameng_for_recover/中的数据文件一致。若不指定，与备份集中的源数据库的数据文件路径一致。

RMAN> dump backupset '/dm_home/dmdbms/backup/db_bak_for_map_01' database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' mapped file '/dm_home/dmdbms/backup/map_file_01.txt';
dump backupset '/dm_home/dmdbms/backup/db_bak_for_map_01' database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' mapped file '/dm_home/dmdbms/backup/map_file_01.txt';
dump mapped file successfully.
time used: 11.093(ms)

生成的映射文件内容如下：

[dmdba@dmks backup]$ cat map_file_01.txt
/**************************************************************/
/***  Delete the unnecessary modified groups                 **/
/***  Modify the data_path or mirror_path only in one group  **/
/**************************************************************/

/**=============================================================**/
/*[jydm_SYSTEM_FIL_0]*/
fil_id         = 0
ts_id          = 0
ts_name        = SYSTEM
data_path      = /dm_home/dmdbms/data/dameng_for_recover/SYSTEM.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_ROLL_FIL_0]*/
fil_id         = 0
ts_id          = 1
ts_name        = ROLL
data_path      = /dm_home/dmdbms/data/dameng_for_recover/ROLL.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_MAIN_FIL_0]*/
fil_id         = 0
ts_id          = 4
ts_name        = MAIN
data_path      = /dm_home/dmdbms/data/dameng_for_recover/MAIN.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_BOOKSHOP_FIL_0]*/
fil_id         = 0
ts_id          = 5
ts_name        = BOOKSHOP
data_path      = /dm_home/dmdbms/data/dameng_for_recover/BOOKSHOP.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_DMHR_FIL_0]*/
fil_id         = 0
ts_id          = 6
ts_name        = DMHR
data_path      = /dm_home/dmdbms/data/dameng_for_recover/DMHR.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_USERS_FIL_0]*/
fil_id         = 0
ts_id          = 7
ts_name        = USERS
data_path      = /dm_home/dmdbms/data/dameng_for_recover/users01.dbf
mirror_path    =

/**=============================================================**/
/*[jydm_SYSAUX_FIL_0]*/
fil_id         = 0
ts_id          = 9
ts_name        = SYSAUX
data_path      = /dm_home/dmdbms/data/dameng_for_recover/SYSAWR.DBF
mirror_path    =

/**=============================================================**/
/*[jydm_FG_PERSON_FIL_0]*/
fil_id         = 0
ts_id          = 10
ts_name        = FG_PERSON
data_path      = /dm_home/dmdbms/data/dameng_for_recover/FG_PERSON.DBF
mirror_path    =

/***************************** END ****************************/

如果需要恢复数据文件或镜像文件到指定路径，可手动编辑映射文件中表空间对应的data_path属性。例如，要还原USERS表空间中的数据文件users01.dbf到/home/dmdba/路径下，修改组jydm_USERS_FIL_0的内容如下：

/*[jydm_USERS_FIL_0]*/
fil_id         = 0
ts_id          = 7
ts_name        = USERS
data_path      = /home/dmdba/users01.dbf
mirror_path    =

3) 指定映射文件还原。还原前可选择对备份文件进行校验。

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_for_map_01' mapped file '/dm_home/dmdbms/backup/map_file_01.txt';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_for_map_01' mapped file '/dm_home/dmdbms/backup/map_file_01.txt';
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_for_map_01] START......
total 23 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 25 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 49988.649(ms)

4)恢复数据库

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_for_map_01';
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_bak_for_map_01';
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: 20379861
RESTORE RLOG  CHECK......
CMD END.CODE:[603],DESC:[no log generates while the backupset [/dm_home/dmdbms/backup/db_bak_for_map_01] created]
no log generates while the backupset [/dm_home/dmdbms/backup/db_bak_for_map_01] created
recover successfully!
time used: 7026.527(ms)

5）检验数据文件还原的位置

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

SQL> select * from v$datafile;

LINEID     GROUP_ID    ID          PATH                                                  CLIENT_PATH             CREATE_TIME                 STATUS$     RW_STATUS   LAST_CKPT_TIME              MODIFY_TIME                 MODIFY_TRX           TOTAL_SIZE           FREE_SIZE            FREE_PAGE_NO         PAGES_READ           PAGES_WRITE          PAGE_SIZE   READ_REQUEST WRITE_REQUEST AUTO_EXTEND MAX_SIZE    NEXT_SIZE   MIRROR_PATH
---------- ----------- ----------- ----------------------------------------------------- ----------------------- --------------------------- ----------- ----------- --------------------------- --------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- ------------ ------------- ----------- ----------- ----------- -----------
1          0           0           /dm_home/dmdbms/data/dameng_for_recover/SYSTEM.DBF    SYSTEM.DBF              2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    2944                 2117                 1840                 368                  3                    8192        368          3             1           16777215    0           NULL
2          1           0           /dm_home/dmdbms/data/dameng_for_recover/ROLL.DBF      ROLL.DBF                2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    25472                24138                24400                32                   1289                 8192        32           1289          1           16777215    0           NULL
3          3           0           /dm_home/dmdbms/data/dameng_for_recover/TEMP.DBF      TEMP.DBF                2020-06-18 17:08:08         1           2           2020-06-18 17:13:08         2020-06-18 17:08:08         0                    1280                 1272                 32                   0                    0                    8192        0            0             1           16777215    0           NULL
4          4           0           /dm_home/dmdbms/data/dameng_for_recover/MAIN.DBF      MAIN.DBF                2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    24064                17781                22944                1607                 0                    8192        1607         0             1           16777215    0           NULL
5          5           0           /dm_home/dmdbms/data/dameng_for_recover/BOOKSHOP.DBF  BOOKSHOP.DBF            2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    19200                19079                864                  43                   0                    8192        43           0             1           16777215    0           NULL
6          6           0           /dm_home/dmdbms/data/dameng_for_recover/DMHR.DBF      DMHR.DBF                2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    16384                16325                304                  35                   0                    8192        35           0             1           16777215    0           NULL
7          7           0           /home/dmdba/users01.dbf                               /home/dmdba/users01.dbf 2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    6400                 6258                 3392                 57                   0                    8192        57           0             1           16777215    0           NULL
8          9           0           /dm_home/dmdbms/data/dameng_for_recover/SYSAWR.DBF    SYSAWR.DBF              2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    21760                2834                 20592                529                  0                    8192        529          0             1           10240       0           NULL
9          10          0           /dm_home/dmdbms/data/dameng_for_recover/FG_PERSON.DBF FG_PERSON.DBF           2020-06-18 17:02:59         1           2           2020-06-18 17:13:08         2020-06-18 17:02:59         0                    16384                16375                32                   1                    0                    8192        1            0             1           16777215    0           NULL

9 rows got

used time: 8.354(ms). Execute id is 10.

DM7使用DMRAMN执行归档恢复

Posted on 2020年11月18日 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 2020年11月6日 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 2020年11月6日 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 2020年11月6日 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)。所有这些不同的命名标准都是有效的。