DM7 RAC数据库恢复成单机数据库

DM7 RAC数据库恢复成单机数据库
1) 启动RMAN备份数据库,保证服务器处于脱机状态。

RMAN> backup database '/dm7/data/rac0_config/dm.ini' full backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
backup database '/dm7/data/rac0_config/dm.ini' full backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
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[0] max_lsn: 76986
EP[0] adjust cur_lsn from [76986] to [77001]
BACKUP DATABASE [rac], execute......
CMD CHECK LSN......
BACKUP DATABASE [rac], collect dbf......
CMD CHECK ......
DBF BACKUP SUBS......
total 1 packages processed...
total 2 packages processed...
total 3 packages processed...
total 4 packages processed...
total 5 packages processed...
DBF BACKUP MAIN......
BACKUPSET [/dm7/backup/db_rac_full_bak_recover_dbmagic] END, CODE [0]......
META GENERATING......
total 9 packages processed...
total 9 packages processed!
CMD END.CODE:[0]
backup successfully!
time used: 3887.714(ms)

RMAN> show backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
show backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';




system path:           +DMDATA/data/rac
db magic:              -1632052820
permanent magic:       450923536
rac node:              2
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:         rac
backup name:           DB_FULL_rac_20200616_154323_000689
backupset description:
backupset ID :         1145407476
parent backupset ID:     -1
META file size :       74240
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          database
mpp_timestamp:         1592293401
ddl_clone:             FALSE
mpp_flag:              FALSE
backup level:          offline
backup type:           full
without log:           FALSE
START_LSN:             76987
START_SEQ:             309
END_LSN:               77001
END_SEQ:               308
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-16 15:43:25
min trx start lsn:     76987
min exec ver:          0x0701060C
pkg size:              0x02000000



RAC EP:            0
RAC status:          OK
RAC begin lsn:       76987
RAC begin seq:       309
RAC end lsn:         77001
RAC end seq:         308
RAC base begin lsn:  -1
RAC base begin seq:  -1
RAC base end lsn:    -1
RAC base end seq:    -1

RAC EP:            1
RAC status:          OK
RAC begin lsn:       77002
RAC begin seq:       308
RAC end lsn:         77001
RAC end seq:         307
RAC base begin lsn:  -1
RAC base begin seq:  -1
RAC base end lsn:    -1
RAC base end seq:    -1


backupset directory: /dm7/backup/db_rac_full_bak_recover_dbmagic
backupset name:        db_rac_full_bak_recover_dbmagic
backup data file num:  5
backup piece num:      1


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |26103     |db_rac_full_bak_recover_dbmagic.bak                     |DATA


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len
1         |0         |SYSTEM                           |0         |+DMDATA/data/rac/system.dbf      |                                 |134217728
2         |1         |ROLL                             |0         |+DMDATA/data/rac/roll.dbf        |                                 |134217728
3         |4         |MAIN                             |0         |+DMDATA/data/rac/main.dbf        |                                 |134217728
4         |5         |TS_FOR_DBF                       |0         |+DMDATA/data/rac/ts_for_dbf_01.dbf|                                 |134217728
5         |5         |TS_FOR_DBF                       |1         |+DMDATA/data/rac/ts_for_dbf_02.dbf|                                 |134217728


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time


show backupsets successfully.
time used: 10.655(ms)

2) 准备目标库,可以使用备份库,也可以重新生成库。这里是在别一台服务器上创建新库,重新生成库操作如下:

[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          -1238913195

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

LINEID     DB_MAGIC
---------- -----------
1          1319824724

used time: 0.984(ms). Execute id is 810.

3)将备份集和归档日志文件复制到目标库所在主机上

