达梦数据库学习之备份还原

一、备份前准备

       1、检查数据库版本
       2、检查磁盘空间大小
       3、检查是否打开归档

二、导出备份的几种方式

       一、dexp逻辑导出备份
               1.全备full：
                ./dexp USERID=MYDM/my123456789:32141 FILE=2020_06_23_fullbak.dmp LOG=2020_06_23_fullbak.log FULL=Y DIRECTORY=/opt/dmdbms/dmdata/DAMENG/bak
                2.用户owner：
                ./dexp USERID=MYDM/my123456789:32141 FILE=2020_06_23_ownerbak.dmp LOG=2020_06_23_ownerbak.log OWNER=MYDM DIRECTORY=/opt/dmdbms/dmdata/DAMENG/bak
                3.模式schemas：
                ./dexp USERID=MYDM/my123456789:32141 FILE=2020_06_23_schemasbak.dmp LOG=2020_06_23_schemasbak.log SCHEMAS=PERSON DIRECTORY=/opt/dmdbms/dmdata/DAMENG/bak
               4.表或表分区table：
                ./dexp USERID=MYDM/my123456789:32141 FILE=2020_06_23_tablebak.dmp LOG=2020_06_23_tablebak.log TABLES=PERSON.PERSON,PERSON.ADDRESS DIRECTORY=/opt/dmdbms/dmdata/DAMENG/bak

二、DM管理工具备份
        ./manager
                点击备份–>右键库备份（表备份等）–>新建备份–>点击常规：写备份名，备份集目录，选择备份类型；点击高级，按需求填写选项–>确定
点击代理–>新建作业–>根据需求创建

三、DM控制台备份
        ./console
        点击备份还原–>选择新建备份–>按需求填写备份信息

四、联机备份（./disql）
        首先检查是否开启归档，然后./disql进入数据库 或者dm管理工具
        1.全备（默认）
                BACKUP DATABASE BACKUPSET ‘/opt/dmdbms/dmdata/DAMENG/bak/db_bak_01’ ;
        设置备份名
                BACKUP DATABASE TO WEEKLY_FULL_BAK BACKUPSET ‘/opt/dmdbms/dmdata/DAMENG/bak/db_bak_01’ ;
        添加备份信息
                BACKUP DATABASE BACKUPSET ‘/opt/dmdbms/dmdata/DAMENG/bak/db_bak_01’ BACKUPINFO ‘完全备份’;
        限制备份集大小
                BACKUP DATABASE BACKUPSET ‘/opt/dmdbms/dmdata/DAMENG/bak/db_bak_01’ MAXPIECESIZE 300;
        压缩级别
                BACKUP DATABASE BACKUPSET ‘/opt/dmdbms/dmdata/DAMENG/bak/db_bak_01’COMPRESSED LEVEL 5；
        2.增量备份
                BACKUP DATABASE INCREMENT WITH BACKUPDIR ‘/home/dm_bak’BACKUPSET ‘/home/dm_bak/db_increment_bak_02’;
        3.表空间备份
        完全备份（默认完全）
                BACKUP TABLESPACE MAIN FULL BACKUPSET ‘/home/dm_bak/ts_full_bak_01’;
        增量备份（创建累积增量备份，还需要指定 CUMULATIVE参数，否则缺省为差异增量备份。）
                BACKUP TABLESPACE MAIN INCREMENT BACKUPSET ‘ts_increment_bak_01’;
        增量备份指定基备份目录
                BACKUP TABLESPACE MAIN INCREMENT BASE ON BACKUPSET’ts_full_bak_01’ BACKUPSET ‘ts_increment_bak_02’;
                BACKUP TABLESPACE MAIN INCREMENT WITH BACKUPDIR ‘/home/dm_bak’BACKUPSET ‘/home/dm_bak/ts_increment_bak_02’;
        4.表备份
                ACKUP TABLE TAB_01 BACKUPSET ‘/home/dm_bak/tab_bak_01’;
        5.归档备份
                一是，归档文件的 db_magic、permanent_magic 值和库的 db_magic、permanent_magic值必须一样；二是，服务器必须配置归档；三是，归档日志必须连续，
                BACKUP ARCHIVE LOG ALL BACKUPSET ‘arch_bak_01’;
        先查询LSN范围。
                select ARCH_LSN, CLSN, PATH from V$ARCH_FILE;
        然后备份归档
                BACKUP ARCHIVELOGLSN BETWEEN 50414 AND 50478 BACKUPSET ‘/home/dm_bak/arch_bak_time_14-78’;
        五、脱机备份（./dmrman）
        需要设置归档和关闭数据库实例
        可执行写好的文件（行解析）
                ./dmrman CTLFILE=/home/dm_cmd/cmd_rman.txt
        1.全备（默认）
                BACKUP DATABASE’/opt/dmdbms/data/DAMENG/dm.ini’ FULL BACKUPSET ‘/home/dm_bak/db_full_bak_01’;
                ./dmrman CTLSTMT="BACKUP DATABASE ‘/opt/dmdbms/data/DAMENG/dm.ini’;（dm.ini备份路径，若无则 SYSTEM_PATH下的 bak目录）
        2.增量备份
                BACKUP DATABASE ‘/opt/dmdbms/data/DAMENG/dm.ini’ INCREMENT WITH BACKUPDIR '/home/dm_bak’BACKUPSET ‘/home/dm_bak/db_increment_bak_02’;
        3.归档备份（默认all）
        全部备份
                BACKUP ARCHIVE LOG DATABASE ‘/opt/dmdbms/data/DAMENG/dm.ini’;
                BACKUP ARCHIVE LOG ALL DATABASE '/opt/dmdbms/data/DAMENG/dm.ini’BACKUPSET ‘/home/dm_bak/arch_all_bak_01’;
        lsn范围备份
                BACKUP ARCHIVE LOG LSN BETWEEN 50000 AND120000 DATABASE '/opt/dmdbms/data/DAMENG/dm.ini’BACKUPSET ‘/home/dm_bak/db_increment_bak_02’;

