工作中难免遇到误操作数据的情况，如果未提交，可通过回滚挽回，如果已提交，并且未开启闪回等功能，可通过备份集和归档还原到故障前的状态。生产环境一般无法直接还原，会丢失后续所有的数据，可初始化新实例，使用原环境的备份集和归档，将新实例还原到误操作前的状态，再将需要的数据迁移回原环境。
环境说明：

IP地址	         端口	说明
127.0.0.1	5236	原始环境（用来模拟生产环境）
127.0.0.1	5237	新实例（不方便直接操作生产环境，使用该环境还原生产环境的数据，生产环境数据恢复后可删除该环境）

一、搭建原始环境

1、安装数据库软件

[root@www ~]# mount -o loop dm8_20241022_x86_rh7_64.iso soft/
mount: /dev/loop0 is write-protected, mounting read-only
[root@www ~]# cd soft/
[root@www soft]# ls
DM8 Install.pdf  DMInstall.bin
[root@www soft]# ./DMInstall.bin -i
OS user dmdba is not detected, please check whether the OS user dmdba exist!
[root@www soft]# groupadd dinstall
[root@www soft]# useradd -g dinstall dmdba
[root@www soft]# ./DMInstall.bin -i
Installer Language:
[1]: 简体中文
[2]: English
Please select the installer's language [2]:
Extract install files.........
Hardware architecture verification passed！
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 7216
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 7216
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

The max number of open files is too little, suggest to set 65536 or more to number of open files.

Welcome to DM DBMS Installer
Whether to Set The TimeZone? (Y/y:Yes N/n:No) [Y/y]:n

Installation Type:
1 Typical
2 Server
3 Client
4 Custom
Please Input the number of the Installation Type [1 Typical]:
Require Space: 2284M

Please Input the install path [/opt/dmdbms]:/home/dmdba/dmdbms
Available Space:33G
Please Confirm the install path(/home/dmdba/dmdbms)? (Y/y:Yes N/n:No) [Y/y]:y

Pre-Installation Summary
Installation Location: /home/dmdba/dmdbms
Require Space: 2284M
Available Space: 33G
Version Information:
Expire Date:
Installation Type: Typical
Confirm to Install? (Y/y:Yes N/n:No):y
2025-02-05 13:25:56
[INFO] Installing DM DBMS...
2025-02-05 13:25:56
[INFO] Installing BASE Module...
2025-02-05 13:26:17
[INFO] Installing SERVER Module...
2025-02-05 13:26:19
[INFO] Installing CLIENT Module...
2025-02-05 13:26:27
[INFO] Installing DRIVERS Module...
2025-02-05 13:26:29
[INFO] Installing MANUAL Module...
2025-02-05 13:26:29
[INFO] Installing SERVICE Module...
2025-02-05 13:26:30
[INFO] Move log file to log directory.
2025-02-05 13:26:30
[INFO] Starting DmAPService service...
2025-02-05 13:26:31
[INFO] Start DmAPService service successfully.
2025-02-05 13:26:31
[INFO] Installed DM DBMS completely.

End

2、初始化数据库

[root@www soft]# cd /home/dmdba/dmdbms/bin
[root@www bin]# ./dminit path=/home/dmdba/dmdata page_size=32
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2025-10-22
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

 log file path: /home/dmdba/dmdata/DAMENG/DAMENG01.log


 log file path: /home/dmdba/dmdata/DAMENG/DAMENG02.log

write to dir [/home/dmdba/dmdata/DAMENG].

3、注册数据库服务并启动数据库

[root@www bin]# cd ../script/root/
[root@www root]# ./dm_service_installer.sh -t dmserver -p DMSERVER -dm_ini /home/dmdba/dmdata/DAMENG/dm.ini
Created symlink from /etc/systemd/system/multi-user.target.wants/DmServiceDMSERVER.service to /usr/lib/systemd/system/DmServiceDMSERVER.service.
Finished to create the service (DmServiceDMSERVER)
[root@www root]# cd /home/dmdba/dmdbms/bin
[root@www bin]# chown -R dmdba:dinstall /home/dmdba/
[root@www bin]# chmod -R 755 /home/dmdba/
[root@www bin]# ./DmServiceDMSERVER start
Starting DmServiceDMSERVER:                                [ OK ]

4、开启归档

ALTER DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG 'DEST = /home/dmdba/arch, TYPE = local,FILE_SIZE = 128, SPACE_LIMIT = 1024';
alter system set 'ARCH_INI'=1 both;
select arch_mode from v$database;
ALTER DATABASE OPEN;
checkpoint(100);

5、备份数据库

SQL> backup database backupset '/home/dmdba/bak1' compressed level 1 task thread 4 parallel 2;
executed successfully
used time: 00:00:06.697. Execute id is 708.
SQL>

二、模拟误操作数据

模拟update忘记加where条件，误更新c2列所有数据场景。

1)建表并插入数据
drop table if exists dm;
create table dm (c1 int,c2 varchar(100));
insert into dm values(1,'test1');
insert into dm values(2,'test2');
commit;
2)记录当前时间
select sysdate;--2025-02-05 15:58:28
3）操作update
update dm set c2='test';
commit;

三、恢复原环境dm表的数据

1、初始化新实例

[dmdba@www bin]$ ./dminit path=/home/dmdba/dmdata db_name=DAMENG2 PORT_NUM=5237
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2025-10-22
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL

 log file path: /home/dmdba/dmdata/DAMENG2/DAMENG201.log


 log file path: /home/dmdba/dmdata/DAMENG2/DAMENG202.log

write to dir [/home/dmdba/dmdata/DAMENG2].

2、使用原环境备份集和归档将新环境数据还原到原环境故障前