[dmdba@dmrac1 backup]$ scp -r db_rac_full_bak_recover_dbmagic dmdba@10.10.10.187:/dm_home/dmdbms/backup/
The authenticity of host ‘10.10.10.187 (10.10.10.187)’ can’t be established.
RSA key fingerprint is 34:9a:d7:67:6d:be:20:45:21:ce:96:82:23:e5:e9:11.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘10.10.10.187’ (RSA) to the list of known hosts.
dmdba@10.10.10.187’s password:
db_rac_full_bak_recover_dbmagic.meta 100% 73KB 72.5KB/s 00:00
db_rac_full_bak_recover_dbmagic.bak 100% 25MB 25.5MB/s 00:00

[dmdba@dmrac1 data]$ scp -r local_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.10.187’s password:
ARCHIVE_LOCAL1_20200528171255857_0.log 100% 157KB 156.5KB/s 00:00
[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.10.187’s password:
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
[dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.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_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]$

4)检验备份

RMAN> check backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
check backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
CMD END.CODE:[0]
check backupset successfully.
time used: 5.877(ms)

5)还原数据库

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
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_rac_full_bak_recover_dbmagic] 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 7 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 16226.253(ms)

6)恢复数据库

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini'  update db_magic ;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' update db_magic;
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: 76986
[-8308]:forbid updating database magic, need redo log

这里使用update db_magic选项出错了,提示需要重做日志文件。下面去掉update db_magic选项,因为每次还原恢复时也是一定会更新db_magic值。

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: 76986
min_ckpt_lsn =
The RAC recover total redo 0 ptx
EP(0) slot ctl page(1, 0, 16) trxid = 9986
EP(1) slot ctl page(1, 0, 17) trxid = 8457
EP[0] Recover LSN from 76987 to 77001.
EP[1] Recover LSN from 77002 to 77001.
Recover from archive log finished, time used:0.006s.
recover successfully!
time used: 7027.075(ms)

7)检查数据,查询表t1的数据可以验证数据恢复成功

[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> desc t1;

LINEID     NAME TYPE$       NULLABLE
---------- ---- ----------- --------
1          ID   INTEGER     Y
2          NAME VARCHAR(20) Y

used time: 20.311(ms). Execute id is 3.
SQL> select * from t1;

LINEID     ID          NAME
---------- ----------- ----
1          1           jy
2          2           hy

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

8)检查db_magic,还原恢复后db_magic从-1632052820变成了1009602608,

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.

DM7配置远程归档

配置远程归档
与本地归档一样,远程归档也是配置在dmarch.ini文件中,远程归档相关的主要几个配置项包括:
1. ARCH_TYPE设置为REMOTE,表示是远程归档
2. ARCH_DEST设置为远程数据库实例名,表示REDO日志发送到这个节点
3. ARCH_INCOMING_PATH设置为本地存储路径,用于保存ARCH_DEST实例发送的REDO日志

一般建议DMRAC集群中的节点,在配置本地归档之外,再交叉配置集群中所有其他节点的远程归档。查询V$DM_ARCH_INI、V$ARCH_STATUS等动态视图可以获取归档配置以及归档状态等相关信息。下面以两节点DMRAC集群为例,说明如何配置远程归档,RAC0和RAC1是DMRAC集群中的两个实例,交叉进行REMOTE归档配置:

1)修改数据库为MOUNT状态。
rac0实例:

SQL> alter database mount;
executed successfully
used time: 00:00:01.852. Execute id is 0.

rac1实例:

SQL> alter database mount;
executed successfully
used time: 00:00:01.853. Execute id is 0.

2)配置本地归档。
rac0实例:

SQL> alter database add archivelog 'dest=/dm7/data/rac0_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 3.459(ms). Execute id is 0.

rac1实例:

SQL> alter database add archivelog 'dest=/dm7/data/rac1_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 3.000(ms). Execute id is 0.

3)配置远程归档。
rac0实例:

SQL> alter database add archivelog 'dest=RAC1,type=remote,file_size=256,space_limit=0,incoming_path=/dm7/data/rac1_arch';
executed successfully
used time: 2.836(ms). Execute id is 0.

rac1实例:

