初始化

/home/dmdba/dmdbms/bin/dminit \
  PATH='/dmdata/data' INSTANCE_NAME=DM1 DB_NAME=DM1 PAGE_SIZE=32 EXTENT_SIZE=32 LOG_SIZE=256 \
  CHARSET=0 CASE_SENSITIVE=1 PORT_NUM=5236 SYSDBA_PWD=Dameng@123 SYSAUDITOR_PWD=Dameng@123

/home/dmdba/dmdbms/bin/dminit \
  PATH='/dmdata/data' INSTANCE_NAME=DM2 DB_NAME=DM2 PAGE_SIZE=32 EXTENT_SIZE=32 LOG_SIZE=256 \
  CHARSET=0 CASE_SENSITIVE=1 PORT_NUM=5237 SYSDBA_PWD=Dameng@123 SYSAUDITOR_PWD=Dameng@123

  

su root
cd /home/dmdba/dmdbms/script/root
./dm_service_installer.sh -t dmserver -dm_ini /dmdata/data/DM1/dm.ini -p DMSERVER01
./dm_service_installer.sh -t dmserver -dm_ini /dmdata/data/DM2/dm.ini -p DMSERVER02

systemctl start DmServiceDMSERVER01.service
systemctl start DmServiceDMSERVER02.service

/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5236'
/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5237'

联机备份

-- 登录 DM1

/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5236'

-- 1. 修改数据库为 MOUNT 状态

ALTER DATABASE MOUNT;

-- 2. 开启归档

ALTER DATABASE ARCHIVELOG;

-- 3. 配置本地归档（大小64M，空间限制2G）

ALTER DATABASE ADD ARCHIVELOG 'DEST=/dmdata/arch/DM1,TYPE=LOCAL,FILE_SIZE=64,SPACE_LIMIT=2048';

-- 4. 打开数据库

ALTER DATABASE OPEN;

-- 5. 验证归档状态（必须看到 VALID）

SELECT ARCH_NAME, ARCH_TYPE, ARCH_DEST, ARCH_STATUS FROM V$ARCH_STATUS;

-- 结果应显示：LOCAL 类型，DEST=/dmdata/arch/DM1，VALID 状态

-- 6. 切换日志生成归档文件（测试归档是否正常工作）

ALTER SYSTEM SWITCH LOGFILE;
SELECT PATH, ARCH_LSN, CLSN FROM V$ARCH_FILE;

-- 创建专用表空间

CREATE TABLESPACE ts_test 
DATAFILE '/dmdata/data/DM1/TS_TEST.DBF' SIZE 128 AUTOEXTEND ON NEXT 32 MAXSIZE 1024;

-- 查看表空间

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS MB FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TS_TEST';

-- 创建用户表（建在默认表空间 MAIN）

CREATE TABLE user_orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    amount DECIMAL(10,2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20)
);

-- 创建第二张表（用于表级备份实验）

