YongJing(敬勇) – 第14页 – 但行好事莫问前程

DM7使用dmrestore工具还原数据库

Posted on 2021年3月10日 by YongJing(敬勇)

使用dmrestore工具还原数据库
1. 概述
DM支持利用还原工具对数据库进行还原。根据不同备份类型，包括联机备份、脱机备份、完全备份以及增量备份，还原工具选择不同的还原策略实现恢复用户数据的目的。首先讨论联机备份与脱机备份，脱机备份仅包含数据页文件的内容，而联机备份文件除了包含数据页文件内容以外，可能还包括备份过程中产生的日志文件内容。因此在利用联机备份还原时，除了拷贝数据页至数据库文件以外，可能还需要重做备份的日志文件内容，达到还原数据库的目的。在还原联机库备份且不带日志的备份时，需要用户指定归档目录，由用户保证备份期间的归档日志存在且完好，在还原库时，需要重做该日志。如果日志不存在，则备份文件就无效，报错归档日志错误。

接下来讨论完全备份与增量备份，对于增量备份，还原工具会根据用户提供备份的信息搜集它的基础备份，若基础备份仍然为增量备份，则工具会继续搜集，直至搜集到一个类型为完全备份的基础备份为止。因此在这个过程中，工具会构造一个基础备份链表，备份链表基于备份间的依赖关系组织。比如，备份A是备份B的增量备份，则可以说A依赖于B。若用户首先进行了一次完全备份A，然后在A的基础上做了增量备份B，随后基于增量备份B又做了增量备份C，最后基于增量备份C做了增量备份D。那么此时利用增量备份D还原数据库时，系统搜集到得备份链表如30.1所示，执行还原时，首先利用完全备份恢复A进行还原，然后依次利用增量备份B，C和D还原，最终完成整个还原过程。需要说明的是，若备份类型为B树备份的数据库备份是不存在增量备份的。

脱机库还原支持无备份文件的还原，原理就是利用归档文件，在目标库上执行REDO操作，把库还原到指定的时间点或者LSN。限制条件是，
1）目标库和归档必须是源自同一个库；2）必须保证源库的归档是完整，否则会出现不可预知的错误，即要保证备份之后产生的归档是完整的。要利用归档还原，需要利用备份文件还原一个目标库，修改目标库db_magic和源库中归档文件的db_magic一致。然后就可以执行还原操作了。库db_magic的修改可以使用dmmdf工具。
dmmdf工具的用法如下：

dmmdf KEYWORD=value

在Windows“命令提示符”窗口中输入带参数的dmmdf命令启动该工具，参数说明见

[dmdba@shard1 dmdbms]$ dmmdf help
Format:  ./dmmdf KEYWORD=value

Example: ./dmmdf TYPE=1 FILE=/opt/data/DAMENG/SYSTEM.DBF

Keyword            Explanation
--------------------------------------------------------------------------------
TYPE                type
                    1: for dbf
                    2: for rlog
                    3: for original bak
                    4: for bakset meta
                    5: for bakset bkp
                    6: for bakset
                    7: batch modify db_magic for arch file
                    8: for database degrades
                    9：batch modify crc_check for DB specified by dm.ini.
                    10：batch modify crc_check for archive log in archive directory.
FILE                file path
DCR_INI             dmdcr.ini path
DB_MAGIC_SRC        db_magic_src,for TYPE=7 and 10
DB_MAGIC_DST        db_magic_dst,for TYPE=7
CRC_CHECK           crc_check,for TYPE=9 and 10
HELP                show this help info

--------------------------------------------------
Example: ./dmmdf TYPE=1 FILE=/opt/data/DAMENG/SYSTEM.dbf
         ./dmmdf TYPE=2 FILE=/opt/data/DAMENG/DAMENG01.log
         ./dmmdf TYPE=3 FILE=/opt/data/DAMENG/bak/dmdb.bak
         ./dmmdf TYPE=4 FILE=/opt/data/DAMENG/bak/bset/nbak.meta
         ./dmmdf TYPE=5 FILE=/opt/data/DAMENG/bak/bset/nbak.bak
         ./dmmdf TYPE=6 FILE=/opt/data/DAMENG/bak/bset
         ./dmmdf TYPE=7 FILE=/opt/data/DAMENG/arch DB_MAGIC_SRC=4734373 DB_MAGIC_DST=4734366
         ./dmmdf TYPE=8 FILE=/opt/data/DAMENG/dm.ctl
         ./dmmdf TYPE=9 FILE=/opt/data/DAMENG/dm.ini,CRC_CHECK=1
         ./dmmdf TYPE=10 FILE=/opt/data/DAMENG/arch，CRC_CHECK=0，DB_MAGIC_SRC=4734373

使用说明：
dmmdf工具可以修改TYPE中指定的文件中的信息，例如db_magic。
DMRESTORE还原工具的用法如下：

DMRESTORE KEYWORD=value 或KEYWORD=(value1,value2,...,valueN)

确认DM数据库服务器已停止后，在Windows“命令提示符”窗口中输入带参数的DMRESTORE命令启动该工具。

[dmdba@shard1 dmdbms]$ dmrestore help
Format: DMRESTORE  KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Eg: DMRESTORE  INI_PATH=/opt/dm7data/dameng/dm.ini FILE=/opt/dm7data/backup/test.bak

Imperative Argument: FILE

Keyword             explain(default value)
--------------------------------------------------------------------------------
INI_PATH            the path of the dmserver's dm.ini file
FILE                backup file
MAPPED_FIL              the path of mapped file, when res_type assign 4.
TIME                the timestamp of be going to recover
ARCHIVE_DIR         the directory at backup, Format (dir1,dir2,dir3,...)
BACKUP_DIR          the stored path of backup file, be used to search backup file, Format (dir1,dir2,dir3,...)
DATAFIL_PATH        the file path at the recovery, Format (id1,path1,id2,path2,id3,path3,...)
MIRROR_PATH         the mirror file path at the recovery, Format (id1,path1,id2,path2,id3,path3,...)
PARALLEL_DIR        the full path of parallel mapping file
RES_TYPE            restore type(0,1,2,3,4), 0: bakfil restore, 1: bakfil show, 2: archfil restore, 3: step increment restore, 4:output mapped file default(0)
END_LSN             restore to end_lsn
LOG                 restore output log file
DUMMY               quiet mode, {(Y)es,(N)o} default:No
PASSWORD            restore with password
ENCRYPT_NAME        restore decrypt with encrypt name
HELP                output help info

INI_PATH:配置文件路径，最大长度为256字节,可选参数

FILE:用于还原的备份文件完整路径,必选参数

MAPPED_FIL:当指定res_type为4时，输出备份文件的映射信息到该文件；如果指定res_type为0或3时，则还原时，会采用MAPPED_FIL文件中的data_path和mirror_path路径为准（如果用户希望使用相对路径策略还原，请删除相关的组，同时修改要修改的data_path和mirror_path）。此外，如果指定MAPPED_FIL参数，则本工具中datafil_path和mirror_path参数会失效；反之，则生效,可选参数

TIME:指定还原的时间点和指定还原的LSN，需要配合ARCHIVE_DIR使用，若指定了ARCHIVE_DIR，既没有指定TIME和END_LSN则会重做所有的归档；如果指定了某一个则会还原到指定的TIME或者LSN；如果都指定了，则会还原到早的一个时间点（或者LSN）；如果指定END_LSN为1，则表示还原到备份的时候，不会redo归档,可选参数

END_LSN:指定还原的时间点和指定还原的LSN，需要配合ARCHIVE_DIR使用，若指定了ARCHIVE_DIR，既没有指定TIME和END_LSN则会重做所有的归档；如果指定了某一个则会还原到指定的TIME或者LSN；如果都指定了，则会还原到早的一个时间点（或者LSN）；如果指定END_LSN为1，则表示还原到备份的时候，不会redo归档,可选参数。

BACKUP_DIR:备份文件存放路径，用于搜索备份文件（默认会搜索备份文件所在的目录，以及库的默认备份目录）,可选参数

ARCHIVE_DIR:归档日志存放的目录，可以指定1~8个。在还原联机库备份且不带日志（WITHOUT LOG）的备份文件时，ARCHIVE_DIR为必选参数,可选参数。

DATAFIL_PATH:指定还原后的新文件路径([编号，路径]，……，[编号，路径])。编号与路径成对出现，最多可以指定16个。编号为大于0的整数。MAPPED_FIL优先,可选参数。

PARALLEL_DIR:并行映射文件存放路径，用于搜索映射文件，可选参数

SHOW_BAK_INFO:可选值（0,1,2）0：表示利用备份还原；1：仅显示备份信息，不进行还原；2：表示使用归档日志进行还原；默认值为0。该参数已被RES_TYPE取代，为兼容之前版本，暂时保留。如果同时指定RES_TYPE和SHOW_BAK_INFO，则RES_TYPE有效，SHOW_BAK_INFO无效,可选参数。

RES_TYPE:可选值（0,1,2,3,4）0：表示利用备份还原；1：仅显示备份信息，不进行还原；2：表示使用归档日志进行还原；3：仅还原单个完全备份或者增量备份；4：仅仅输出备份文件映射信息到文件。默认值为0,可选参数。

MIRROR_PATH:指定备份库中的镜像文件路径在还原时的映射路径。([编号，路径]，……，[编号，路径])。编号与路径成对出现，最多可以指定16个。编号为大于0的整数，映射路径绝对路径。MAPPED_FIL优先,可选参数。

LOG:指定一个文件，还原时日志会写入该文件,可选参数。

DUMMY:静默模式，可以指定{(Y)es,(N)o}。默认No。交互信息处理: 打印(P)。Y：打印所有交互信息。N：不打印交互信息。可选参数。

PASSWORD:还原时，指定备份时的加密密码

ENCRYPT_NAME:还原时用来解密的算法名。缺省算法为AES256_CFB

HELP:打印帮助信息

若设置SHOW_BAK_INFO参数为1，则不需要利用备份进行还原操作，可以不指定INI_PATH；指定了BACKUP_DIR参数时，则会收集备份目录下的所有的备份，并分组输出；如果指定了FILE，且该备份文件是增量备份，则会收集增量备份相关的所有备份输出。

数据库还原时，如果需要重做的日志量较大，将INI参数BUFFER适当设大，可以提高还原的性能。

2. 使用备份文件还原
例如，利用备份文件/dm_home/dmdba/dmdbms/data/jydm/bak/DB_jydm_20200711192046000434.bak还原数据库

[dmdba@shard1 backup]$ dmrestore ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini file=/dm_home/dmdba/dmdbms/data/jydm/bak/DB_jydm_20200711192046000434.bak
restore V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!


backup sig:            BA
backup tool version:   12595
backup db name:        jydm
backup db magic:       -82313077
backup pemnt magic:    1250320462
backup name:           jydm
backup type:           full
backup level:          offline
backup range:          database
backup description:
compressed level:      0
encrypt_type:          0
encrypt_id:            2052
rac node:              0
page check:            0
rlog encrypt:          0
external cipher id:    0
external hash id:      0
length in char:        0
use new hash:          1
backup time:           2020-07-11 19:20:46
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
charset:               0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
mpp_timestamp:         0
crc_check:             TRUE
parallel type:         0
parallel info len:     0
backup db fil num:     8
archive flag:          1
after backup LSN:      27830642


$bak_seq |$file_path                                        |$size(K)       |$used(K)
        1|/dm_home/dmdba/dmdbms/data/jydm/bak/DB_jydm_20200711192046000434.bak|      294912.00|      284866.00