SQL> alter database add archivelog 'dest=RAC0,type=remote,file_size=256,space_limit=0,incoming_path=/dm7/data/rac0_arch';
executed successfully
used time: 2.865(ms). Execute id is 0.

4)开启归档模式。
rac0实例:

SQL> alter database archivelog;
executed successfully
used time: 11.553(ms). Execute id is 0.

rac1实例:

SQL> alter database archivelog;
executed successfully
used time: 10.644(ms). Execute id is 0.

5)修改数据库为OPEN状态。
rac0实例:

SQL> alter database open;
executed successfully
used time: 00:00:01.362. Execute id is 0.

rac1实例:

SQL> alter database open;
executed successfully
used time: 00:00:02.341. Execute id is 0.

6)查看归档配置文件dmarch.ini
rac0实例:

[dmdba@dmrac1 rac0_config]$ pwd
/dm7/data/rac0_config
[dmdba@dmrac1 rac0_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY                 = 1

[ARCHIVE_LOCAL1]
        ARCH_TYPE                       = LOCAL
        ARCH_DEST                       = /dm7/data/rac0_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

[ARCH_REMOTE1]
        ARCH_TYPE                       = REMOTE
        ARCH_DEST                       = RAC1
        ARCH_INCOMING_PATH              = /dm7/data/rac1_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

rac1实例:

[dmdba@dmrac2 rac1_config]$ pwd
/dm7/data/rac1_config
[dmdba@dmrac2 rac1_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY                 = 1

[ARCHIVE_LOCAL1]
        ARCH_TYPE                       = LOCAL
        ARCH_DEST                       = /dm7/data/rac1_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

[ARCH_REMOTE1]
        ARCH_TYPE                       = REMOTE
        ARCH_DEST                       = RAC0
        ARCH_INCOMING_PATH              = /dm7/data/rac0_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

7)检查ARCH_INI参数是否为1
rac0实例:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  1          0         1         N       1          1          dmarch.ini  SYS

used time: 10.267(ms). Execute id is 9.

rac1实例:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  1          0         1         N       1          1          dmarch.ini  SYS

used time: 30.225(ms). Execute id is 19.

8)检查是否生成了归档日志文件(在打开数据库时就会生成)
本地归档日志文件
rac0实例:

[dmdba@dmrac1 rac0_arch]$ pwd
/dm7/data/rac0_arch
[dmdba@dmrac1 rac0_arch]$ ls -lrt
total 136
-rw-r--r-- 1 dmdba dinstall 268435456 May 28 20:21 ARCHIVE_LOCAL1_20200528202150715_0.log

rac1实例:

[dmdba@dmrac2 rac1_arch]$ pwd
/dm7/data/rac1_arch
[dmdba@dmrac2 rac1_arch]$ ls -lrt
total 136
-rw-r--r-- 1 dmdba dinstall 268435456 May 28 20:21 ARCHIVE_LOCAL1_20200528202151877_1.log

远程归档日志文件
rac0实例:

[dmdba@dmrac1 rac1_arch]$ pwd
/dm7/data/rac1_arch
[dmdba@dmrac1 rac1_arch]$ ls -lrt
total 136
-rw-r--r-- 1 dmdba dinstall 268435456 May 28 20:21 ARCH_REMOTE1_20200528202146001_1.log

rac1实例:

[dmdba@dmrac2 rac0_arch]$ pwd
/dm7/data/rac0_arch
[dmdba@dmrac2 rac0_arch]$ ls -lrt
total 136
-rw-r--r-- 1 dmdba dinstall 268435456 May 28 20:21 ARCH_REMOTE1_20200528202156601_0.log

DM7配置本地归档

配置本地归档
归档配置有两种方式:一是联机归档配置,数据库服务器启动情况下,使用SQL语句完成dmarch.ini和ARCH_INI配置;二是手动配置归档,数据库服务器未启动的情况下,手动编写dmarch.ini文件和设置参数ARCH_INI。下面将分别说明这两种归档如何配置。