[dmdba@www bin]$ ./dmrman
dmrman V8
RMAN> restore database '/home/dmdba/dmdata/DAMENG2/dm.ini' from backupset '/home/dmdba/bak1';
restore database '/home/dmdba/dmdata/DAMENG2/dm.ini' from backupset '/home/dmdba/bak1';
file dm.key not found, use default license!
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:02][Remaining:00:00:00]
restore successfully.
time used: 00:00:02.775
RMAN> recover database '/home/dmdba/dmdata/DAMENG2/dm.ini' with archivedir '/home/dmdba/arch' until time '2025-02-05 15:58:28';
recover database '/home/dmdba/dmdata/DAMENG2/dm.ini' with archivedir '/home/dmdba/arch' until time '2025-02-05 15:58:28';
[Percent:66.67%][Speed:0.00PKG/s][Cost:00:00:00][Remaining:00:00:00]
recover successfully!
time used: 00:00:01.149
RMAN> recover database '/home/dmdba/dmdata/DAMENG2/dm.ini' update db_magic;
recover database '/home/dmdba/dmdata/DAMENG2/dm.ini' update db_magic;
recover successfully!
time used: 00:00:01.079
RMAN>

3、启动新实例

临时创建的实例，未注册数据库服务，直接前台启动即可。

[dmdba@www bin]$ ./dmserver /home/dmdba/dmdata/DAMENG2/dm.ini
file dm.key not found, use default license!
version info: develop
csek2_vm_t = 1408
nsql_vm_t = 328
prjt2_vm_t = 176
ltid_vm_t = 216
nins2_vm_t = 1048
nset2_vm_t = 272
ndlck_vm_t = 192
ndel2_vm_t = 776
slct2_vm_t = 208
nli2_vm_t = 200
aagr2_vm_t = 280
pscn_vm_t = 288
dist_vm_t = 896
DM Database Server 64 V8 03134284194-20241022-246636-20108 startup...
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
Database mode = 0, oguid = 0
License will expire on 2025-10-22
file lsn: 43950
ndct db load finished, code:0
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct second level fill fast pool finished
ndct third level fill fast pool finished
ndct fill fast pool finished
pseg_set_gtv_trxid_low next_trxid in mem:[38058]
pseg_collect_mgr_items, total collect 1 active_trxs, 0 cmt_trxs, 0 pre_cmt_trxs, 0 to_release_trxs, 1 active_pages, 0 cmt_pages, 0 pre_cmt_pages, 0 to_release_pages, 1 mgr pages, 1 mgr recs!
next_trxid in mem:[40060]
next_trxid = 42062.
total 1 active crash trx, pseg_crash_trx_rollback sys_only(0) begin ...
trx: 36062 rollbacking...
total [1/1] page rollbacked, rollback percent: 100%
total [1/1] trx rollbacked, rollback percent: 100%
trx: 36062 rollback 1 upages, 2 urecs end
pseg_crash_trx_rollback end, total 1 active crash trx, include 0 empty_trxs, 0 empty_pages which only need to delete mgr recs.
pseg_crash_trx_rollback end
pseg recv finished
nsvr_startup end.
uthr_pipe_create, create pipe[read:10, write:11]
uthr_pipe_create, create pipe[read:12, write:13]
uthr_pipe_create, create pipe[read:14, write:15]
uthr_pipe_create, create pipe[read:16, write:17]
uthr_pipe_create, create pipe[read:18, write:19]
uthr_pipe_create, create pipe[read:20, write:21]
uthr_pipe_create, create pipe[read:22, write:23]
uthr_pipe_create, create pipe[read:24, write:25]
uthr_pipe_create, create pipe[read:26, write:27]
uthr_pipe_create, create pipe[read:28, write:29]
uthr_pipe_create, create pipe[read:30, write:31]
uthr_pipe_create, create pipe[read:32, write:33]
uthr_pipe_create, create pipe[read:34, write:35]
uthr_pipe_create, create pipe[read:36, write:37]
uthr_pipe_create, create pipe[read:38, write:39]
uthr_pipe_create, create pipe[read:40, write:41]
aud sys init success.
aud rt sys init success.
systables desc init success.
ndct_db_load_info finished, code:0.
nsvr_process_before_open begin.
nsvr_process_before_open success.
SYSTEM IS READY.

4、检查新实例dm表数据是否正确

[dmdba@www bin]$ ./disql SYSDBA/SYSDBA:5237

Server[LOCALHOST:5237]:mode is normal, state is open
login used time : 15.702(ms)
disql V8
SQL> select * from dm;

LINEID     C1          C2
---------- ----------- -----
1          1           test1
2          2           test2

used time: 1.414(ms). Execute id is 601.
SQL>
数据正确。

5、将新实例dm表数据迁移到原库

可以使用图形化工具dts直接迁移，本例采用dmp方式迁移。

1）新实例导出数据

./dexp userid=SYSDBA/SYSDBA@localhost:5237 DIRECTORY=/home/dmdba file=dm.dmp TABLES=SYSDBA.dm LOG=dm.log PARALLEL=4

2）原环境导入数据

./dimp userid=SYSDBA/SYSDBA@localhost:5236 DIRECTORY=/home/dmdba file=dm.dmp TABLES=SYSDBA.dm LOG=dmdimp.log PARALLEL=4 table_exists_action=replace

3）数据校验

检查原环境数据是否恢复。

[dmdba@www bin]$ ./disql SYSDBA/SYSDBA:5236

Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 5.601(ms)
disql V8
SQL> select * from dm;

LINEID     C1          C2
---------- ----------- -----
1          1           test1
2          2           test2

used time: 0.761(ms). Execute id is 2001.
SQL>
数据已恢复