达梦备份

脱机物理备份，为保持数据一致性需要关闭数据库，多用于主备、mpp搭建

脱机物理备份
关闭数据库备份
[dmdba@ht01 hh]$ ps -ef|grep ini|grep -v grep
dmdba 1336 1 0 08:33 ? 00:00:03 /u01/app/dm/bin/dmwatcher path=/u01/app/dm/data/hh/dmwatcher.ini -noconsole
dmdba 1570 1 0 08:33 ? 00:00:05 /u01/app/dm/bin/dmserver /u01/app/dm/data/hh/dm.ini mount
dmdba 1799 1728 0 08:41 pts/0 00:00:04 dmmonitor /u01/app/dm/data/hh/dmmonitor.ini

systemctl stop DmServicehh01.service
dmrman CTLSTMT="BACKUP DATABASE '/u01/app/dm/data/hh/dm.ini' FULL"
数据库恢复
dmrman CTLSTMT="RESTORE DATABASE '/u01/app/dm/data/hh/dm.ini' FROM BACKUPSET '/u01/app/dm/data/hh/bak/DB_hh_FULL_20220815_165724_891753'"
dmrman CTLSTMT="RECOVER DATABASE '/u01/app/dm/data/hh/dm.ini' FROM BACKUPSET '/u01/app/dm/data/hh/bak/DB_hh_FULL_20220815_165724_891753'"
dmrman CTLSTMT="RECOVER DATABASE '/u01/app/dm/data/hh/dm.ini' UPDATE DB_MAGIC"

联机物理备份，需要开启归档

alter DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG 'DEST =/data/dm8/arch, TYPE = local,FILE_SIZE = 512, SPACE_LIMIT = 40960';
ALTER DATABASE OPEN;

恢复到某个时间点

RESTORE DATABASE '/u01/app/dm/data/hh/dm.ini' FROM BACKUPSET '/u01/app/dm/data/hh/bak/DB_hh_FULL_20220815_165724_891753';

RECOVER DATABASE '/u01/app/dm/data/hh/dm.ini' FROM BACKUPSET '/u01/app/dm/data/hh/bak/DB_hh_FULL_20220815_165724_891753'  with archivedir '/data/dm8/arch' until time '2025-05-21 11:05:00';

RECOVER DATABASE '/u01/app/dm/data/hh/dm.ini' UPDATE DB_MAGIC;

 

检查数据库是否开启归档
[dmdba@ht01 tool]$ disql SYSDBA/SYSDBA

Server[LOCALHOST:5236]:mode is primary, state is open
login used time : 1.763(ms)
disql V8
SQL> select name,status$,role$,arch_mode from v$database;

LINEID NAME STATUS$ ROLE$ ARCH_MODE
---------- ---- ----------- ----------- ---------
1 hh 4 1 Y

used time: 6.160(ms). Execute id is 1100.

全备
[dmdba@ht01 script]$ cat full.sh
/u01/app/dm/bin/disql SYSDBA/SYSDBA -e "backup database backupset '/u01/app/dm/bak/db_full'"
cd /u01/app/dm/bak
tar cf db_full_`date +"%Y%m%d%H%M%S"`.tar db_full
rm -rf db_full

增量
[dmdba@ht01 script]$ cat incr.sh
/u01/app/dm/bin/disql SYSDBA/SYSDBA -e "backup database increment with backupdir '/u01/app/dm/bak/' backupset '/u01/app/dm/bak/db_inc'"
cd /u01/app/dm/bak
tar cf db_inc_`date +"%Y%m%d%H%M%S"`.tar db_inc
rm -rf db_inc

备份归档日志，执行脚本备份时，数据库会自动切换一次归档日志，建议不要在脚本里在执行切换归档日志
[dmdba@ht01 script]$ cat arch.sh
/u01/app/dm/bin/disql SYSDBA/SYSDBA -e "backup archivelog all delete input backupset '/u01/app/dm/bak/db_arc'"
cd /u01/app/dm/bak
tar cf db_arc_`date +"%Y%m%d%H%M%S"`.tar db_arc
rm -rf db_arc

 

创建表空间及用户
create tablespace hh datafile '/u01/app/dm/data/hh/hh01.DBF' size 32M;
create user hh identified by oracle123 default tablespace hh;
grant dba to hh;
create table hh.tt as select * from dba_objects;

逻辑备份 dexp/dimp
--整库 parallel和filesize 不能一起使用
dexp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/script/full_%U.dmp LOG=full.log FULL=Y parallel=8
--用户
dexp USERID=SYSDBA/SYSDBA FILE=hh%U.dmp LOG=hh.log DIRECTORY=/home/dmdba/script owner=hh parallel=8
--表
dexp USERID=SYSDBA/SYSDBA FILE=tt%U.dmp LOG=tt.log DIRECTORY=/home/dmdba/script tables=hh.tt parallel=8
导入
--整库,file使用%U不能识别
dimp USERID=SYSDBA/SYSDBA FILE=/home/dmdba/script/full_01.dmp LOG=full.log FULL=Y parallel=8
--用户，导入用户时不建用户
dimp USERID=SYSDBA/SYSDBA FILE=hh01.dmp LOG=imphh.log DIRECTORY=/home/dmdba/script owner=hh parallel=8
--表
dimp USERID=SYSDBA/SYSDBA FILE=tt01.dmp LOG=imptt.log DIRECTORY=/home/dmdba/script parallel=8 ignore=y

逻辑备份 dexpdp/dimpdp
--查看目录，如果没有创建
select * from dba_directories;
create directory dm as '/home/dmdba/bk';
grant write,read on directory dm to hh;
--整库 directory目录名必须时大写
dexpdp SYSDBA/SYSDBA directory=DM file=full%U.dmp log=full.log full=y parallel=8
--用户
dexpdp SYSDBA/SYSDBA directory=DM FILE=hh%U.dmp LOG=hh.log owner=hh parallel=8
--表
dexpdp SYSDBA/SYSDBA directory=DM FILE=tt%U.dmp LOG=tt.log tables=hh.tt parallel=8
导入
--整库,file使用%U不能识别，应用数据会重复
dimpdp SYSDBA/SYSDBA directory=DM FILE=full%U.dmp LOG=full.log FULL=Y parallel=8 COMMIT_ROWS=5000
--用户，导入用户时不建用户
dimpdp SYSDBA/SYSDBA directory=DM FILE=hh01.dmp LOG=imphh.log owner=hh parallel=8
--表
dimpdp SYSDBA/SYSDBA directory=DM FILE=tt01.dmp LOG=imptt.log parallel=8 TABLE_EXISTS_ACTION=truncate