联机配置归档
使用SQL语句配置本地归档。语法如下:
ALTER DATABASE <ADD|MODIFY|DELETE> ARCHIVELOG <归档配置语句>;
<归档配置语句>::= ‘DEST = <归档目标>,TYPE = <归档类型>’
<归档类型>::=<local方式>|<remote方式>
<local方式>::=LOCAL [,FILE_SIZE = <文件大小>][,SPACE_LIMIT = <空间大小限制>]
<remote方式>::=REMOTE [,FILE_SIZE = <文件大小>][,SPACE_LIMIT = <空间大小限制>],INCOMING_PATH = <归档存放路径>

使用SQL语句开启和关闭归档模式。
语法如下:

ALTER DATABASE ARCHIVELOG | NOARCHIVELOG;

在归档模式下,不允许删除本地归档。
例如,RAC环境联机归档配置如下:
1)修改数据库为MOUNT状态。
节点1:

SQL> alter database mount;
executed successfully
used time: 00:00:01.852. Execute id is 0.

节点2:

SQL> alter database mount;
executed successfully
used time: 00:00:01.853. Execute id is 0.

2)配置本地归档。
节点1:

SQL> alter database add archivelog 'dest=/dm7/data/local_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 3.110(ms). Execute id is 0.

节点2:

SQL> alter database add archivelog 'dest=/dm7/data/local_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 3.171(ms). Execute id is 0.

3)开启归档模式。
节点1:

SQL> alter database archivelog;
executed successfully
used time: 11.986(ms). Execute id is 0.

节点2:

SQL> alter database archivelog;
executed successfully
used time: 11.780(ms). Execute id is 0.

4)修改数据库为OPEN状态。
节点1:

SQL> alter database open;
executed successfully
used time: 00:00:01.466. Execute id is 0.

节点2:

SQL> alter database open;
executed successfully
used time: 711.999(ms). Execute id is 0.

5)查看归档配置文件dmarch.ini
节点1:

[dmdba@dmrac1 rac0_config]$ pwd
/dm7/data/rac0_config
[dmdba@dmrac1 rac0_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY                 = 1    --性能模式,是否等待重演完成,取值0:高性能模式, 1:数据一致模式。本地归档取值NULL

[ARCHIVE_LOCAL1]
        ARCH_TYPE                       = LOCAL
        ARCH_DEST                       = /dm7/data/local_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

节点2:

[dmdba@dmrac2 rac1_config]$ pwd
/dm7/data/rac1_config
[dmdba@dmrac2 rac1_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY                 = 1       --性能模式,是否等待重演完成,取值0:高性能模式, 1:数据一致模式。本地归档取值NULL

[ARCHIVE_LOCAL1]
        ARCH_TYPE                       = LOCAL
        ARCH_DEST                       = /dm7/data/local_arch
        ARCH_FILE_SIZE                  = 256
        ARCH_SPACE_LIMIT                = 0

6)检查ARCH_INI参数是否为1
节点1:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  1          0         1         N       1          1          dmarch.ini  SYS

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

节点2:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  1          0         1         N       1          1          dmarch.ini  SYS

used time: 29.498(ms). Execute id is 13.

禁用归档
1修改数据库为MOUNT状态。
节点1:

SQL> alter database mount;
executed successfully
used time: 00:00:01.858. Execute id is 0.

节点2:

SQL> alter database mount;
executed successfully
used time: 00:00:01.858. Execute id is 0.

2.禁用归档
节点1:

SQL> alter database noarchivelog;
executed successfully
used time: 42.916(ms). Execute id is 0.

节点2:

SQL> alter database noarchivelog;
executed successfully
used time: 42.533(ms). Execute id is 0.

3.删除配置的本地归档设置
节点1:

SQL> alter database delete archivelog 'dest=/dm7/data/local_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 0.931(ms). Execute id is 0.

节点2:

SQL> alter database delete archivelog 'dest=/dm7/data/local_arch,type=local,file_size=256,space_limit=0';
executed successfully
used time: 0.952(ms). Execute id is 0.

4.删除之前生成的归档日志文件(可选项)
节点1:

SQL> sf_archivelog_delete_before_time(sysdate+1);
DMSQL executed successfully
used time: 2.907(ms). Execute id is 7.

节点2:

SQL> sf_archivelog_delete_before_time(sysdate+1);
DMSQL executed successfully
used time: 3.542(ms). Execute id is 16.

5.打开数据库
节点1:

SQL> alter database open;
executed successfully
used time: 00:00:01.609. Execute id is 0.

节点2:

SQL> alter database open;
executed successfully
used time: 00:00:02.304. Execute id is 0.

6.检查归档配置文件dmarch.ini的内容是否已经被删除
节点1:

[dmdba@dmrac1 rac0_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY      = 1        --性能模式,是否等待重演完成,取值0:高性能模式, 1:数据一致模式。本地归档取值NULL

节点2:

[dmdba@dmrac2 rac1_config]$ cat dmarch.ini
#DaMeng Database Archive Configuration file
#this is comments

        ARCH_WAIT_APPLY      = 1        --性能模式,是否等待重演完成,取值0:高性能模式, 1:数据一致模式。本地归档取值NULL

7.检查ARCH_INI参数是否为0
节点1:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  0          0         1         N       0          0          dmarch.ini  SYS

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

节点2:

SQL> select * from v$dm_ini where para_name='ARCH_INI';

LINEID     PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------- ---------- --------- --------- ------- ---------- ---------- ----------- ---------
1          ARCH_INI  0          0         1         N       0          0          dmarch.ini  SYS

used time: 14.366(ms). Execute id is 18.

手动配置归档示例如下:
1)手动编辑dmarch.ini文件,之后保存在dm.ini所在的目录。dmarch.ini文件内容如下:

[ARCHIVE_LOCAL1]
ARCH_TYPE = LOCAL
ARCH_DEST = /dm7/data/local_arch
ARCH_FILE_SIZE = 256
ARCH_SPACE_LIMIT = 0

2)编辑dm.ini文件,设置参数ARCH_INI=1,保存。
3)启动服务器,服务器已运行于归档模式。

多路归档,指配置多个本地归档。配置的第一个归档,称为第一路归档,后面依次是第二路、第三路……

达梦使用联机SQL执行表空间还原(二)

使用disql执行表空间还原(一)已经对表空间的最基本最常用的还原场景进行了描述,这里将介绍一些不常用且较复杂的还原场景。主要内容包括:
1指定归档目录还原
2还原表空间中指定的数据文件
3指定映射文件还原

1.指定归档目录还原
由于磁盘空间的影响,服务器归档可能出现分布在多个目录的情况。出现这种情况时就需要指定归档目录还原。还原时指定多个归档目录的操作步骤如下:
1) 备份用户表空间MAIN。

SQL> backup tablespace main to ts_main_bak_for_arch backupset 'E:\dmdbms\backup\ts_main_bak_for_arch';
操作已执行
已用时间: 00:00:01.116. 执行号:80.

2) 校验备份。此步骤可选。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_bak_for_arch');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_bak_for_arch')
---------- ---------------------------------------------------------------
1          1

已用时间: 18.229(毫秒). 执行号:81.
3) 还原用户表空间MAIN。假设归档日志分布在目录为E:\dmdbms\data\arch和E:\dmdbms\data\arch1两个目录下。
SQL> alter tablespace main offline;
操作已执行
已用时间: 111.415(毫秒). 执行号:82.
SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_main_bak_for_arch' with backupdir 'E:\dmdbms\data\arch','E:\dmdbms\data\arch1';
操作已执行
已用时间: 414.930(毫秒). 执行号:83.
SQL> alter tablespace main online;
操作已执行
已用时间: 27.742(毫秒). 执行号:84.