CREATE TABLE user_logs (
    log_id INT PRIMARY KEY,
    operation VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE ts_test;

-- 插入第一批数据（用于全备基线）

INSERT INTO user_orders VALUES (1, '张三', 1999.00, CURRENT_TIMESTAMP, '已支付');
INSERT INTO user_orders VALUES (2, '李四', 2999.00, CURRENT_TIMESTAMP, '待发货');
INSERT INTO user_orders VALUES (3, '王五', 999.00, CURRENT_TIMESTAMP, '已完成');
INSERT INTO user_orders VALUES (4, '赵六', 4999.00, CURRENT_TIMESTAMP, '待发货');
INSERT INTO user_orders VALUES (5, '刘七', 3999.00, CURRENT_TIMESTAMP, '已完成');
COMMIT;

INSERT INTO user_logs VALUES (1, '系统初始化', CURRENT_TIMESTAMP);
INSERT INTO user_logs VALUES (2, '用户登录', CURRENT_TIMESTAMP);
INSERT INTO user_logs VALUES (3, '进入桌面', CURRENT_TIMESTAMP);
COMMIT;

-- 验证数据

SELECT * FROM user_orders;
SELECT * FROM user_logs;

-- 执行全库备份（带压缩，并行2线程）

BACKUP DATABASE BACKUPSET '/dmdata/backup/full/DM1_FULL_20260202';

-- 查看备份集信息（确认成功）

SELECT 
    BACKUP_NAME, 
    BACKUP_PATH, 
    TYPE,
    LEVEL,
    BEGIN_LSN, 
    END_LSN,
    BACKUP_TIME,
    DESC#
FROM V$BACKUPSET 
WHERE BACKUP_PATH = '/dmdata/backup/full/DM1_FULL_20260202';

-- 备份 MAIN 表空间（系统表空间）

BACKUP TABLESPACE MAIN FULL 
BACKUPSET '/dmdata/backup/tablespace/TS_MAIN_FULL' 
BACKUPINFO 'MAIN表空间备份';

-- 备份我们创建的测试表空间

BACKUP TABLESPACE ts_test FULL 
BACKUPSET '/dmdata/backup/tablespace/TS_TEST_FULL' 
BACKUPINFO 'TS_TEST表空间备份';

-- 查看表空间备份

SELECT BACKUP_NAME, OBJECT_NAME, BACKUP_PATH, TYPE 
FROM V$BACKUPSET 
WHERE BACKUP_PATH LIKE '/dmdata/backup/tablespace/%';

-- 备份单张表（会备份数据页和逻辑关系）

BACKUP TABLE user_orders 
BACKUPSET '/dmdata/backup/table/T_USER_ORDERS' 
BACKUPINFO '订单表备份' 
COMPRESSED LEVEL 1;

-- 备份另一张表

BACKUP TABLE user_logs 
BACKUPSET '/dmdata/backup/table/T_USER_LOGS' 
BACKUPINFO 'TABLE_LOGS_BAK';

-- 查看表备份（RANGE#=3 表示表级）

SELECT BACKUP_NAME, OBJECT_NAME, BACKUP_PATH, TYPE, DESC# 
FROM V$BACKUPSET 
WHERE BACKUP_PATH LIKE '/dmdata/backup/table/%';

-- 备份所有归档（NOT BACKED UP 1 TIMES 表示只备份未备份过的）

BACKUP ARCHIVE LOG ALL NOT BACKED UP 1 TIMES 
BACKUPSET '/dmdata/backup/archive/ARCH_ALL_01' 
BACKUPINFO 'ARCH_BACKUP_01' 
COMPRESSED;

-- 查看归档备份

SELECT BACKUP_NAME, BACKUP_PATH, TYPE, BACKUP_TIME, DESC#
FROM V$BACKUPSET 
WHERE BACKUP_PATH = '/dmdata/backup/archive/ARCH_ALL_01';

-- 或者基于LSN范围备份（用于精确恢复）
-- 先查看当前归档范围

SELECT MIN(ARCH_LSN) AS MIN_LSN, MAX(CLSN) AS MAX_LSN FROM V$ARCH_FILE;

-- 基于LSN备份（示例）

BACKUP ARCHIVE LOG LSN BETWEEN 50000 AND 60000 
BACKUPSET '/dmdata/backup/archive/ARCH_LSN_RANGE' 
BACKUPNAME 'ARCH_LSN_BAK';

-- 查看原始数据

SELECT * FROM user_orders;
SELECT COUNT(*) FROM user_orders;  -- 应该显示 5 条

-- 模拟误操作：清空表

TRUNCATE TABLE user_orders;

-- 确认数据丢失

SELECT COUNT(*) FROM user_orders;  -- 应该显示 0 条

-- 1. 检查备份集有效性（返回1表示有效）

SELECT SF_BAKSET_CHECK('DISK', '/dmdata/backup/table/T_USER_ORDERS');

-- 2. 恢复表结构（如果表结构也被破坏了，如 DROP TABLE）
-- 本例中 TRUNCATE 只删数据，结构还在，可以跳过这步
-- RESTORE TABLE sysdba.user_orders STRUCT FROM BACKUPSET '/dmdata/backup/table/T_USER_ORDERS';

-- 3. 恢复表数据（联机执行）

RESTORE TABLE sysdba.user_orders FROM BACKUPSET '/dmdata/backup/table/T_USER_ORDERS';

-- 4. 验证恢复结果

SELECT * FROM user_orders;
SELECT COUNT(*) FROM user_orders;  -- 应该恢复为 5 条

-- 检查数据内容是否完整

SELECT * FROM user_orders WHERE order_id IN (1, 4, 5);

-- 模拟删除 user_logs 的部分数据（不是全删，是 DELETE）

DELETE FROM user_logs WHERE log_id = 2;
COMMIT;

SELECT * FROM user_logs;

-- 恢复（表级恢复会替换整个表为备份时的状态）

RESTORE TABLE sysdba.user_logs FROM BACKUPSET '/dmdata/backup/table/T_USER_LOGS';

-- 1. 查看表上的约束和索引

SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'USER_LOGS';
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'USER_LOGS';

-- 1. 删除主键约束（会自动删除关联索引）

ALTER TABLE user_logs DROP CONSTRAINT CONS134218797;

-- 2. 删除剩余普通索引

DROP INDEX INDEX33555469;

-- 3. 查看确认无二级索引

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'USER_LOGS';

-- 应该只剩 CLUSTER 聚集索引

-- 4. 执行表恢复

RESTORE TABLE user_logs FROM BACKUPSET '/dmdata/backup/table/T_USER_LOGS';

-- 5. 恢复后重建主键和索引

ALTER TABLE user_logs ADD PRIMARY KEY (log_id);

-- 6. 验证

SELECT * FROM user_logs;

-- 验证（log_id=3 应该恢复了）

SELECT * FROM user_logs;  -- 应该显示 3 条（包含 log_id=3）

-- 查看数据库级备份

SELECT 
    BACKUP_ID,
    BACKUP_NAME, 
    BACKUP_PATH, 
    CASE TYPE 
        WHEN 0 THEN 'FULL' 
        WHEN 1 THEN 'INCREMENT' 
        WHEN 2 THEN 'TABLE' 
        WHEN 3 THEN 'ARCHIVE' 
    END AS BAK_TYPE,
    BASE_NAME,
    BEGIN_LSN,
    END_LSN,
    BACKUP_TIME,
    DESC#
FROM V$BACKUPSET 
ORDER BY BACKUP_TIME DESC;

-- 查看备份片信息（物理文件）

SELECT * FROM V$BACKUPSET_BKP WHERE BACKUP_ID IN (
    SELECT BACKUP_ID FROM V$BACKUPSET WHERE BACKUP_NAME='FULL_BASE_01'
);

-- 查看备份集搜索路径

SELECT * FROM V$BACKUPSET_SEARCH_DIRS;

脱机备份

SELECT CURRENT_LSN FROM V$RLOG;
SELECT COUNT(*) FROM user_orders;  -- 应显示 5 条
SELECT COUNT(*) FROM user_logs;    -- 应显示 3 条

# 停止数据库（必须正常关闭，确保一致性）

systemctl stop DmServiceDMSERVER01.service
systemctl stop DmServiceDMSERVER02.service

# 确认进程已停止

ps -ef | grep dmserver | grep DM1

# 应无输出

cd /home/dmdba/dmdbms/bin
./dmrman
BACKUP DATABASE '/dmdata/data/DM1/dm.ini' FULL BACKUPSET '/dmdata/backup/full/DM1_OFFLINE_FULL' BACKUPINFO '脱机全库基备份';

# 启动 DM1

systemctl start DmServiceDMSERVER01.service

# 登录并插入新数据

/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5236'

INSERT INTO user_orders VALUES (6, '孙八', 1299.00, CURRENT_TIMESTAMP, '已发货');
INSERT INTO user_orders VALUES (7, '周九', 3599.00, CURRENT_TIMESTAMP, '已支付');
COMMIT;

-- 记录当前时间（用于PITR）

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS current_time FROM DUAL;

-- 假设：2026-02-02 23:26:05

INSERT INTO user_orders VALUES (8, '吴十', 99.00, CURRENT_TIMESTAMP, '已取消');
COMMIT;
exit

# 停止 DM1

systemctl stop DmServiceDMSERVER01.service

# 进入 DMRMAN 交互模式

./dmrman

# 执行增量备份（必须指定 WITH BACKUPDIR）

RMAN> BACKUP DATABASE '/dmdata/data/DM1/dm.ini' INCREMENT WITH BACKUPDIR '/dmdata/backup/full' BACKUPSET '/dmdata/backup/increment/DM1_OFFLINE_INC' BACKUPINFO '脱机增量备份';

# 预期输出：backup successfully!

RMAN> exit

# 进入 DMRMAN（数据库保持关闭状态）
./dmrman

# 备份所有归档

RMAN> BACKUP ARCHIVE LOG ALL DATABASE '/dmdata/data/DM1/dm.ini' BACKUPSET '/dmdata/backup/archive/DM1_OFFLINE_ARCH' BACKUPINFO '脱机归档备份';

# 进入 DMRMAN
./dmrman

# 将 DM1 的脱机全备还原到 DM2

RESTORE DATABASE '/dmdata/data/DM2/dm.ini' FROM BACKUPSET '/dmdata/backup/full/DM1_OFFLINE_FULL';

# 预期输出：restore successfully!
exit

./dmrman

# 仅从备份集恢复（不含后续归档）

RMAN> RECOVER DATABASE '/dmdata/data/DM2/dm.ini' FROM BACKUPSET '/dmdata/backup/full/DM1_OFFLINE_FULL';

# 或者应用所有本地归档（含第8条数据）

RMAN> RECOVER DATABASE '/dmdata/data/DM2/dm.ini' WITH ARCHIVEDIR '/dmdata/arch/DM1';

# 必须更新魔数，否则 DM2 无法启动

RMAN> RECOVER DATABASE '/dmdata/data/DM2/dm.ini' UPDATE DB_MAGIC;

# 预期输出：recover successfully!
RMAN> exit

# 启动 DM2

systemctl start DmServiceDMSERVER02.service

# 登录验证

/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5237'

-- 验证数据（PITR应显示7条，全恢复显示8条）

SELECT COUNT(*) FROM user_orders;
SELECT * FROM user_orders ORDER BY order_id;
exit

# 停止 DM1

systemctl stop DmServiceDMSERVER01.service

# 删除表空间文件

rm -f /dmdata/data/DM1/TS_TEST.DBF

# 进入 DMRMAN
./dmrman

# 还原表空间（使用实验一备份的 TS_TEST_FULL）

RMAN> RESTORE DATABASE '/dmdata/data/DM1/dm.ini' TABLESPACE TS_TEST FROM BACKUPSET '/dmdata/backup/tablespace/TS_TEST_FULL';

# 恢复表空间（应用归档）

RMAN> RECOVER DATABASE '/dmdata/data/DM1/dm.ini' TABLESPACE TS_TEST;

# 注意：表空间恢复不需要 UPDATE DB_MAGIC
RMAN> exit

# 启动 DM1

systemctl start DmServiceDMSERVER01.service

# 验证

/home/dmdba/dmdbms/bin/disql 'SYSDBA/"Dameng@123"@localhost:5236'
SELECT COUNT(*) FROM user_logs;  -- 应显示3条
exit

./dmrman

# 查看特定备份集元数据

RMAN> SHOW BACKUPSET '/dmdata/backup/full/DM1_OFFLINE_FULL' INFO META;

# 查看目录下所有备份集

RMAN> SHOW BACKUPSETS WITH BACKUPDIR '/dmdata/backup/full';

RMAN> exit

./dmrman

# 检查备份集完整性

RMAN> CHECK BACKUPSET '/dmdata/backup/full/DM1_OFFLINE_FULL';

# 返回：check backupset successfully.
RMAN> exit

https://eco.dameng.com