三、还原命令

一、dimp逻辑导入
        1.full导入
                ./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log FULL=Y DIRECTORY=/mnt/data/dimp
        2.owner导入
                ./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log USER=USER01 DIRECTORY=/mnt/data/dimp
        3.schemas导入
                ./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log SCHEMAS=USER01 DIRECTORY=/mnt/data/dimp
        4.tables导入
                ./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log TABLES=table1,table2 DIRECTORY=/mnt/data/dimp

二、DM管理工具导入
        右键库名称–>选择导入—>填写导入信息—>导入

三、DM控制台导入
        点击备份还原—>点击还原（恢复）—>根据需求还原恢复相应备份集

四、联机还原
        DM 仅支持表的联机还原，数据库、表空间和归档日志的还原必须通过脱机工具 DMRMAN执行。
        1.表还原（表数据，结构）
                RESTORE TABLE TAB_01 FROM BACKUPSET ‘tab_bak_01’;
                RESTORE TABLE TAB_FOR_RES FROM BACKUPSET ‘/home/dm_bak/tab_bak_for_res_01’;
        表与备份中都包含表结构
                RESTORE TABLE TAB_FOR_RES_02 STRUCT FROM BACKUPSET ‘/home/dm_bak/tab_bak_for_res_02’;
                RESTORE TABLE TAB_FOR_RES_02 FROM BACKUPSET ‘/home/dm_bak/tab_bak_for_res_02’;
        还原表数据不重建索引
                RESTORE TABLE TAB_FOR_RES WITHOUT INDEX FROM BACKUPSET ‘/home/dm_bak/tab_bak_for_res_01’;
        还原表数据不还原约束
                RESTORE TABLETAB_FOR_CONS_01 WITHOUT CONSTRAINT FROM BACKUPSET ‘/home/dm_bak/tab_bak_for_res_01’;

五、脱机还原
        还原步骤: 还原–>恢复–>更新 三步都需要执行
        1.数据库还原
                （1）完全还原
                        RESTORE DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini’ FROM BACKUPSET ‘/home/dm_bak/db_full_bak_for_restore’;
                （2）归档还原
                        RESTORE ARCHIVE LOG FROM BACKUPSET ‘/home/dm_bak/arch_bak_lsn_421401’ ALL TO ARCHIVEDIR ‘/home/dm_arch/arch’;
                        RESTORE ARCHIVE LOG FROM BACKUPSET ‘/home/dm_bak/arch_all_for_restore’ TO DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini’ OVERWRITE 2;
                （3）恢复数据库
                        RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini’ FROM BACKUPSET ‘/home/dm_bak/db_full_bak_for_recover_backupset’;
                （4）归档恢复
                        SHOW BACKUPSET ‘/home/dm_bak/db_full_bak_for_recover_arch’ INFO DB;
                        RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini’ WITH ARCHIVEDIR’/home/dm_arch/arch’ USE DB_MAGIC 1447060265;
                （5）数据库更新
                        RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RESTORE/dm.ini’ UPDATE DB_MAGIC;
        2.表空间恢复
                RESTORE DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RECOVER/dm.ini’ TABLESPACE MAIN FROM BACKUPSET ‘/home/dm_bak/db_full_bak_for_recover’;
                RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RECOVER/dm.ini’ TABLESPACE MAIN;
        通过文件编号
                SELECT ID, PATH FROM V$DATAFILE;
                RESTORE DATABASE ‘/home/xm/DAMENG/dm.ini’TABLESPACE TS_FOR_RES_01 DATAFILE
                RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RECOVER/dm.ini’ TABLESPACE MAIN;
        不通过文件编号
                RESTORE TABLESPACE TS_FOR_RES_01 DATAFILE ‘/home/xm/DAMENG/ts_for_res_01_02.dbf’, ‘/home/xm/DAMENG/ts_for_res_01_03.dbf’ FROM BACKUPSET ‘/home/dm_bak/ts_bak_for_dbf’;
                RECOVER DATABASE ‘/opt/dmdbms/data/DAMENG_FOR_RECOVER/dm.ini’ TABLESPACE MAIN;
        指定映射文件还原
                RESTORE DATABASE ‘/home/xm/DAMENG/dm.ini’ TABLESPACE MAIN FROM BACKUPSET ‘/home/dm_bak/ts_bak_for_map’MAPPED FILE’/home/dm_mappedfile/map_file.txt’;
        指定归档目录还原
                RECOVER DATABASE ‘/home/xm/DAMENG/dm.ini’ TABLESPACE MAIN FROM BACKUPSET ‘/home/dm_bak/ts_bak_for_arch’ WITH ARCHIVEDIR’/home/dm_arch1’, ‘/home/dm_arch2’;
        主备环境下指定 DB_MAGIC收集归档
                RESTORE DATABASE ‘/home/xm/DAMENG/dm.ini’ TABLESPACE MAIN FROM BACKUPSET ‘/home/dm_bak/ts_bak_for_arch’;
                RECOVER DATABASE ‘/home/xm/DAMENG/dm.ini’ TABLESPACE MAIN FROM BACKUPSET ‘/home/dm_bak/ts_bak_for_arch’ WITH ARCHIVEDIR’ ‘/home/dm_arch2’ USE DB_MAGIC 18446520;