2.还原表空间中指定的数据文件
DM7不仅支持从数据库备份和表空间备份中还原表空间,还支持还原表空间中特定的数据文件。若表空间已经被破坏,则不允许执行特定数据文件的还原。使用数据库备份还原表空间或表空间中的数据文件与使用表空间备份操作类似,区别在于RESTORE语句中指定的备份集为数据库备份集。因此,本节仅以使用表空间备份为例说明还原数据文件的操作步骤。
1) 创建待备份的表空间TS_FOR_RES_01, 并在库目录下创建3个数据文件。

SQL> create tablespace ts_for_res_01 datafile 'ts_for_res_01_01.dbf' size 128;
操作已执行
已用时间: 83.275(毫秒). 执行号:87.
SQL> alter tablespace ts_for_res_01 add datafile 'ts_for_res_01_02.dbf' size 128;
操作已执行
已用时间: 38.806(毫秒). 执行号:88.
SQL> alter tablespace ts_for_res_01 add datafile 'ts_for_res_01_03.dbf' size 128;
操作已执行
已用时间: 42.169(毫秒). 执行号:89.

2) 备份表空间。

SQL> backup tablespace ts_for_res_01 backupset 'E:\dmdbms\backup\ts_for_res_01_bak';
操作已执行
已用时间: 00:00:01.161. 执行号:93.

3) 查询数据文件的文件编号或路径。还原指定的数据文件需知道数据文件对应的文件编号或路径,相关信息可通过查询动态视图V$DATAFILE获取。

SQL> select group_id,id,path from v$datafile;

行号       GROUP_ID    ID          PATH
---------- ----------- ----------- ----------------------------------------
1          0           0           E:\dmdbms\data\jydm\SYSTEM.DBF
2          1           0           E:\dmdbms\data\jydm\ROLL.DBF
3          3           0           E:\dmdbms\data\jydm\TEMP.DBF
4          4           0           E:\dmdbms\data\jydm\MAIN.DBF
5          5           0           E:\dmdbms\data\jydm\BOOKSHOP.DBF
6          6           0           E:\dmdbms\data\jydm\DMHR.DBF
7          7           0           E:\dmdbms\data\jydm\ts_for_res_01_01.dbf
8          7           1           E:\dmdbms\data\jydm\ts_for_res_01_02.dbf
9          7           2           E:\dmdbms\data\jydm\ts_for_res_01_03.dbf

9 rows got

已用时间: 1.655(毫秒). 执行号:94.

如果想还原ts_for_res_01_02.dbf和ts_for_res_01_03.dbf两个数据文件,通过查询结果可知它们的文件编号(1和2)以及相应的路径。

4) 校验备份。此步骤为可选。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_for_res_01_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_for_res_01_bak')
---------- ------------------------------------------------------------
1          1

已用时间: 32.108(毫秒). 执行号:95.

5) 通过文件编号还原表空间TS_FOR_RES_01中的数据文件ts_for_res_01_02.dbf和ts_for_res_01_03.dbf。

SQL> alter tablespace ts_for_res_01 offline;
操作已执行
已用时间: 105.157(毫秒). 执行号:96.
SQL> restore tablespace ts_for_res_01 datafile 1,2 from backupset 'E:\dmdbms\backup\ts_for_res_01_bak';
操作已执行
已用时间: 358.862(毫秒). 执行号:97.
如果不想使用文件编号还原,使用指定数据文件路径还原数据文件的语句如下:
SQL> restore tablespace ts_for_res_01 datafile 'E:\dmdbms\data\jydm\ts_for_res_01_02.dbf','E:\dmdbms\data\jydm\ts_for_res_01_03.dbf' from backupset 'E:\dmdbms\backup\ts_for_res_01_bak';
操作已执行
已用时间: 344.008(毫秒). 执行号:98.
SQL> alter tablespace ts_for_res_01 online;
操作已执行
已用时间: 24.644(毫秒). 执行号:99.