$file_seq |$type|$ts_name       |$state    |$size(K)  |$seq_in_bak    |$off_in_bak(B) |$len_in_bak(B) |$file_path                                        |$mirror_path
         1|DBF  |SYSTEM         |ONLINE    |     23552|              1|          43008|        6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF        |
         2|DBF  |ROLL           |ONLINE    |    229376|              1|        6809600|         401408|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF          |
         3|DBF  |MAIN           |ONLINE    |    210944|              1|        7211008|      171753472|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF          |
         4|DBF  |BOOKSHOP       |ONLINE    |    153600|              1|      178964480|         933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF      |
         5|DBF  |DMHR           |ONLINE    |    131072|              1|      179898368|         425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF          |
         6|DBF  |USERS          |ONLINE    |     51200|              1|      180324352|        1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf       |
         7|DBF  |SYSAUX         |ONLINE    |    179200|              1|      181430272|      110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF        |
         8|DBF  |FG_PERSON      |ONLINE    |    131072|              1|      291686400|          16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF     |

Continue?[Y/N]:Y
restore bak_file: /dm_home/dmdba/dmdbms/data/jydm/bak/DB_jydm_20200711192046000434.bak ...
start restore database...
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM), pages: 826
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF(SYSTEM)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL), pages: 49
end restore file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF(ROLL)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN), pages: 20966
end restore file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF(MAIN)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP), pages: 114
end restore file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF(BOOKSHOP)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR), pages: 52
end restore file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF(DMHR)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS), pages: 135
end restore file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf(USERS)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX), pages: 13459
end restore file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF(SYSAUX)
start restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON), pages: 2
end restore file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF(FG_PERSON)
end restore database data files.

Apply archive log LSN from 27830643 to 27830642, time used:0.000s.
restore finished, code = 0!

restore successfully!
restore time used: 7144.214(ms)

例如，利用归档文件/dm_home/dmdbms/backup/arch还原数据库，这里将使用备份文件还原新的目标库
1.先将原备份库的备份文件和归档文件复制到新还原库的主机上

[dmdba@shard1 backup]$ scp  -r /dm_home/dmdba/dmdbms/data/jydm/bak/DB_jydm_20200711192046000434.bak  dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
DB_jydm_20200711192046000434.bak                                                                                                                                                                          100%  288MB  96.0MB/s   00:03
[dmdba@shard1 backup]$ scp  -r /dm_home/dmdba/dmdbms/data/arch  dmdba@10.138.130.187:/dm_home/dmdbms/backup/
dmdba@10.138.130.187's password:
ARCHIVE_LOCAL1_20200603195207512_0.log                                                                                                                                                                    100%   84MB  21.0MB/s   00:04
ARCHIVE_LOCAL1_20200606020009584_0.log                                                                                                                                                                    100% 6588KB   6.4MB/s   00:00
ARCHIVE_LOCAL1_20200606061513930_0.log                                                                                                                                                                    100%   24KB  24.0KB/s   00:00
ARCHIVE_LOCAL1_20200606062617837_0.log                                                                                                                                                                    100% 7168     7.0KB/s   00:00
ARCHIVE_LOCAL1_20200606062943852_0.log                                                                                                                                                                    100%  300MB 100.0MB/s   00:03
ARCHIVE_LOCAL1_20200606142842948_0.log                                                                                                                                                                    100%  300MB 100.0MB/s   00:03
ARCHIVE_LOCAL1_20200606142846352_0.log                                                                                                                                                                    100%  300MB 100.0MB/s   00:03
ARCHIVE_LOCAL1_20200606142902741_0.log                                                                                                                                                                    100%  300MB  33.3MB/s   00:09
ARCHIVE_LOCAL1_20200606142909259_0.log                                                                                                                                                                    100%  300MB 150.0MB/s   00:02
ARCHIVE_LOCAL1_20200606220653158_0.log                                                                                                                                                                    100%  300MB  50.0MB/s   00:06
ARCHIVE_LOCAL1_20200613210000757_0.log                                                                                                                                                                    100%  300MB  14.3MB/s   00:21
ARCHIVE_LOCAL1_20200617093523748_0.log                                                                                                                                                                    100%   32MB   7.9MB/s   00:04
ARCHIVE_LOCAL1_20200617161801000_0.log                                                                                                                                                                    100% 3926KB   3.8MB/s   00:00
ARCHIVE_LOCAL1_20200617170101954_0.log                                                                                                                                                                    100% 1464KB   1.4MB/s   00:00
ARCHIVE_LOCAL1_20200617173005734_0.log                                                                                                                                                                    100% 8583KB   8.4MB/s   00:01
ARCHIVE_LOCAL1_20200617190859140_0.log                                                                                                                                                                    100%   20MB  20.1MB/s   00:01
ARCHIVE_LOCAL1_20200622160035411_0.log                                                                                                                                                                    100%  220KB 220.0KB/s   00:00
ARCHIVE_LOCAL1_20200623172303743_0.log                                                                                                                                                                    100%  300MB  11.1MB/s   00:27
ARCHIVE_LOCAL1_20200624180051432_0.log                                                                                                                                                                    100%  300MB  10.7MB/s   00:28
ARCHIVE_LOCAL1_20200626193023317_0.log                                                                                                                                                                    100%  300MB  10.7MB/s   00:28
ARCHIVE_LOCAL1_20200630024319628_0.log                                                                                                                                                                    100%   47MB   9.5MB/s   00:05
ARCHIVE_LOCAL1_20200630173436447_0.log                                                                                                                                                                    100%   39MB   9.6MB/s   00:04
ARCHIVE_LOCAL1_20200706091427214_0.log                                                                                                                                                                    100%  300MB   9.7MB/s   00:31
ARCHIVE_LOCAL1_20200710110030273_0.log                                                                                                                                                                    100%  113MB   8.7MB/s   00:13
ARCHIVE_LOCAL1_20200606062454805_0.log                                                                                                                                                                    100% 7680     7.5KB/s   00:00
ARCHIVE_LOCAL1_20200606062942967_0.log                                                                                                                                                                    100% 7680     7.5KB/s   00:00
ARCHIVE_LOCAL1_20200606064235117_0.log                                                                                                                                                                    100%   16KB  15.5KB/s   00:00
ARCHIVE_LOCAL1_20200606142853068_0.log                                                                                                                                                                    100%  300MB  10.0MB/s   00:30
ARCHIVE_LOCAL1_20200606142906087_0.log                                                                                                                                                                    100%  300MB  23.1MB/s   00:13
ARCHIVE_LOCAL1_20200606142912805_0.log                                                                                                                                                                    100% 5120     5.0KB/s   00:00
ARCHIVE_LOCAL1_20200617193955110_0.log                                                                                                                                                                    100%   83MB  10.4MB/s   00:08
ARCHIVE_LOCAL1_20200622160200765_0.log                                                                                                                                                                    100%  234KB 234.0KB/s   00:00
ARCHIVE_LOCAL1_20200711193829081_0.log                                                                                                                                                                    100%  300MB  13.6MB/s   00:22
[dmdba@shard1 backup]$

[dmdba@dmks backup]$ ls -lrt

drwxr-xr-x. 2 dmdba dinstall      4096 7月  11 19:47 arch
-rw-r--r--. 1 dmdba dinstall  33554432 7月  11 19:50 DB_dmks_FULL_2020_07_11_19_50_30.bak

2.使用原备份库还原新库

[dmdba@dmks backup]$ dmrestore ini_path=/dm_home/dmdbms/data/dameng_for_recover/dm.ini file=/dm_home/dmdbms/backup/DB_jydm_20200711192046000434.bak
restore V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!


backup sig:            BA
backup tool version:   12595
backup db name:        jydm
backup db magic:       -82313077
backup pemnt magic:    1250320462
backup name:           jydm
backup type:           full
backup level:          offline
backup range:          database
backup description:
compressed level:      0
encrypt_type:          0
encrypt_id:            2052
rac node:              0
page check:            0
rlog encrypt:          0
external cipher id:    0
external hash id:      0
length in char:        0
use new hash:          1
backup time:           2020-07-11 19:20:46
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
charset:               0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
mpp_timestamp:         0
crc_check:             TRUE
parallel type:         0
parallel info len:     0
backup db fil num:     8
archive flag:          1
after backup LSN:      27830642


$bak_seq |$file_path                                        |$size(K)       |$used(K)
        1|/dm_home/dmdbms/backup/DB_jydm_20200711192046000434.bak|      294912.00|      284866.00


$file_seq |$type|$ts_name       |$state    |$size(K)  |$seq_in_bak    |$off_in_bak(B) |$len_in_bak(B) |$file_path                                        |$mirror_path
         1|DBF  |SYSTEM         |ONLINE    |     23552|              1|          43008|        6766592|/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF        |
         2|DBF  |ROLL           |ONLINE    |    229376|              1|        6809600|         401408|/dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF          |
         3|DBF  |MAIN           |ONLINE    |    210944|              1|        7211008|      171753472|/dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF          |
         4|DBF  |BOOKSHOP       |ONLINE    |    153600|              1|      178964480|         933888|/dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF      |
         5|DBF  |DMHR           |ONLINE    |    131072|              1|      179898368|         425984|/dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF          |
         6|DBF  |USERS          |ONLINE    |     51200|              1|      180324352|        1105920|/dm_home/dmdba/dmdbms/data/jydm/users01.dbf       |
         7|DBF  |SYSAUX         |ONLINE    |    179200|              1|      181430272|      110256128|/dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF        |
         8|DBF  |FG_PERSON      |ONLINE    |    131072|              1|      291686400|          16384|/dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF     |

Continue?[Y/N]:Y
Warning: Backup file may has been modified !

Continue?[Y/N]:Y
restore bak_file: /dm_home/dmdbms/backup/DB_jydm_20200711192046000434.bak ...
start restore database...
start restore file: /dm_home/dmdbms/data/dameng_for_recover/SYSTEM.DBF(SYSTEM), pages: 826
end restore file: /dm_home/dmdbms/data/dameng_for_recover/SYSTEM.DBF(SYSTEM)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/ROLL.DBF(ROLL), pages: 49
end restore file: /dm_home/dmdbms/data/dameng_for_recover/ROLL.DBF(ROLL)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/MAIN.DBF(MAIN), pages: 20966
end restore file: /dm_home/dmdbms/data/dameng_for_recover/MAIN.DBF(MAIN)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/BOOKSHOP.DBF(BOOKSHOP), pages: 114
end restore file: /dm_home/dmdbms/data/dameng_for_recover/BOOKSHOP.DBF(BOOKSHOP)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/DMHR.DBF(DMHR), pages: 52
end restore file: /dm_home/dmdbms/data/dameng_for_recover/DMHR.DBF(DMHR)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/users01.dbf(USERS), pages: 135
end restore file: /dm_home/dmdbms/data/dameng_for_recover/users01.dbf(USERS)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/SYSAWR.DBF(SYSAUX), pages: 13459
end restore file: /dm_home/dmdbms/data/dameng_for_recover/SYSAWR.DBF(SYSAUX)
start restore file: /dm_home/dmdbms/data/dameng_for_recover/FG_PERSON.DBF(FG_PERSON), pages: 2
end restore file: /dm_home/dmdbms/data/dameng_for_recover/FG_PERSON.DBF(FG_PERSON)
end restore database data files.

Apply archive log LSN from 27830643 to 27830642, time used:0.000s.
restore finished, code = 0!

restore successfully!
restore time used: 44867.245(ms)

使用备份文件还原数据库归档日志LSN为27830643
3.使用dmmdbf工具查看原备份库中的db_magic为1371967312

[dmdba@shard1 backup]$ dmmdf type=1 file=/dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 db_magic=1371967312
2 next_trxid=3377415
3 pemnt_magic=1250320462
**********************************************************
Please input which parameter you want to change(1-3), q to quit: q

4.使用dmmdf工具查看目标库的system.dbf的db_magic并进行修改1371967312

[dmdba@dmks dameng_for_recover]$ dmmdf type=1 file=/dm_home/dmdbms/data/dameng_for_recover/SYSTEM.DBF
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 db_magic=-82313077
2 next_trxid=3377415
3 pemnt_magic=1250320462
**********************************************************
Please input which parameter you want to change(1-3), q to quit: 1
Please input new value: 1371967312
**********************************************************
1 db_magic=1371967312
2 next_trxid=3377415
3 pemnt_magic=1250320462
**********************************************************
Do you want to save the change to file (y/n): y
Save to file success!