3.指定映射文件还原
映射文件用于指定存放还原目标路径,即备份集里面的数据文件路径。指定映射文件还原可以重新指定备份集中数据文件的路径。下面以MAIN表空间为例说明如何使用映射文件还原。
1) 备份MAIN表空间。

SQL>BACKUP TABLESPACE MAIN BACKUPSET '/home/dm_bak/ts_bak_for_map';

2) 使用DMRMAN的DUMP命令生成映射文件ts_bak_mapped.txt,存至’E:\dmdbms\backup\目录。文件中指定数据文件MAIN.DBF还原后的路径为E:\dmdbms\data\jydm\MAIN.DBF。

RMAN> dump backupset  'E:\dmdbms\backup\ts_main_bak_for_map'  device type disk mapped file  'E:\dmdbms\backup\ts_bak_mapped.txt';
dump backupset 'E:\dmdbms\backup\ts_main_bak_for_map' device type disk mapped file 'E:\dmdbms\backup\ts_bak_mapped.txt';
dump mapped file successfully.
time used: 28.360(ms)



/**************************************************************/
/***  Delete the unnecessary modified groups                 **/
/***  Modify the data_path or mirror_path only in one group  **/
/**************************************************************/

/**=============================================================**/
/*[jydm_MAIN_FIL_0]*/
fil_id         = 0
ts_id          = 4
ts_name        = MAIN
data_path      = E:\dmdbms\data\jydm\MAIN.DBF
mirror_path    =

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

3) 还原MAIN表空间。

SQL> alter tablespace main offline;
操作已执行
已用时间: 119.506(毫秒). 执行号:102.
SQL> select * from tab_for_res_2;
select * from tab_for_res_2;
[-3408]:表空间[MAIN]处于脱机状态.
已用时间: 1.372(毫秒). 执行号:0.
SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_main_bak_for_map' mapped file 'E:\dmdbms\backup\ts_bak_mapped.txt';
操作已执行
已用时间: 377.253(毫秒). 执行号:104.
SQL> alter tablespace main online;
操作已执行
已用时间: 17.211(毫秒). 执行号:105.
SQL> select * from tab_for_res_2;

行号       C1
---------- -----------
1          1
2          2
3          3
4          4
5          5
6          6
7          7
8          8
9          9
10         10

10 rows got

已用时间: 2.604(毫秒). 执行号:106.

达梦使用联机SQL执行表空间还原(一)

使用disql执行表空间还原
介绍使用disql工具如何还原表空间。主要内容包括:
1.使用表空间完全备份还原表空间
2.使用表空间增量备份还原表空间
3.使用数据库备份还原表空间

1.使用表空间完全备份还原表空间
表空间的还原、恢复操作是一次性完成,因此还原后不需要执行恢复操作。使用表空间完全备份还原表空间的完整步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 备份表空间。

SQL> backup tablespace main to ts_main_bak backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 00:00:01.069. 执行号:9.

4) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_bak')
---------- ------------------------------------------------------
1          1

已用时间: 17.741(毫秒). 执行号:10.

5) 修改表空间为脱机。

SQL> alter tablespace main offline;
操作已执行
已用时间: 137.581(毫秒). 执行号:11.

6) 还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_main_bak';
操作已执行
已用时间: 276.897(毫秒). 执行号:12.

7) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 17.971(毫秒). 执行号:13.

2.使用表空间增量备份还原表空间
在增量备份与基备份同目录情况下,使用增量备份还原表空间步骤与完全备份一致。当增量备份与基备份不在同一个目录下时需要使用RESTORE TABLESPACE…WITH BACKUPDIR语句还原。这里以后面一种情况为例说明使用增量备份还原表空间。步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 完全备份表空间。

SQL> backup tablespace main to ts_main_full_bak backupset 'E:\dmdbms\backup\ts_main_full_bak';
操作已执行
已用时间: 00:00:01.095. 执行号:35.