5.使用dmmdf工具查看目标库的dameng_for_recover01.log和dameng_for_recover02.log的db_magic并进行修改-82313077

[dmdba@dmks dameng_for_recover]$ dmmdf type=2 file=/dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover01.log
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 35
7 db_magic = -82313077
8 clsn_fil = 0
10 next_seq = 304
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 27830642
15 clsn_off = 4096
16 arch_lsn = 0
17 pemnt_magic = 1250320462
18 fil_id = 0
19 crc_check = 1
**********************************************************
You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18).
Please input the num which one you want to change, q to quit: 7
Input the new value: 1371967312
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 35
7 db_magic = 1371967312
8 clsn_fil = 0
10 next_seq = 304
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 27830642
15 clsn_off = 4096
16 arch_lsn = 0
17 pemnt_magic = 1250320462
18 fil_id = 0
19 crc_check = 1
**********************************************************
Do you want to quit and save the change to file (y/n): y
Save to file success!
[dmdba@dmks dameng_for_recover]$ dmmdf type=2 file=/dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover02.log
dmmdf V7.1.6.46-Build(2018.02.08-89107)ENT
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 35
7 db_magic = -82313077
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 0
15 clsn_off = 0
16 arch_lsn = 0
17 pemnt_magic = 1250320462
18 fil_id = 1
19 crc_check = 1
**********************************************************
You can only reset sta(5) or db_magic (7) or clsn (14) or pemnt_magic(17) or fil_id(18).
Please input the num which one you want to change, q to quit: 7
Input the new value: 1371967312
**********************************************************
1 sig = DMRLOG
2 ver = 7004
3 chksum = 0
4 dbversion = 0x7000a
5 sta = 0
6 n_magic = 35
7 db_magic = 1371967312
8 clsn_fil = 0
10 next_seq = 0
11 arch_seq = 0
12 len = 268435456
13 free = 4096
14 clsn = 0
15 clsn_off = 0
16 arch_lsn = 0
17 pemnt_magic = 1250320462
18 fil_id = 1
19 crc_check = 1
**********************************************************
Do you want to quit and save the change to file (y/n): y
Save to file success!

6.使用归档文件还原数据库

[dmdba@dmks backup]$ dmrestore ini_path=/dm_home/dmdbms/data/dameng_for_recover/dm.ini archive_dir=(/dm_home/dmdbms/backup/arch) show_bak_info=2
-bash: syntax error near unexpected token `('

[dmdba@dmks backup]$ dmrestore ini_path=/dm_home/dmdbms/data/dameng_for_recover/dm.ini archive_dir=\(/dm_home/dmdbms/backup/arch\) show_bak_info=2
restore V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!

Continue?[Y/N]:Y
try to apply archive log from LSN: 27830642 to LSN: 9223372036854775807.

Apply archive log LSN from 27830643 to 27832982, time used:2.811s.

restore successfully!
restore time used: 3840.401(ms)
[dmdba@dmks backup]$

使用归档文件还原数据库归档日志LSN从27830643(因为使用数据库备份文件还原数据库时LSN27830643)为应用到了27832982。

7.启动恢复目标库

[root@dmks init.d]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]

[dmdba@dmks dameng_for_recover]$ cat dm.ini | grep PORT_NUM
                PORT_NUM                        =  5336                 #Port number on which the database server will listen
                DCP_PORT_NUM                    =  5237                 #Port number on which DCP will listen
[dmdba@dmks dameng_for_recover]$ disql sysdba/xxzx7817600@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 19.135(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5

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

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5

used time: 16.368(ms). Execute id is 4733.

原库

SQL> select * from t1;

LINEID     C1          C2
---------- ----------- -----------
1          1           1
2          2           2
3          3           3
4          4           4
5          5           5

used time: 16.368(ms). Execute id is 4733.

DM7使用脱机工具dmbackup进行数据库备份

Posted on 2021年2月8日 by YongJing(敬勇)

使用脱机工具dmbackup进行数据库备份,语法如下：

DMBACKUP KEYWORD=value

确认DM数据库服务器已停止后，在Windows“命令提示符”窗口中输入带参数的DMBACKUP命令启动该工具，参数说明见下表。

[dmdba@shard1 dmdbms]$ dmbackup help
Format:  ./dmbackup  KEYWORD=value

Example: ./dmbackup  INI_PATH=/opt/dm7data/dameng/dm.ini NAME=test.bak

Imperative Argument: INI_PATH NAME

Keyword             explain(default value)
--------------------------------------------------------------------------------
INI_PATH            the path of dmserver's dm.ini file
BASE_BAK_DIR        the path with increment backup Format (dir1,dir2,dir3,...)
NAME                backup name
TYPE                backup type (FULL, INCREMENT), Default(FULL)
BAKFILE_PATH        the file path of backup file
DESCRIBE            the description of backup
MAXSIZE             the max value capability of the single backup file Default(33MB~2048MB, default(2048))
ENCRYPT             encrypt type (0, 1, 2), Default(0)
PASSWORD            encrypt key
ENCRYPT_NAME        encrypt algorithm name
COMPRESS            compress level(0-9), 0:no compress, 9:high compress, Default(1)
PARALLEL            parallel mapping the file path
SHOW_BAK_INFO       the directory of backup Format (dir1,dir2,dir3,...)
HELP                output help info

INI_PATH:配置文件路径，最大长度为256字节,若未设置SHOW_BAK_INFO参数，则为必选参数
NAME:备份名，最大长度为128字节,若未设置SHOW_BAK_INFO参数，则为必选参数
BAKFILE_PATH:备份文件路径,可选参数
TYPE:备份类型，默认为完全备份，FULL 表示完全备份，INCREMENT表示增量备份,可选参数
BASE_BAK_DIR:增量备份时的指定基备份目录,可选参数，仅对增量备份有效
DESCRIBE:备份文件描述，即备注信息，注意：描述如果含有空格，需要用双引号括起来，否则会报错,可选参数
MAXSIZE:备份文件最大大小，取值范围为：33-2048，单位为M（兆），默认为2048,可选参数
ENCRYPT:加密类型，0表示不加密，1表示简单加密，2表示复杂加密,可选参数
PASSWORD:加密密码，长度最大为128字节，如果选择不加密，此处不必指定,可选参数
ENCRYPT_NAME:加密算法，可通过查看动态视图v$CIPHERS获取加密算法信息。不支持xxx_cbc和xxx_ecb算法。可选参数
COMPRESS:指定备份为压缩，如果不选择，则表示不压缩,可选参数
PARALLEL:指定并行备份映射文件完整路径，如： E:\bak.parallel。可选参数
SHOW_BAK_INFO:指定备份存放的目录，可以指定1~16个,可选参数
HELP:打印帮助信息
例1，对名为jydm数据库执行一个完全备份。

[dmdba@shard1 backup]$ dmbackup type=full ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini name=jydm
backup V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!
start backup tablespace: SYSTEM.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF, pages: 2944
end backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
end backup tablespace: SYSTEM.

start backup tablespace: ROLL.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
end backup tablespace: ROLL.

start backup tablespace: MAIN.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
end backup tablespace: MAIN.

start backup tablespace: BOOKSHOP.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
end backup tablespace: BOOKSHOP.

start backup tablespace: DMHR.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
end backup tablespace: DMHR.

start backup tablespace: USERS.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf
end backup tablespace: USERS.

start backup tablespace: SYSAUX.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF
end backup tablespace: SYSAUX.

start backup tablespace: FG_PERSON.
start backup file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF, pages: 0
end backup file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF
end backup tablespace: FG_PERSON.

backup fun end time used: 13128.406(ms)
backup successfully!
backup time used: 19512.280(ms)

在没有指定bakfile_path参数指定备份文件目录时，会在默认的备份目录中生成备份文件。

[dmdba@shard1 bak]$ pwd
/dm_home/dmdba/dmdbms/data/jydm/bak
[dmdba@shard1 bak]$ ls -lrt
-rw-r--r-- 1 dmdba dinstall 301989888 7月  11 19:20 DB_jydm_20200711192046000434.bak

DM7使用联机执行SQL语句进行备份还原

Posted on 2021年2月8日 by YongJing(敬勇)

使用联机执行SQL语句进行备份还原
1. 数据备份
1.1 备份数据库
系统提供联机SQL命令执行数据库备份。语法如下：

BACKUP DATABASE [FULL |INCREMENT WITH BACKUPDIR ‘< 备份目录>’{,‘< 备份目录>’}] TO < 备份名> [BAKFILE ‘< 备份路径>’]
[BACKUPINFO ‘< 备份描述>’] [MAXSIZE < 限制大小>] [IDENTIFIED BY < 密钥>[WITH ENCRYPTION][ENCRYPT WITH < 加密算法>]]
[COMPRESSED [LEVEL < 压缩级别>]][WITHOUT LOG][PARALLEL ‘< 并行映射文件路径>’];
FULL|INCREMENT：备份类型，FULL表示完全备份，INCREMENT表示增量备份。
< 备份目录>：基础备份所在的目录，最大长度为256个字节。
< 备份名>：备份的名称，在DMDBMS中以此标识不同的备份。
< 备份路径>：备份文件存放的完整路径。
< 备份描述>：备份的描述信息。
< 限制大小>：最大备份文件大小，以M为单位，取值范围33M~2G。
< 密钥>：备份加密通过使用IDENTIFIED BY来指定密码。

WITH ENCRYPTION ：用来指定加密类型，0表示不加密，1表示简单加密，2表示复杂加密。
COMPRESSED：用来指定是否压缩级别，取值范围1~9。如果使用0，表示不压缩，1表示压缩快、但是压缩比低，9表示压缩慢、但是压缩比高。
WITHOUT LOG：联机数据库备份是否备份日志。如果使用，则表示不备份，否则表示备份。
PARALLEL：用来指定并行映射文件的完整路径。
PARALLEL：用来指定并行映射文件的完整路径。
< 并行映射文件路径>：并行映射文件的完整路径，最大长度为256个字节，如： E:\bak.parallel；并行映射文件的书写请参考4.2.3.2。
< 加密算法>：加密算法具体见《DM7_SQL语言使用手册》相关章节，但是不支持xxx_cbc和xxx_ecb算法加密。缺省情况下，算法为AES256_CFB。

备份成功后会在备份默认目录下生成备份文件，若没有指定备份文件名，则系统自动生成以“数据库名_日期.bak”格式的备份文件，通过该备份文件可以进行数据库还原。例如，执行数据库备份，备份类型为完全备份，备份文件名为/dm_home/dmdba/dmdbms/backup/database_full_bak.bak。

SQL> backup database full to full_bak bakfile '/dm_home/dmdba/dmdbms/backup/database_full_bak.bak';
executed successfully
used time: 00:00:09.508. Execute id is 86138.

1.2 备份用户表空间
系统提供联机SQL命令执行表空间备份，不支持对系统表空间、日志表空间、回滚表空间、临时表空间执行备份操作。语法如下：

BACKUP TABLESPACE < 表空间名> [FULL| INCREMENT WITH BACKUPDIR '< 备份目录>'{,'< 备份目录>'}] TO < 备份名>
[BAKFILE '< 备份路径>'] [BACKUPINFO '< 备份描述>'] [MAXSIZE < 限制大小>] [IDENTIFIED BY < 密钥>[WITH ENCRYPTION ]]
[COMPRESSED[LEVEL < 压缩级别>]];

例如，备份名为t1的表空间，备份类型为FULL，备份文件名为’/dm_home/dmdba/dmdbms/backup/tablespace_users.bak’。

SQL> backup tablespace users full to users_bak bakfile '/dm_home/dmdba/dmdbms/backup/tablespace_users.bak';
executed successfully
used time: 00:00:01.376. Execute id is 87164.

2. 数据还原
备份文件和待还原数据库的建库参数USE_NEW_HASH应保持一致，若取值不一致，则还原时会报错。

2.1 还原表空间
使用SQL命令对表空间进行还原。与数据库还原类似，表空间备份分为完全备份和增量备份，但表空间备份没有联机备份和脱机备份之分，表空间备份只支持联机备份。表空间还原根据不同的表空间备份类型选择不同的还原策略对表空间进行还原。与库级还原和表级还原不同，表空间还原依赖于归档日志，利用备份文件还原表空间只能还原到备份时所在数据库中，并且还原后，表空间处于最新状态。由于待还原表空间的文件名和文件数目可能与备份时的不一致，因此还原后需要更新文件系统。另外，由于从备份表空间到还原表空间的时间间隔中，系统可能累积了很多归档日志，为了保证跨表空间的对象数据同步，需要重做该表空间的所有归档日志。

系统提供联机SQL命令执行表空间还原，还原前需要将表空间置为脱机状态，不支持对系统表空间、日志表空间、回滚表空间、临时表空间执行还原操作。

RESTORE TABLESPACE < 表空间名> FROM ‘< 备份路径>’ | BACKUP < 备份文件名> [ INDENTIFIED BY < 密码>] [ WITH BACKUPDIR ‘< 备份
目录>’{,‘< 备份目录>’}][WITH ARCHIVEDIR ‘归档目录’{,’归档目录’}][DBFILE < 文件ID> TO ‘< 文件路径>’ {,DBFILE < 文件ID>
 TO ‘< 文件路径>’}] [MIRROR < 文件ID> TO‘< 文件路径>’{,MIRROR < 文件ID> TO ‘< 文件路径>’}];

< 表空间名>:需要还原的表空间名称。
< 备份路径>:备份文件存放的完整路径。
< 备份文件名>:备份的名称，在DMDBMS中以此标识不同的备份。
< 密码>:加密备份表空间时，用户设置的密码。
< 备份目录>:搜集备份文件的目录。
< 归档目录>:还原时，搜集归档文件的目录。
< 文件ID>:需要重新设置文件路径文件对应的文件号。
< 文件路径>:设置还原后，数据文件的路径。
MIRROR:要使用数据文件镜像，必须在建库时开启页校验的参数page_check。
例如，利用备份文件/dm_home/dmdba/dmdbms/backup/tablespace_users.bak还原表空间users。

SQL> alter tablespace users offline;
executed successfully
used time: 00:00:07.751. Execute id is 89303.
SQL> restore tablespace users from '/dm_home/dmdba/dmdbms/backup/tablespace_users.bak';
executed successfully
used time: 00:00:01.641. Execute id is 89348.
SQL> alter tablespace users online;
executed successfully
used time: 76.018(ms). Execute id is 89360.

对于表空间联机还原，还原前需要将表空间置为脱机状态；还原后，为了使用表空间，需要重新将表空间置为联机状态。

在表空间还原的时候，如果出现异常（如掉电），还原表空间的操作异常终止，那么表空间可能已经被损坏。这种情况下，只有再次还原才能够修复。

3. 设置备份属性
3.1 加密与压缩
备份文件中包含了数据库中的原始数据，为提高备份数据的安全性，无论是普通备份还是B树备份，DM都提供了对备份数据进行加密的功能。系统中支持简单加密和复杂加密。简单加密和复杂加密实现原理都是在备份过程中利用经典的加密算法对数据页面加密，然后将密文写入备份文件，区别在于复杂加密方式对写入备份文件的每一页数据都进行加密，而简单加密方式只是选择性地对某些页面进行加密。显然，简单加密的效率相对于复杂加密效率要高，但同时，简单加密的备份数据不如复杂加密安全。

此外，DM还支持备份数据压缩功能。压缩功能的实现能有效缩短备份文件的大小，减少所占的磁盘空间。

不论是加密还是压缩，都是在正常执行备份数据时的额外操作，与此同时，压缩和加密的备份文件用于还原时，还需要执行解压缩和解密操作，因此会对备份、还原性能造成一定的影响。系统管理员应根据实际情况，在磁盘空间利用率、备份、还原性能，以及备份数据安全性之间做权衡，确定在执行备份操作时是否需要设置压缩，加密参数。

举例说明：
(1) 联机备份数据库，并对备份数据压缩，备份文件存放路径为/dm_home/dmdba/dmdbms/backup/database_cpr.bak。

SQL> backup database full to cpr_bak bakfile '/dm_home/dmdba/dmdbms/backup/database_cpr.bak' compressed;
executed successfully
used time: 00:00:09.535. Execute id is 90807.

(2) 联机备份名为t1的表空间，备份类型为FULL，对备份数据进行简单加密，加密密码为ABCDEF123，备份文件存放路径为/dm_home/dmdba/dmdbms/backup/tablespace_users_cpr.bak。

SQL> backup tablespace users full to ts_users_cpr_bak bakfile '/dm_home/dmdba/dmdbms/backup/tablespace_users_cpr.bak' identified by ABCDEF123 with encryption 1;
executed successfully
used time: 00:00:01.386. Execute id is 91101.

(3)联机备份SYSDBA模式下的test表，对备份数据进行复杂加密，加密密码为ABCDEF123，并且对备份数据压缩。备份文件存放路径为/dm_home/dmdba/dmdbms/backup/table_t1_bak.bak。

SQL> backup table sysdba.t1 to table_t1_bak bakfile '/dm_home/dmdba/dmdbms/backup/table_t1_bak.bak' identified by ABCDEF123 with encryption 2 compressed;
executed successfully
used time: 00:00:01.121. Execute id is 91267.

3.2 并行映射文件
为了提高数据库备份性能，针对物理备份，系统提供了并行备份的功能。并行备份的核心思想是将对数据库的备份拆分为对多个表空间的备份。系统在执行备份前，将物理存储位置临近的表空间作为基本备份单位，随后同时备份不同的备份单位，从而使得系统在执行备份时，充分利用了CPU资源和磁盘组资源，达到提升备份性能的目的。利用并行备份功能，需要系统管理员配置并行映射文件，映射文件格式如下所示。映射文件配置内容与系统并行备份的性能直接相关，因此，系统管理员应在充分了解数据库中各个表空间所在磁盘的物理位置，合理设置配置文件。此外，每个备份单位设置的备份目录应尽量在不同的磁盘组，这样能保证读写文件都能充分利用磁盘IO，使备份性能最优化。

格式如下：
[表空间名，表空间名，……]=备份目录1
[表空间名，表空间名，……]=备份目录2
[表空间名，表空间名，……]=备份目录3
……
[default]=备份目录N
说明：
1. 备份目录必需是本地物理磁盘上已经存在的目录。
2. 设置表空间名必需存在，若不存在则报错。
3. 每一行只能有一个“[表空间名，表空间名……]=备份目录”项。
4. 自动忽略同一备份目录下对应的多个同名表空间名，只取其中一个。
5. 一个表空间名不能对应多个备份目录，否则报错。
6. 对于相同的备份目录，DM7自动将它们作为一个备份单位。
7. default：没有显示设置的表空间，备份数据都存放在default对应的目录下。
8. [default]行必需存在，DM7认为[default]为最后一行，不统计[default]后续行的设置。
9. 映射文件的扩展名为parallel。
10.不允许显示设置系统表空间的磁盘目录。
11.对于含有特殊字符的表空间名，比如逗号、空格、中括号、等号，系统不保证解析映射文件的正确性。举例说明：
(1)假设数据库数据分布在三个磁盘组DISK1，DISK2和DISK3中。SYSTEM，MAIN，ROLL表空间在磁盘组DISK1；TS1，TS2表空间在磁盘组DISK2；TS3表空间在DISK3，配置映射文件bak.parallel如下：

[dmdba@shard1 dmdbms]$ vi bak.parallel
[DMHR,USERS]=/dm_home/dmdba/dmdbms/backup1
[FG_PERSON]=/dm_home/dmdba/dmdbms/backup2
[default]=/dm_home/dmdba/dmdbms/backup3

从上述配置内容可知，TS1，TS2作为一个备份单位，备份数据存储在D:\DAMENG_1中；TS3作为一个备份单位，备份数据存储在C:\DAMENG_2中；系统表空间作为一个备份单位，备份数据存储在E:\DAMENG目录中。

(2) 利用配置映射文件/dm_home/dmdba/dmdbms/bak.parallel执行联机并行备份操作，主备份文件为/dm_home/dmdba/dmdbms/parallel.bak，各个子备份分别在/dm_home/dmdba/dmdbms/backup1，/dm_home/dmdba/dmdbms/backup2和/dm_home/dmdba/dmdbms/backup3目录中。

SQL> backup database full to para_bak bakfile '/dm_home/dmdba/dmdbms/parallel.bak' parallel '/dm_home/dmdba/dmdbms/bak.parallel';
executed successfully
used time: 00:00:08.585. Execute id is 93997.

[dmdba@shard1 dmdbms]$ ls -lrt parallel*
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:30 parallel.bak
[dmdba@shard1 dmdbms]$ ls -lrt backup1/
总用量 1512
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:30 DB_jydm_20200701173032000519_1.bak
[dmdba@shard1 dmdbms]$ ls -lrt backup2/
总用量 28
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:30 DB_jydm_20200701173032000519_1.bak
[dmdba@shard1 dmdbms]$ ls -lrt backup3/
总用量 306072
-rw-r--r-- 1 dmdba dinstall 335544320 7月   1 17:30 DB_jydm_20200701173032000519_1.bak

(3)利用配置映射文件/dm_home/dmdba/dmdbms/bak.parallel执行脱机并行全量备份操作，主备份文件为/dm_home/dmdba/dmdbms/parallel02.bak，各个子备份分别在各个子备份分别在/dm_home/dmdba/dmdbms/backup1，
/dm_home/dmdba/dmdbms/backup2和/dm_home/dmdba/dmdbms/backup3目录中。

[dmdba@shard1 dmdbms]$ dmbackup type=full ini_path=/dm_home/dmdba/dmdbms/data/jydm/dm.ini name=test parallel=/dm_home/dmdba/dmdbms/bak.parallel
backup V7.1.6.46-Build(2018.02.08-89107)ENT
file dm.key not found, use default license!
[channel<5912>]start backup tablespaces group to directory /dm_home/dmdba/dmdbms/backup2.
[channel<5912>]start backup tablespace: FG_PERSON.
[channel<5912>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF, pages: 0
[channel<5911>]start backup tablespaces group to directory /dm_home/dmdba/dmdbms/backup1.
[channel<5911>]start backup tablespace: DMHR.
[channel<5911>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF, pages: 0
[channel<5912>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/FG_PERSON.DBF
[channel<5912>]end backup tablespace: FG_PERSON.

[channel<5912>]end backup tablespaces group.

[channel<5913>]start backup tablespaces group to directory /dm_home/dmdba/dmdbms/backup3.
[channel<5913>]start backup tablespace: SYSTEM.
[channel<5913>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF, pages: 2944
[channel<5911>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/DMHR.DBF
[channel<5911>]end backup tablespace: DMHR.

[channel<5911>]start backup tablespace: USERS.
[channel<5911>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf, pages: 0
[channel<5911>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/users01.dbf
[channel<5911>]end backup tablespace: USERS.

[channel<5911>]end backup tablespaces group.

[channel<5913>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSTEM.DBF
[channel<5913>]end backup tablespace: SYSTEM.

[channel<5913>]start backup tablespace: ROLL.
[channel<5913>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF, pages: 0
[channel<5913>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/ROLL.DBF
[channel<5913>]end backup tablespace: ROLL.

[channel<5913>]start backup tablespace: MAIN.
[channel<5913>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF, pages: 0
[channel<5913>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/MAIN.DBF
[channel<5913>]end backup tablespace: MAIN.

[channel<5913>]start backup tablespace: BOOKSHOP.
[channel<5913>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF, pages: 0
[channel<5913>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/BOOKSHOP.DBF
[channel<5913>]end backup tablespace: BOOKSHOP.

[channel<5913>]start backup tablespace: SYSAUX.
[channel<5913>]start backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF, pages: 0
[channel<5913>]end backup file: /dm_home/dmdba/dmdbms/data/jydm/SYSAWR.DBF
[channel<5913>]end backup tablespace: SYSAUX.

[channel<5913>]end backup tablespaces group.

backup fun end time used: 6701.450(ms)
backup successfully!
backup time used: 13028.166(ms)

[dmdba@shard1 dmdbms]$ ls -lrt backup3/
总用量 529716
-rw-r--r-- 1 dmdba dinstall 335544320 7月   1 17:30 DB_jydm_20200701173032000519_1.bak
-rw-r--r-- 1 dmdba dinstall 234881024 7月   1 17:35 DB_jydm_20200701173535000023_1.bak
[dmdba@shard1 dmdbms]$ ls -lrt backup2/
总用量 56
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:30 DB_jydm_20200701173032000519_1.bak
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:35 DB_jydm_20200701173535000023_1.bak
[dmdba@shard1 dmdbms]$ ls -lrt backup1/
总用量 3024
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:30 DB_jydm_20200701173032000519_1.bak
-rw-r--r-- 1 dmdba dinstall 33554432 7月   1 17:35 DB_jydm_20200701173535000023_1.bak

DM7使用DMRMAN执行表空间还原

Posted on 2021年2月8日 by YongJing(敬勇)

使用DMRMAN执行表空间还原
介绍表空间的脱机还原。表空间的脱机还原除了支持联机还原的所有功能外，也放开了SYSTEM和ROLL表空间还原操作。与联机表空间还原不同的是，脱机表空间还原不需要事先置目标表空间为OFFLINE状态。语法如下：

RESTORE DATABASE '' TABLESPACE < 表空间名>
[DATAFILE< <文件编号> {,< 文件编号>} | '< 文件路径>' {,'< 文件路径>'}>]
FROM BACKUPSET '< 备份集目录>'
[DEVICE TYPE DISK|TAPE[PARMS '< 介质参数>']]
[IDENTIFIED BY < 密码> [ENCRYPT WITH < 加密算法>]]
[WITH BACKUPDIR '< 基备份集搜索目录>'{,'< 基备份集搜索目录>'}]
[]
[MAPPED FILE '< 映射文件>'][TASK THREAD < 任务线程数>] [NOT PARALLEL]
[UNTIL TIME '< 时间串>'] [UNTIL LSN ];
 ::=
WITH ARCHIVEDIR '< 归档日志目录>'{,'< 归档日志目录>'}

DATABASE：指定还原库目标的dm.ini文件路径。
TABLESPACE：指定还原的表空间，可以是用户表空间，也可以是SYSTEM系统表空间和ROLL回滚表空间。
DATAFILE：还原指定的数据文件。可以指定数据文件编号或数据文件路径。文件编号，对应动态视图V$DATAFILE中ID列的值；文件路径，对应动态视图V$DATAFILE中PATH或者MIRROR_PATH列的值，也可以仅指定数据文件名称（相对路径），与表空间中数据文件匹配时，会使用SYSTEM目录补齐。
BACKUPSET：指定用于还原目标数据库的备份集目录。若指定为相对路径，会在默认备份目录下搜索备份集。
DEVICE TYPE：指存储备份集的介质类型，支持DISK和TAPE，默认为DISK。DISK表示备份集存储介质为磁盘，TAPE表示存储介质为磁带。
PARMS：介质参数，供第三方存储介质（TAPE类型）管理使用。
IDENTIFIED BY：指定备份时使用的加密密码，供还原过程解密使用。
ENCRYPT WITH：指定备份时使用的加密算法，供还原过程解密使用，若未指定，则使用默认算法。
WITH BACKUPDIR：指定备份集搜索目录。
MAPPED FILE：指定存放还原目标路径的文件，参见3.3.5.2.1 数据库还原。当< 备份集目录>和< 映射文件>指定的路径不一致时，以< 映射文件>指定的路径为主
TASK THREAD：指定还原过程中用于处理解压缩和解密任务的线程个数。若未指定，则默认为4；若指定为0，调整为1；若指定超过当前系统主机核数，则调整为当前核数。
NOT PARALLEL：指定并行备份集使用非并行方式还原。对于非并行备份集，不论是否指定该关键字，均采用非并行还原。
WITH ARCHIVEDIR：本地归档日志搜索目录，若未指定，则仅使用目标库配置的第一个本地归档。
UNTIL TIME：恢复数据库到指定的时间点。
UNTIL LSN：恢复数据库到指定的LSN。

使用说明：
1. 表空间还原不能是TEMP表空间，指定文件还原也不能为TEMP表空间中文件。
2. 表空间还原要求还原目标库与备份库为同一个库，且若还原目标库中SYSTEM表空间没有故障且还原目标表空间不是SYSTEM表空间的话，则要求目标库校验通过，比如加密校验和日志校验等；否则将使用备份集中信息与当前的进行校验。
3. 还原目标库不能为已经执行过restore，但是未指定过recover的库。
4. SYSTEM表空间和ROLL表空间不允许指定UNTIL LSN或者UNTIL TIME还原；其他用户表空间可以。若未指定UNTIL TIME或者UNTIL LSN，则均还原到最新状态。使用UNTIL TIME（或者UNTIL LSN）的同时不能指定DATAFILE还原，否则报语法错误。
5. 处于RES_OFFLINE或CORRUPT状态的表空间不允许指定表空间中数据文件还原。
6. 整个还原过程中不会修改数据库本身状态或者调整CKPT_LSN。
7. 不管是RAC环境，还是单机环境，若异常退出，需手动指定各节点归档修复后，使用各节点完整的归档日志执行还原恢复；否则，将可能无法恢复到最新。
8. 异常退出库指定UNTIL TIME或者UNTIL LSN还原失效，因为库故障重启时会执行故障修复，UNTIL失效，表空间仍会恢复到最新状态。
9. 若目标库中SYSTEM表空间故障，则必须优先还原SYSTEM表空间。在DMRAC环境中进行表空间还原，需要先确保所有节点实例都已退出，此时在任一节点上使用该节点的备份集均可进行表空间还原操作，且只要在一个节点上执行目标表空间还原即可。对于两节点RAC01、RAC02，表空间的还原操作如下：

RMAN>backup database '/home/DMDBMS/rac/dm01.ini' backupset '/home/DMDBMS/bak/rman_dm01_01';
RMAN>restore database'/home/DMDBMS/rac/dm01.ini' tablespace main from backupset '/home/DMDBMS/bak/rman_dm01_01'
 with archivedir'/home/DMDBMS/rac/arch_dest1';

10. 如果SYSTEM表空间处于ONLINE/OFFLINE状态且文件丢失，则必须要通过库还原修复，不支持对其执行表空间还原。

下面的例子将对RAC环境中的TS_FOR_DBF表空间执行还原
1.对数据库执行备份

SQL> backup database full backupset '/dm7/backup/db_bak_for_tbs';
executed successfully
used time: 00:00:01.217. Execute id is 11.

2.在DMRAC环境中进行表空间还原，需要先确保所有节点实例都已退出，此时在任一节点上使用该节点的备份集均可进行表空间还原操作，且只要在一个节点上执行目标表空间还原即可。对于两节点RAC01、RAC02，表空间的还原操作如下：

[root@dmrac1 backup]# service DmServicerac1 stop
Stopping DmServicerac1: [ OK ]

[root@dmrac2 rac1_config]# service DmServicerac2 stop
Stopping DmServicerac2: [ OK ]



RMAN> restore database 'dm7/data/rac0_config/dm.ini' tablespace ts_for_dbf from backupset '/dm7/backup/db_bak_for_tbs' with archivedir '/dm7/data/rac0_arch','/dm7/data/rac1_arch' until;
restore database '/dm7/data/rac0_config/dm.ini' tablespace ts_for_dbf from backupset '/dm7/backup/db_bak_for_tbs' with archivedir '/dm7/data/rac0_arch' , '/dm7/data/rac1_arch' ;
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....
RESTORE TABLESPACE[ts_for_dbf] IN DB[/dm7/data/rac0_config/dm.ini] CHECK......
EP[0] max_lsn: 92312
RESTORE TABLESPACE[ts_for_dbf] IN DB[/dm7/data/rac0_config/dm.ini], dbf collect......
RESTORE TABLESPACE[ts_for_dbf] IN DB[/dm7/data/rac0_config/dm.ini], ts status and dbf refresh ......
RESTORE BACKUPSET [/dm7/backup/db_bak_for_tbs] START......
total 2 packages processed...
RESTORE TABLESPACE[ts_for_dbf] IN DB[/dm7/data/rac0_config/dm.ini], UPDATE ctl file......
EP 0's ckpt_lsn = 90803
min_ckpt_lsn = 90803
The RAC redo archive log 1 ptx
The RAC redo archive log 2 ptx
The RAC redo archive log 3 ptx
The RAC redo archive log 4 ptx
The RAC redo archive log 5 ptx
The RAC redo archive log 6 ptx
The RAC redo archive log 7 ptx
The RAC redo archive log 8 ptx
The RAC redo archive log 9 ptx
The RAC redo archive log 10 ptx
The RAC redo archive log 11 ptx
The RAC redo archive log 12 ptx
The RAC redo archive log 13 ptx
The RAC redo archive log 14 ptx
The RAC redo archive log 15 ptx
The RAC redo archive log 16 ptx
The RAC redo archive log 17 ptx
The RAC redo archive log 18 ptx
The RAC recover total redo 18 ptx
EP[0] Recover LSN from 90803 to 92307.
EP[1] Recover LSN from 92310 to 92309.
Recover from archive log finished, time used:0.004s.
total 6 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 4423.437(ms)

3.启动RAC实例

[root@dmrac1 data]# service DmServicerac1 start
Starting DmServicerac1: [ OK ]

[root@dmrac2 rac1_config]# service DmServicerac2 start
Starting DmServicerac2: [ OK ]

openPower服务器搭建Oracle 19c adg

Posted on 2021年1月15日2021年1月15日 by YongJing(敬勇)

创建用户与组
创建组(在各个主机上执行)

mkgroup -'A' id='501' adms='root' oinstall
mkgroup -'A' id='502' adms='root' asmdba
mkgroup -'A' id='503' adms='root' asmoper
mkgroup -'A' id='504' adms='root' dba
mkgroup -'A' id='505' adms='root' oper
mkgroup -'A' id='506' adms='root' backdba
mkgroup -'A' id='507' adms='root' dgdba
mkgroup -'A' id='508' adms='root' racdba
mkgroup -'A' id='509' adms='root' kmdba

创建用户(在各个主机上执行)

mkuser id=601 pgrp=oinstall groups=dba,asmdba,oper,asmoper,backdba,dgdba,racdba,kmdba home=/home/oracle oracle

创建安装目录

mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle/product/19.3/db
mkdir -p /u01/temp

chown -R oracle:oinstall /u01
chmod -R 775 /u01

设置用户环境变量（用oracle用户来编辑.profile)

umask 022
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3/db
export ORACLE_SID=hxsy
export ORACLE_UNQNAME=hxsy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/u01/tmp
export TMPDIR=/u01/tmp
export PS1=`hostname`:'$PWD'"$"

使用如下命令查看虚拟内存管理参数

vmo -L minperm%
vmo -L maxperm%
vmo -L maxclient%
vmo -L lru_file_repage
vmo -L strict_maxclient
vmo -L strict_maxperm

如果设置不合适，使用如下命令修改：

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

检查网络参数设置
ephemeral参数：
使用命令no -a |fgrep ephemeral可以查看当前系统ephemeral参数设置，建议的参数设置如下

tcp_ephemeral_high = 65500
tcp_ephemeral_low = 9000
udp_ephemeral_high= 65500
udp_ephemeral_low = 9000

如果系统中参数设置和上述值不一样，使用命令修改：

#no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
#no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

使用如下命令修改网络可调整参数：

#no -p -o rfc1323=1
#no -r -o ipqmaxlen=512
#no -p -o ipqmaxlen=512
#no -p -o sb_max=4194304
#no -p -o tcp_recvspace=65536
#no -p -o tcp_sendspace=65536
#no -p -o udp_recvspace=1351680 该值是udp_sendspace的10倍，但须小于sb_max
#no -p -o udp_sendspace=135168

备注：-r表示reboot后生效，-p表示即刻生效.
检查内核参数maxuproc（建议16384）和ncargs（至少128）

#lsattr -E -l sys0 -a ncargs
#lsattr -E -l sys0 -a maxuproc

如果设置不合适使用如下命令修改：

#chdev -l sys0 -a ncargs=256
#chdev -l sys0 -a maxuproc=16384

解压数据库软件

aix1:/u01/soft$unzip -q AIX.PPC64_193000_db_home.zip -d $ORACLE_HOME

安装数据库软件
在$ORACLE_HOME/install/response目录中有一个安装rsp文件示例
配置响应文件

[oracle@ora19c ~]$ vi 19c_dbinstall.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3/db
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT

执行安装

openaix1:/home/oracle$$ORACLE_HOME/runInstaller -silent -force -noconfig -ignorePrereq -responseFile /home/oracle/19c_dbinstall.rsp

********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation. The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation. The rootpre.sh script can be found at:
/u01/app/oracle/product/19.3/db/clone/rootpre.sh

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root in this machine? [y/n] (n)
y
Launching Oracle Database Setup Wizard...

Enter password for 'root' user:
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2020-11-17_01-49-09AM.log
ACTION: Identify the list of failed prerequisite checks from the log: installActions2020-11-17_01-49-09AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
/u01/app/oracle/product/19.3/db/install/response/db_2020-11-17_01-49-09AM.rsp

You can find the log of this install session at:
/u01/tmp/InstallActions2020-11-17_01-49-09AM/installActions2020-11-17_01-49-09AM.log
Successfully Setup Software with warning(s).
Moved the install session logs to:
/u01/app/oraInventory/logs/InstallActions2020-11-17_01-49-09AM

配置监听
创建配置监听的响应文件
在/u01/app/oracle/product/19.3/db/assistants/netca/目录有一个示例文件

aix1:/home/oracle$vi 19c_netca.rsp
"19c_netca.rsp" [New file] 

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}


aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp
UnsatisfiedLinkError exception loading native library: njni19
java.lang.UnsatisfiedLinkError: njni19 (Not found in java.library.path)
java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
        at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
        at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
        at oracle.net.ca.NetCA.main(NetCA.java:459)

Error: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
Oracle Net Services configuration failed.  The exit code is 1


# lsdev | grep iocp
iocp0      Defined         I/O Completion Ports
# smitty iocp

# lsdev | grep iocp
iocp0      Available       I/O Completion Ports


aix1:/home/oracle$$ORACLE_HOME/bin/relink all 
writing relink log to: /u01/app/oracle/product/19.3/db/install/relinkActions2020-11-16_11-17-27PM.log

aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /home/oracle/19c_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control: 
      /u01/app/oracle/product/19.3/db/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

aix1:/home/oracle$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 23:24:53

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 23:23:28
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

创建数据库
创建配置数据库的响应文件
在/u01/app/oracle/product/19.3/db/assistants/dbca/目录中有一个示例文件

[oracle@ora19c install]$ cat /u01/app/oracle/product/19.3/db/assistants/dbca/dbca.rsp


aix1:/home/oracle$vi 19c_dbca.rsp
automaticMemoryManagement=TRUE
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
templateName=General_Purpose.dbc
gdbName=hxsy
sid=hxsy
databaseConfigType=SI
createAsContainerDatabase=TRUE
numberOfPDBs=1
pdbName=hxsy1
useLocalUndoForPDBs=TRUE
pdbAdminPassword=hxsy_123456
sysPassword=hxsy_123456
systemPassword=hxsy_123456
storageType=FS
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
listeners=LISTENER
sampleSchema=TRUE
totalMemory 2048
databaseType=MULTIPURPOSE
automaticMemoryManagement=FALSE
totalMemory=4096

创建数据库

aix1:/home/oracle$dbca -silent -createDatabase -responseFile /home/oracle/19c_dbca.rsp
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/hxsy.
Database Information:
Global Database Name:hxsy
System Identifier(SID):hxsy
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/hxsy/hxsy.log" for further details.

给主库配置归档

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.3/db/dbs/arch
Oldest online log sequence 5
Current log sequence 7

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both sid='*';

System altered.

SQL> show parameter log_archive_for

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:32:33 2020
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size 9303576 bytes
Variable Size 956301312 bytes
Database Buffers 2231369728 bytes
Redo Buffers 24248320 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HXSY1 MOUNTED

SQL> alter pluggable database hxsy1 open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HXSY1 READ WRITE NO

这里主库已经启用了归档

启用force logging

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

给备库创建密码文件
这里通过复制主库的密码文件来创建备库密码文件

openaix1:/u01/app/oracle/product/19.3/db/dbs$scp oracle@aix1:/u01/app/oracle/product/19.3/db/dbs/orapwhxsy /u01/app/oracle/product/19.3/db/dbs/
oracle@aix1's password: 
orapwhxsy                                                                                                                                                                                                 100% 2048   199.4KB/s   00:00    
openaix1:/u01/app/oracle/product/19.3/db/dbs$ls -lrt
total 16
-rw-r--r--    1 oracle   oinstall       3079 May 16 2015  init.ora
-rw-r-----    1 oracle   oinstall       2048 Nov 17 16:40 orapwhxsy

给备库创建参数文件
使用主库的参数文件进行创建

SQL> create pfile from spfile;

File created.



aix1:/u01/app/oracle/product/19.3/db/dbs$cat inithxsy.ora

*.audit_file_dest='/u01/app/oracle/admin/hxsy/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/HXSY_DG/control01.ctl','/u01/app/oracle/oradata/HXSY_DG/control02.ctl'
*.db_block_size=8192
*.db_name='hxsy'
*.db_unique_name='hxsy_dg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hxsyXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_HXSY'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy_dg'
*.log_archive_dest_2='service=hxsy LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=hxsy'
*.log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072m
*.undo_tablespace='UNDOTBS1'
--备库以备库角色运行时需要设置的参数
*.db_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.fal_client='hxsy_dg'
*.fal_server='hxsy'
*.log_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.standby_file_management='auto'

在备库主机上创建参数文件

openaix1:/u01/app/oracle/product/19.3/db/dbs$vi inithxsy.ora
"inithxsy.ora" [New file] 
*.audit_file_dest='/u01/app/oracle/admin/hxsy/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/HXSY_DG/control01.ctl','/u01/app/oracle/oradata/HXSY_DG/control02.ctl'
*.db_block_size=8192
*.db_name='hxsy'
*.db_unique_name='hxsy_dg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hxsyXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_HXSY'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy_dg'
*.log_archive_dest_2='service=hxsy LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=hxsy'
*.log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072m
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.fal_client='hxsy_dg'
*.fal_server='hxsy'
*.log_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.standby_file_management='auto'

为主库和备库配置监听
主库

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hxsy)
      (ORACLE_HOME =/u01/app/oracle/product/19.3/db)
      (GLOBAL_DBNAME=hxsy)
    )
  )

备库

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hxsy)
      (ORACLE_HOME =/u01/app/oracle/product/19.3/db)
      (GLOBAL_DBNAME=hxsy_dg)
    )
  )

重启主库和备库的监听
主库:

aix1:/u01/app/oracle/product/19.3/db/network/admin$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 16:58:45

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 16:58:06
Uptime                    0 days 0 hr. 0 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hxsy" has 1 instance(s).
  Instance "hxsy", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

备库:

openaix1:/home/oracle$lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 17-NOV-2020 17:01:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/openaix1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=openaix1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=openaix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                17-NOV-2020 17:01:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/openaix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=openaix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hxsy_dg" has 1 instance(s).
  Instance "hxsy", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

为主库和备库创建Oracle Net服务名
主库:

HXSY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.24.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hxsy)
    )
  )


HXSY_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.24.116)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hxsy_dg)
      (UR=A)
    )
  )

备库:
使用备份创建备库

openaix1:/u01/app/oracle/oradata$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 17:07:09 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size                  9242136 bytes
Variable Size             754974720 bytes
Database Buffers         2449473536 bytes
Redo Buffers                7532544 bytes

aix1:/u01/app/oracle/product/19.3/db/dbs$rman target sys/hxsy_123456@hxsy auxiliary sys/hxsy_123456@hxsy_dg

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 16 17:23:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HXSY (DBID=1728588152)
connected to auxiliary database: HXSY (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 2020-11-16 17:23:12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2420 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.3/db/dbs/orapwhxsy'   ;
}
executing Memory Script

Starting backup at 2020-11-16 17:23:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2908 device type=DISK
Finished backup at 2020-11-16 17:23:16

contents of Memory Script:
{
   restore clone from service  'hxsy' standby controlfile;
}
executing Memory Script

Starting restore at 2020-11-16 17:23:16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/HXSY_DG/control01.ctl
output file name=/u01/app/oracle/oradata/HXSY_DG/control02.ctl
Finished restore at 2020-11-16 17:23:22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/HXSY_DG/temp01.dbf";
   set newname for tempfile  2 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf";
   set newname for tempfile  3 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/HXSY_DG/system01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to 
 "/u01/app/oracle/oradata/HXSY_DG/users01.dbf";
   set newname for datafile  8 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf";
   set newname for datafile  10 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf";
   set newname for datafile  11 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf";
   set newname for datafile  12 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf";
   restore
   from  nonsparse   from service 
 'hxsy'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HXSY_DG/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2020-11-16 17:23:32
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/HXSY_DG/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/HXSY_DG/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2020-11-16 17:25:31

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1056734739 file name=/u01/app/oracle/oradata/HXSY_DG/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1056734739 file name=/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf
Finished Duplicate Db at 2020-11-16 17:25:45

openaix1:/u01/app/oracle/oradata/HXSY_DG$sqlplus / as sysdba 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 17:26:54 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
hxsy             MOUNTED

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hxsy_dg

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY_DG/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/users01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf

11 rows selected.

对物理备库创建备重做日志文件
查询主库的联机重做日志文件，备重做日志文件的大小应该与主库联机重做日志文件的大小相同，备重做日志文件组的数量应该比主库联机重做日志文件组多一组，计算公式为

(maximum # of logfiles +1) * maximum # of threads

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus sys/hxsy_123456@hxsy as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 17:28:21 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY/redo03.log
/u01/app/oracle/oradata/HXSY/redo02.log
/u01/app/oracle/oradata/HXSY/redo01.log


SQL> select group#,thread#,bytes/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          1             200



SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY_DG/redo03.log
/u01/app/oracle/oradata/HXSY_DG/redo02.log
/u01/app/oracle/oradata/HXSY_DG/redo01.log

SQL> select group#,thread#,bytes/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         3          1             200
         2          1             200

备库:

SQL> alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/HXSY_DG/redo04.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/HXSY_DG/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/HXSY_DG/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/HXSY_DG/redo07.log') size 200M;

Database altered.

主库:

SQL> alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/HXSY/redo04.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/HXSY/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/HXSY/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/HXSY/redo07.log') size 200M;

Database altered.

设置主库相关初始化参数

log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
log_archive_dest_1='LOCATION=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy'
log_archive_dest_2='SERVICE=hxsy_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=hxsy_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
log_archive_format= %t_%s_%r.dbf
log_archive_max_processes=30

主库以备库角色运行时需要额外设置的参数。这些参数当主库被转换为备库角色运行时生效:

fal_server='hxsy_dg'
fal_client='hxsy'
db_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY'
log_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY'
standby_file_management='auto'


SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy' scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=hxsy_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=hxsy_dg'scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1=enable scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

System altered.


SQL> alter system set log_archive_max_processes=30 scope=both sid='*';

System altered.

SQL> alter system set fal_server='hxsy_dg' scope=both sid='*';

System altered.

SQL> alter system set fal_client='hxsy' scope=both sid='*';

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY' scope=spfile sid='*';

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY' scope=spfile sid='*';

System altered.

SQL> alter system set standby_file_management='auto' scope=both sid='*';

System altered.


SQL> alter system set log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)' scope=both sid='*';

System altered.



SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
HXSY      PRIMARY          READ WRITE


SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
HXSY      PHYSICAL STANDBY READ ONLY

使用alter database语句来启用实时应用功能:
.对于物理备库执行alter database recover managed standby database(在Oracle 12.1中需要指定current logfile子句来启用实时应用，但在12.2中不再需要)。

在备库节点上执行实时重做应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

alert日志信息如下

# tail -f /u01/app/oracle/diag/rdbms/hxsy_dg/hxsy/trace/alert_hxsy.log
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf'
2020-11-17T17:47:58.239174-06:00
File 202 not verified due to error ORA-01157
2020-11-17T17:47:58.390428-06:00
PDB$SEED(2):Re-creating tempfile /u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf
2020-11-17T17:47:59.013123-06:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
Physical standby database opened for read only access.
Completed: alter database open
2020-11-17T17:50:46.966690-06:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
2020-11-17T17:50:47.069179-06:00
Attempt to start background Managed Standby Recovery process (hxsy)
Starting background process MRP0
2020-11-17T17:50:47.296690-06:00
MRP0 started with pid=67, OS id=10682718 
2020-11-17T17:50:47.329349-06:00
Background Managed Standby Recovery process started (hxsy)
2020-11-17T17:50:52.560871-06:00
 Started logmerger process
2020-11-17T17:50:52.734970-06:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 
PR00 (PID:12190072): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2020-11-17T17:50:54.165887-06:00
Parallel Media Recovery started with 8 slaves
2020-11-17T17:50:54.389175-06:00
stopping change tracking
2020-11-17T17:50:54.609199-06:00
PR00 (PID:12190072): Media Recovery Log /u01/app/oracle/arch/1_7_1056672314.dbf
2020-11-17T17:50:54.799176-06:00
PR00 (PID:12190072): Media Recovery Log /u01/app/oracle/arch/1_8_1056672314.dbf
2020-11-17T17:50:54.877923-06:00
TT02 (PID:12779914): Waiting for all non-current ORLs to be archived
2020-11-17T17:50:54.987923-06:00
TT02 (PID:12779914): All non-current ORLs have been archived
2020-11-17T17:50:55.311006-06:00
Completed: alter database recover managed standby database using current logfile disconnect from session
2020-11-17T17:50:55.335874-06:00
PR00 (PID:12190072): Media Recovery Waiting for T-1.S-9 (in transit)
2020-11-17T17:50:55.376975-06:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
2020-11-17T17:50:55.412407-06:00
  Mem# 0: /u01/app/oracle/oradata/HXSY_DG/redo04.log

在主库创建表空间test

SQL> create tablespace test datafile '/u01/app/oracle/oradata/HXSY/test01.dbf' size 50M;

Tablespace created.


SQL>  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

SQL> select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         3          0 SYSTEM
         3          1 SYSAUX

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         3          2 UNDOTBS1
         3          3 TEMP
         3          5 USERS
         1          6 TEST

15 rows selected.

SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS# NAME
---------- --------------------------------------------------
         6 /u01/app/oracle/oradata/HXSY/test01.dbf

备库alert日志如下:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
2020-11-17T17:50:55.412407-06:00
  Mem# 0: /u01/app/oracle/oradata/HXSY_DG/redo04.log
2020-11-17T17:52:48.412685-06:00
Recovery created file /u01/app/oracle/oradata/HXSY_DG/test01.dbf
2020-11-17T17:52:48.453155-06:00
Errors in file /u01/app/oracle/diag/rdbms/hxsy_dg/hxsy/trace/hxsy_dbw0_7602670.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
Successfully added datafile 13 to media recovery
Datafile #13: '/u01/app/oracle/oradata/HXSY_DG/test01.dbf'
2020-11-17T17:53:13.126690-06:00
Control autobackup written to DISK device

备库:

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

SQL> select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         3          0 SYSTEM
         3          1 SYSAUX

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         3          2 UNDOTBS1
         3          3 TEMP
         3          5 USERS
         1          6 TEST

15 rows selected.

SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS# NAME
---------- --------------------------------------------------
         6 /u01/app/oracle/oradata/HXSY_DG/test01.dbf

验证
主库:

SQL> create table t1 as select * from dba_users;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
        36


SQL> create table t2(id number(20),name varchar2(20));

Table created.

SQL> insert into t2 values(1,'jy');

1 row created.

SQL> commit;

Commit complete.

SQL> update t2 set name='hy' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from t2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> create table t3(id number(20),name varchar2(20)) tablespace test;

Table created.

SQL> insert into t3 values(1,'jy');

1 row created.

SQL> commit;

Commit complete.

备库

SQL> select count(*) from t1;

  COUNT(*)
----------
        36

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(20)
 NAME                                               VARCHAR2(20)

SQL> select * from t2;

        ID NAME
---------- --------------------------------------------------
         1 jy

SQL> select * from t2;

        ID NAME
---------- --------------------------------------------------
         1 hy

SQL> select * from t2;

no rows selected

SQL> 
SQL> 
SQL> desc t3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(20)
 NAME                                               VARCHAR2(20)

SQL> select * from t3;

        ID NAME
---------- --------------------------------------------------
         1 jy

验证物理备库是否执行正确
在创建物理备库后并且设置重做传输服务，可能想要验证主库的数据库修改是否会成功的传输到备库。对于备库可以查询v$managed_standby视图来验证重做是否被从主库传输到备库并应用。

SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where client_process='LGWR' or process='MRP0';

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1          9 IDLE
N/A      MRP0               1          9 APPLYING_LOG

上面的查询对于使用CLIENT_PROCESS为LGWR的主库会显示一行记录，它指示重做传输工作正常并且主重做线程将会被发送到备库。如果主库是RAC数据库，那么对于使用CLIENT_PROCESS为LGWR的当前活动的每个主库实例都会显示一行记录。

上面的查询对于MRP也行显示一行。如果MRP的状态显示为APPLYING_LOG并且SEQUENCE#等于主库当前正被发送的日志序列号，那么备库已经解决了所有的日志差异并且当前处于实时应用日志模式。

查询主库当前正被发送日志的序列号为9与上面的MRP进程所显示的sequence#(9)相同

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1          7 INACTIVE
         2          1          8 INACTIVE
         3          1          9 CURRENT

注意MRP进程可能显示的sequence#比主库当前被发送的日志序列号小，那么这就表示正在应用的归档重做日志文件与发送的日志文件之间存在差异并且它并没有赶上。一旦所有差异被解决，相同的查询将显示MRP正在应用当前sequence#。

openPower服务器安装Oracle 19c

Posted on 2021年1月15日 by YongJing(敬勇)

在openPower服务器上安装Oracle 19C
1.创建用户与组
1.1.创建组(在各个主机上执行)

mkgroup -'A' id='501' adms='root' oinstall
mkgroup -'A' id='502' adms='root' asmdba
mkgroup -'A' id='503' adms='root' asmoper
mkgroup -'A' id='504' adms='root' dba
mkgroup -'A' id='505' adms='root' oper
mkgroup -'A' id='506' adms='root' backdba
mkgroup -'A' id='507' adms='root' dgdba
mkgroup -'A' id='508' adms='root' racdba
mkgroup -'A' id='509' adms='root' kmdba

1.2.创建用户(在各个主机上执行)

mkuser id=601 pgrp=oinstall groups=dba,asmdba,oper,asmoper,backdba,dgdba,racdba,kmdba home=/home/oracle oracle

创建安装目录

mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle/product/19.3/db
mkdir -p /u01/temp

chown -R oracle:oinstall /u01
chmod -R 775 /u01

2.设置用户环境变量（用oracle用户来编辑.profile)

umask 022
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3/db
export ORACLE_SID=hxsy
export ORACLE_UNQNAME=hxsy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/u01/tmp
export TMPDIR=/u01/tmp
export PS1=`hostname`:'$PWD'"$"

3.使用如下命令查看虚拟内存管理参数

vmo -L minperm%
vmo -L maxperm%
vmo -L maxclient%
vmo -L lru_file_repage
vmo -L strict_maxclient
vmo -L strict_maxperm

4.如果设置不合适，使用如下命令修改：

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

5.检查网络参数设置
ephemeral参数：
5.1 使用命令no -a |fgrep ephemeral可以查看当前系统ephemeral参数设置，建议的参数设置如下

tcp_ephemeral_high = 65500
tcp_ephemeral_low = 9000
udp_ephemeral_high= 65500
udp_ephemeral_low = 9000

如果系统中参数设置和上述值不一样，使用命令修改：

#no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
#no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

使用如下命令修改网络可调整参数：

#no -p -o rfc1323=1
#no -r -o ipqmaxlen=512
#no -p -o ipqmaxlen=512
#no -p -o sb_max=4194304
#no -p -o tcp_recvspace=65536
#no -p -o tcp_sendspace=65536
#no -p -o udp_recvspace=1351680 该值是udp_sendspace的10倍，但须小于sb_max
#no -p -o udp_sendspace=135168

备注：-r表示reboot后生效，-p表示即刻生效.
检查内核参数maxuproc（建议16384）和ncargs（至少128）

#lsattr -E -l sys0 -a ncargs
#lsattr -E -l sys0 -a maxuproc

如果设置不合适使用如下命令修改：

#chdev -l sys0 -a ncargs=256
#chdev -l sys0 -a maxuproc=16384

6.解压数据库软件

aix1:/u01/soft$unzip -q AIX.PPC64_193000_db_home.zip -d $ORACLE_HOME

7.安装数据库软件
在$ORACLE_HOME/install/response目录中有一个安装rsp文件示例
7.1.配置响应文件

[oracle@ora19c ~]$ vi 19c_dbinstall.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3/db
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT

8.执行安装

openaix1:/home/oracle$$ORACLE_HOME/runInstaller -silent -force -noconfig -ignorePrereq -responseFile /home/oracle/19c_dbinstall.rsp

********************************************************************************

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root in this machine? [y/n] (n)
y
Launching Oracle Database Setup Wizard...

9.配置监听
创建配置监听的响应文件
在/u01/app/oracle/product/19.3/db/assistants/netca/目录有一个示例文件

aix1:/home/oracle$vi 19c_netca.rsp
"19c_netca.rsp" [New file]

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}


aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp
UnsatisfiedLinkError exception loading native library: njni19
java.lang.UnsatisfiedLinkError: njni19 (Not found in java.library.path)
java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
        at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
        at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
        at oracle.net.ca.NetCA.main(NetCA.java:459)

Error: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
Oracle Net Services configuration failed.  The exit code is 1


# lsdev | grep iocp
iocp0      Defined         I/O Completion Ports
# smitty iocp

# lsdev | grep iocp
iocp0      Available       I/O Completion Ports


aix1:/home/oracle$$ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/oracle/product/19.3/db/install/relinkActions2020-11-16_11-17-27PM.log

aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /home/oracle/19c_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/19.3/db/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

aix1:/home/oracle$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 23:24:53

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 23:23:28
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

10.创建数据库
10.1 创建配置数据库的响应文件
在/u01/app/oracle/product/19.3/db/assistants/dbca/目录中有一个示例文件

[oracle@ora19c install]$ cat /u01/app/oracle/product/19.3/db/assistants/dbca/dbca.rsp


aix1:/home/oracle$vi 19c_dbca.rsp
automaticMemoryManagement=TRUE
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
templateName=General_Purpose.dbc
gdbName=hxsy
sid=hxsy
databaseConfigType=SI
createAsContainerDatabase=TRUE
numberOfPDBs=1
pdbName=hxsy1
useLocalUndoForPDBs=TRUE
pdbAdminPassword=hxsy_123456
sysPassword=hxsy_123456
systemPassword=hxsy_123456
storageType=FS
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
listeners=LISTENER
sampleSchema=TRUE
totalMemory 2048
databaseType=MULTIPURPOSE
automaticMemoryManagement=FALSE
totalMemory=4096

11.创建数据库

12.验证是否可以登录数据库

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:32:33 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size                  9303576 bytes
Variable Size             956301312 bytes
Database Buffers         2231369728 bytes
Redo Buffers               24248320 bytes
Database mounted.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          MOUNTED

SQL> alter pluggable database hxsy1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          READ WRITE NO

MySQL InnoDB搜索索引的Stopwords

Posted on 2021年1月15日 by YongJing(敬勇)

InnoDB搜索索引的Stopwords
InnoDB的默认禁止词列表相对较短，因为来自技术、文学和其他来源的文档经常使用短词作为关键字或重要短语。例如，你可能搜索“是”或“不是”，并期望得到一个合理的结果，而不是让所有这些词都被忽略
InnoDB默认的stopword列表可以通过查询INFORMATION_SCHEMA查看。INNODB_FT_DEFAULT_STOPWORD表。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+
36 rows in set (0.01 sec)

为了对所有InnoDB表定义了一个自定义的stopword列表，那么使用与innodb_ft_default_stopword表相同的结构来定义你自定义的stopword表，然后向表中插入stopwords，并且在创建全文索引之前以db_name/table_name的形式设置innodb_ft_server_stopword_table选项的值。自定义的stopword表必须有一个varchar类型的value列。下面的例子演示了如何为innodb创建一个新的全局stopword表。

mysql> CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
Query OK, 0 rows affected (0.21 sec)


mysql> INSERT INTO my_stopwords(value) VALUES ('Ishmael');
Query OK, 1 row affected (0.12 sec)


mysql> CREATE TABLE opening_lines (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> opening_line TEXT(500),
    -> author VARCHAR(200),
    -> title VARCHAR(200)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)


mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
    -> ('Call me Ishmael.','Herman Melville','Moby-Dick'),
    -> ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
    -> ('I am an invisible man.','Ralph Ellison','Invisible Man'),
    -> ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
    -> ('It was love at first sight.','Joseph Heller','Catch-22'),
    -> ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
    -> ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
    -> ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.04 sec)
Records: 8  Duplicates: 0  Warnings: 0



mysql> SET GLOBAL innodb_ft_server_stopword_table = 'mysql/my_stopwords';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 1

默认情况下，长度小于3个字符或大于84个字符的单词不会出现在InnoDB全文搜索索引中。最大和最小字长值可以通过innodb_ft_max_token_size和innodb_ft_min_token_size变量进行配置。这种默认行为不适用于ngram解析器插件。ngram令牌大小由
ngram_token_size选项定义。

通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE中的单词，验证指定的stopword (‘Ishmael’)没有出现。

mysql> SET GLOBAL innodb_ft_aux_table='mysql/opening_lines';
Query OK, 0 rows affected (0.01 sec)


mysql> SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+-----------+
| word      |
+-----------+
| across    |
| all       |
| burn      |
| buy       |
| call      |
| comes     |
| dalloway  |
| first     |
| flowers   |
| happened  |
| herself   |
| invisible |
| less      |
| love      |
| man       |
| more      |
| mrs       |
| now       |
| now       |
| now       |
| pleasure  |
| said      |
| screaming |
| she       |
| sight     |
| sky       |
| the       |
| the       |
| this      |
| was       |
| was       |
| when      |
| where     |
| who       |
| would     |
+-----------+
35 rows in set (0.00 sec)

要在一个表一个表的基础上创建stopword列表，需要创建其他stopword表，并在创建全文索引之前使用innodb_ft_user_stopword_table选项指定想要使用的stopword表。

MySQL 全文搜索与查询扩展

Posted on 2021年1月15日 by YongJing(敬勇)

全文搜索与查询扩展
全文搜索支持查询扩展(特别是它的变体盲查询扩展)。当搜索短语太短时，这通常很有用，因为这通常意味着用户依赖于全文搜索引擎所缺乏的隐含知识。例如，用户搜索数据库可能意味着MySQL、Oracle、DB2和RDBMS都是应与数据库匹配并应返回的短语。这是隐含知识。

盲查询扩展(也称为自动关联反馈)是通过添加查询扩展或以自然语言模式在搜索短语之后添加查询扩展来实现的。它的工作原理是执行两次搜索，其中第二次搜索的搜索短语是原始搜索短语与第一次搜索中几个相关度最高的文档相连接。因此，如果其中一个文档包含单词”database”和单词”MySQL”，那么第二次搜索将找到包含单词”MySQL”的文档，即使它们不包含单词”database”。下面的示例显示了这种差异:

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+------------------------------+-------------------------------+
| id | title                        | body                          |
+----+------------------------------+-------------------------------+
|  6 | Database, Database, Database | database database database    |
|  3 | Optimizing Your Database     | In this database tutorial ... |
|  1 | MySQL Tutorial               | This database tutorial ...    |
+----+------------------------------+-------------------------------+
3 rows in set (0.01 sec)


mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  3 | Optimizing Your Database     | In this database tutorial ...       |
|  6 | Database, Database, Database | database database database          |
|  1 | MySQL Tutorial               | This database tutorial ...          |
|  5 | MySQL Security               | When configured properly, MySQL ... |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |
|  2 | How To Use MySQL             | After you went through a ...        |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
+----+------------------------------+-------------------------------------+
8 rows in set (0.01 sec)

另一个例子是搜索Georges Simenon写的关于Maigret的书，当用户不知道如何拼写Maigret时。对麦格里和不情愿证人的搜索发现，只有麦格里和不情愿证人没有查询扩展。带有查询扩展的搜索在第二次搜索时发现所有带有单词Maigret的书籍。因为盲查询扩展会通过返回不相关的文档而显著增加噪声，所以只在搜索短语很短的时候使用盲查询扩展。

MySQL单词搜索相关度排名

Posted on 2021年1月15日 by YongJing(敬勇)

一个单词搜索的相关度排名,这个例子演示了一个单词搜索的相关度排名计算。

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.56 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','This database tutorial ...'),
    -> ("How To Use MySQL",'After you went through a ...'),
    -> ('Optimizing Your Database','In this database tutorial ...'),
    -> ('MySQL vs. YourSQL','When comparing databases ...'),
    -> ('MySQL Security','When configured properly, MySQL ...'),
    -> ('Database, Database, Database','database database database'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0



mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) AS score FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+---------------------+
| id | title                        | body                                | score               |
+----+------------------------------+-------------------------------------+---------------------+
|  6 | Database, Database, Database | database database database          |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...       | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...          | 0.18144935369491577 |
|  2 | How To Use MySQL             | After you went through a ...        |                   0 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |                   0 |
|  5 | MySQL Security               | When configured properly, MySQL ... |                   0 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |                   0 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |                   0 |
+----+------------------------------+-------------------------------------+---------------------+
8 rows in set (0.00 sec)

总共有8条记录，其中3条与数据库搜索词匹配。第一条记录(id 6)包含搜索词6次，相关度排名为1.0886961221694946。这个排名值计算使用TF的价值6(数据库搜索词出现6次记录id 6)和IDF值为0.42596873216370745,计算如下(8是记录的总数和3是包含搜索词的记录数量)

${IDF} = log10( 8 / 3 ) = 0.42596873216370745

mysql> select log10( 8 / 3 ) ;
+---------------------+
| log10( 8 / 3 )      |
+---------------------+
| 0.42596873216370745 |
+---------------------+
1 row in set (0.00 sec)

然后将TF和IDF值输入到排名公式中

${rank} = ${TF} * ${IDF} * ${IDF}

在MySQL命令行客户端执行计算将返回一个排名值1.088696164686938。

mysql> SELECT 6*log10(8/3)*log10(8/3);
+-------------------------+
| 6*log10(8/3)*log10(8/3) |
+-------------------------+
|       1.088696164686938 |
+-------------------------+
1 row in set (0.00 sec)

您可能会注意到SELECT … MATCH … AGAINST语句和MySQL命令行客户端所计算的排名值有差别(1.0886961221694946对1.088696164686938)。区别在于InnoDB内部是如何执行整数和浮点数/双精度类型转换的(以及相关的精度和四舍五的决定)，以及它们在其他地方是如何执行的，比如在MySQL命令行客户端或其他类型的计算器中。

多词搜索的相关度排名
这个示例演示了基于前面示例中使用的articles表和数据计算多单词全文搜索的相关度排名。

如果你搜索的是一个以上的单词，那么相关度排名值就是每个单词相关度排名值的总和，如下公式所示:

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

执行两个搜索词(‘mysql tutorial’)搜索将返回以下结果:

mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

在第一条记录(id 1)中，“mysql”出现一次，“tutorial”出现两次。“mysql”有六条匹配记录，“tutorial”有两条匹配记录。当将这些值插入到用于多个单词搜索的排名公式中时，MySQL命令行客户端返回预期的排名值

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));
+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
|                                    0.7405621541938003 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

与单个单词搜索一样，使用select … match … against语句和MySQL命令行工具执行的结果有差别。

MySQL 生成列索引

Posted on 2021年1月15日 by YongJing(敬勇)

MySQL支持在生成列上创建索引，例如:

mysql> create table t1(f1 int,gc int as (f1+1) stored,index(gc));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1(f1) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| f1   | gc   |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
|    4 |    5 |
|    5 |    6 |
|    6 |    7 |
|    7 |    8 |
|    8 |    9 |
|    9 |   10 |
|   10 |   11 |
+------+------+
10 rows in set (0.00 sec)

生成列gc，它的定义为表达式f1+1。这个列gc也创建了索引因此在生成执行计划时优化器可以使用这个索引。下面的查询where条件引用了列gc并且优化器会考虑使用这个索引是否可以生成更有效的执行计划:

mysql> explain select * from t1 where gc>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

优化器可以在生成的列上使用索引来生成执行计划，即使在查询中没有按名称直接引用这些列的情况下也是如此。如果where，order by 或group by子句引用的表达式与某些被索引的生成列相匹配就会出现这种情况。下面的查询没有直接引用生成列gc，但使用的表达式与生成列gc的定义匹配:

mysql> explain select * from t1 where f1+1>9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

优化器识别出了表达式f1+1与生成列gc相匹配并且gc列创建了索引，所以优化器在生成执行计划时考虑使用了该索引。

实际上，优化器已经用与表达式匹配的生成列的名称gc替换了表达式f1 + 1。这在执行show warnings命令所显示的扩展解释信息中可以很明显地看到重写查询语句确实用生成列替换了表达式。

mysql> show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`t1`.`f1` AS `f1`,`mysql`.`t1`.`gc` AS `gc` from `mysql`.`t1` where (`mysql`.`t1`.`gc` > 9)
1 row in set (0.00 sec)

优化器使用生成列索引有以下限制和条件:
.对于一个查询语句的表达式是否成生成列定义相匹配，那么表达式必须完全相同并且有相同的结果类型。例如，如果生成列表达式是f1+1，如果查询使用1+f1或者如查f1+1(一个整数表达式)与字符进行比较而不是数字那么优化器将不会认为是匹配的。

.对于这些操作:=,< ,<=,>,>=,between和in优化器处理是，对于不是between和in之外的其它操作符而言，任何一个操作数都可以被匹配的生成列所替换。对于between和in操作符，只有第一个参数可以被匹配的生成列替换，并且其它的参数必须要有相同的结果类型。between和in操作符目前还不支持对JSON的调用。

.生成列必须定义为至少包含一个函数调用或前一项中提到的一个运算符的表达式。表达式不能只是简单的引用其它列。例如，gc int as (f1) stored，这个生成列的定义只是简单的引用了一个列，因此在生成列gc上的索引不会被优化器考虑。

.为了比较字符串与调用JSON函数返回带引号的字符串的被索引的生成列，JSON_UNQUOTE()函数需要在生成列定义中用来删除函数返回值的引号(对于直接比较字符串与函数值，JSON比较器句柄会删了引号，但在索引查找时不会发生)。

.如果优化器未能选择所需的索引，则可以使用索引提示强制优化器做出不同的选择。