4) 多次增量备份表空间。

SQL> begin
2   for i in 1 .. 100 loop
3    insert into t1 values(i);
4   end loop;
5   commit;
6   end;
7   /
DMSQL 过程已成功完成
已用时间: 3.383(毫秒). 执行号:36.
SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak' backupset 'E:\dmdbms\backup1\ts_main_incr_bak_01';
操作已执行
已用时间: 00:00:01.079. 执行号:37.


SQL> begin
2   for i in 101 .. 200 loop
3   insert into t1 values(i);
4   end loop;
5   end;
6   /
DMSQL 过程已成功完成
已用时间: 1.274(毫秒). 执行号:38.
SQL> alter system switch logfile;
操作已执行
已用时间: 14.166(毫秒). 执行号:0.
SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E:\dmdbms\backup1\ts_main_incr_bak_01' backupset 'E:\dmdbms\backup2\ts_main_incr_bak_02';
操作已执行
已用时间: 00:00:01.081. 执行号:39.

5) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_full_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_full_bak')
---------- -----------------------------------------------------------
1          1

已用时间: 20.931(毫秒). 执行号:40.
SQL> select sf_bakset_check('disk','E:\dmdbms\backup1\ts_main_incr_bak_01');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup1\ts_main_incr_bak_01')
---------- ---------------------------------------------------------------
1          1

已用时间: 31.184(毫秒). 执行号:41.
SQL> select sf_bakset_check('disk','E:\dmdbms\backup2\ts_main_incr_bak_02');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup2\ts_main_incr_bak_02')
---------- ---------------------------------------------------------------
1          1

已用时间: 25.171(毫秒). 执行号:42.

6) 修改表空间为脱机

SQL> alter tablespace main offline;
操作已执行
已用时间: 98.420(毫秒). 执行号:43.

7) 使用增量备份E:\dmdbms\backup2\ts_main_incr_bak_02还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup2\ts_main_incr_bak_02' with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E:\dmdbms\backup1\ts_main_incr_bak_01';
操作已执行
已用时间: 370.046(毫秒). 执行号:44.

8) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 15.717(毫秒). 执行号:45.
SQL> select count(*) from t1;

行号       COUNT(*)
---------- --------------------
1          200

已用时间: 1.345(毫秒). 执行号:46.

3.使用数据库备份还原表空间
使用数据库备份还原表空间可以快速还原一个或多个表空间,而不影响数据库中其他的表空间和对象。在数据库中仅有特定的表空间损坏且没有表空间备份的情况下,可以选择使用此种方式还原。
具体步骤如下:
1) 配置归档。
2) 保证数据库处于OPEN或MOUNT状态。
3) 创建数据库完全备份。

SQL> backup database full to db_jydm_full_bak backupset 'E:\dmdbms\backup\ts_jydm_full_bak';
操作已执行
已用时间: 00:00:01.198. 执行号:50.

4) 校验表空间备份。此步骤为可选,如果确定备份文件合法可不进行备份校验。

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_jydm_full_bak');

行号       SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_jydm_full_bak')
---------- -----------------------------------------------------------
1          1

已用时间: 46.115(毫秒). 执行号:51.

5) 修改表空间为脱机。

SQL> alter tablespace main offline;
操作已执行
已用时间: 96.596(毫秒). 执行号:52.

6) 还原表空间。

SQL> restore tablespace main from backupset 'E:\dmdbms\backup\ts_jydm_full_bak';
操作已执行
已用时间: 326.842(毫秒). 执行号:53.

7) 修改表空间为联机。

SQL> alter tablespace main online;
操作已执行
已用时间: 17.338(毫秒). 执行号:54.
SQL> select count(*) from t1;

行号       COUNT(*)
---------- --------------------
1          200

已用时间: 1.969(毫秒). 执行号:55.
Proudly powered by WordPress | Indrajeet by Sus Hill.