Skip to content

All posts by ludatou - 3. page

在前阵子有一个客户的Mysql数据库被恶意删除,通过一晚努力恢复回来了。这里介绍下mysql的特殊恢复工具 undrop for innodb。

 

官网:

http://twindb.com/undrop-tool-for-innodb/

 

首先这是一个开源的工具,在github上有更新,在官网上也有对各个场景的使用介绍,这里不做多的介绍,我这里列举一个问题就是sys_parse工具的编译,官网提供的方法以及read me里的方式在我的环境里是编译不过去的,客户的数据库版本是5.1的,所在的系统平台是Cent os,但是需要引起注意的undrop for innodb有建议使用的操作系统版本,我使用的是Centos 6.5,Mysql 5.7版。下面是这次的恢复过程,请注意我改变的编译语法。

 

CentOs 6.5 Mysql 5.7.23

./stream_parser -f /mysqldata/ibdata1
[root@localhost undrop-for-innodb-develop]# ./stream_parser -f /mysqldata/ibdata1
Opening file: /mysqldata/ibdata1
File information:
ID of device containing file:        64512
inode number:                       652164
protection:                         100755 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36912
time of last access:            1559729794 Wed Jun  5 18:16:34 2019
time of last modification:      1559281768 Fri May 31 13:49:28 2019
time of last status change:     1559729794 Wed Jun  5 18:16:34 2019
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
[root@localhost FIL_PAGE_INDEX]# ls -al
total 1812
drwxr-xr-x 2 root root   4096 Jun  5 18:23 .
drwxr-xr-x 4 root root   4096 Jun  5 18:23 ..
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000001.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000002.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000003.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000004.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000005.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000006.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000011.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000012.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000013.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000014.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000043.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000044.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000045.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000046.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000047.page
-rw-r–r– 1 root root 262144 Jun  5 18:23 0000000000000048.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000049.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000050.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000051.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000052.page
-rw-r–r– 1 root root 147456 Jun  5 18:23 0000000000000053.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000054.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000055.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000056.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000057.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000058.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000059.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000060.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000061.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000062.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000063.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000064.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000065.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000066.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000067.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000068.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000069.page
-rw-r–r– 1 root root  81920 Jun  5 18:23 0000000000000070.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000071.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000072.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 18446744069414584320.page
mkdir -p dumps/default
恢复sys_tables
[root@localhost undrop-for-innodb-develop]# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/default/SYS_TABLES  -l ./dumps/default/SYS_TABLES.sql
[root@localhost undrop-for-innodb-develop]# cd dumps
[root@localhost dumps]# ls default/
SYS_TABLES  SYS_TABLES.sql
[root@localhost dumps]# ls
default
[root@localhost dumps]# cd default/
[root@localhost default]# ls
SYS_TABLES  SYS_TABLES.sql
[root@localhost default]# cat SYS_TABLES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 3)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 25, Lost records: YES, Leaf page: YES
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/SYS_TABLES  -l ./dumps/SYS_TABLES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql -o ./dumps/SYS_INDEXES  -l ./dumps/SYS_INDEXES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql -o ./dumps/SYS_COLUMNS  -l ./dumps/SYS_COLUMNS.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql -o ./dumps/SYS_FIELDS  -l ./dumps/SYS_FIELDS.sql
create database dictionary;
use dictionary;
mysql> source  /undropmysql/dictionary/SYS_TABLES.sql
mysql> source  /undropmysql/dictionary/SYS_INDEXES.sql
mysql> source  /undropmysql/dictionary/SYS_FIELDS.sql
mysql> source  /undropmysql/dictionary/SYS_COLUMNS.sql
mysql> source  /undropmysql/dictionary/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> .
    ->
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.’ at line 1
mysql> source  /undropmysql/dictionary/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> /undropmysql/dictionary/SYS_FIELDS.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/undropmysql/dictionary/SYS_FIELDS.sql’ at line 1
mysql> source  /undropmysql/dictionary/SYS_FIELDS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> source  /undropmysql/dictionary/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#执行前面生成的LOAD DATA语句导入恢复的记录
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_TABLES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_COLUMNS.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_INDEXES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_FIELDS.sql
 要用mysql_config的绝对路径
 make sys_parser /usr/bin/mysql_config cc -o sys_parser sys_parser.c ‘mysql_config –cflags’ ‘mysql_config –libs’  ===>测试不行
 make sys_parser /usr/bin/mysql_config cc ‘mysql_config –cflags’ ‘mysql_config –libs’ -o sys_parser sys_parser.c  ===>测试不行
  gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags) ==》重新修改代码后按照c语言编译的方法改写通过
 mysql> desc SYS_TABLES
    -> ;
+————–+———————+——+—–+———+——-+
| Field        | Type                | Null | Key | Default | Extra |
+————–+———————+——+—–+———+——-+
| NAME         | varchar(255)        | NO   | PRI |         |       |
| ID           | bigint(20) unsigned | NO   |     | 0       |       |
| N_COLS       | int(10)             | YES  |     | NULL    |       |
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       |
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       |
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       |
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       |
+————–+———————+——+—–+———+——-+
8 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+———————————-+—-+——–+——+——–+———+————–+——-+
| NAME                             | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+———————————-+—-+——–+——+——–+———+————–+——-+
| car_doc/t_advert                 | 30 |     13 |    1 |      0 |       0 |              |     0 |
| car_doc/t_auth_config            | 31 |      8 |    1 |      0 |       0 |              |     0 |(空表)
| car_doc/t_book_type              | 32 |     17 |    1 |      0 |       0 |              |     0 |
| car_doc/t_car_type               | 33 |     18 |    1 |      0 |       0 |              |     0 |
| car_doc/t_count                  | 34 |      8 |    1 |      0 |       0 |              |     0 |
| car_doc/t_dict                   | 18 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_feedback               | 36 |      4 |    1 |      0 |       0 |              |     0 |
| car_doc/t_log                    | 37 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager                | 38 |      9 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_authbak        | 39 |      7 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_role           | 40 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_meals                  | 41 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_news                   | 42 |     12 |    1 |      0 |       0 |              |     0 |
| car_doc/t_order                  | 26 |     16 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role                   | 44 |      6 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role_auth              | 45 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_user                   | 46 |      9 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING       | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00231 | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00232 | 47 |      4 |    1 |      0 |       0 |              |     0 |
+———————————-+—-+——–+——+——–+———+————–+——-+
20 rows in set (0.00 sec)
 gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags)
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_advert
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_auth_config
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_book_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_car_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_count
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_dict
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_feedback
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_log
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_authbak
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_meals
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_news
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_order
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role_auth
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_user
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00231
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00232
mysql>  create database car_doc;
Query OK, 1 row affected (0.00 sec)
mysql> use car_doc
Database changed
mysql> source /undropmysql/car_doc/t_advert.sql
Query OK, 0 rows affected (0.04 sec)
mysql> source /undropmysql/dumps/default/t_advert.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.07 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from car_doc;
ERROR 1146 (42S02): Table ‘car_doc.car_doc’ doesn’t exist
mysql> select * from t_advert;
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
| id  | url            | title            | createor            | status | createTime | updateTime | sort        | updateor | picture | releaseTime | approvalHistory | offlineTime |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
|  24 | 12016051209173 | 3094e08e0738c422 | 51007424128@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  25 | 12016051210135 | 2dd39aef34bf64a7 | 8568144450@qq.co    | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  26 | 12016061312503 | 19864b15399af40d | 51007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 119 | 12017100914075 | 290c508ebb5434bd | 8dfylxxgk@163.co    | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 120 | 12017100914084 | 1f9fad68aa8404ae | 31007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 121 | 12017100914093 | 6bbabcaa806f0400 | 1zhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 122 | 12018051611300 | 852199cab75a745f | e2042213325@qq.co   | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 123 | 12018052315465 | 3b5ff7ba13b784f9 | e2042213325@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 124 | 12018081413501 | 254bdc81e991a495 | bberling@livemail.t | w      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 125 | 12018082911062 | 85f7a36723107478 | 384558040@qq.co     | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 126 | 12018111208521 | 1aeafacfb1013418 | azhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
11 rows in set (0.00 sec)
mysql>
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000016.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000047.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
source /undropmysql/car_doc/t_auth_config.sql
source /undropmysql/dumps/default/t_auth_config.sql
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_auth_config
    -> ;
+————-+————-+——+—–+———+——-+
| Field       | Type        | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| id          | bigint(20)  | NO   | PRI | NULL    |       |
| name        | varchar(40) | NO   |     | NULL    |       |
| url         | varchar(60) | NO   |     | NULL    |       |
| status      | int(11)     | NO   |     | NULL    |       |
| pid         | bigint(20)  | NO   |     | NULL    |       |
| createtime  | datetime    | NO   |     | NULL    |       | 类型改为varchar
| operator    | varchar(45) | YES  |     | NULL    |       |
| operatetime | datetime    | YES  |     | NULL    |       |
+————-+————-+——+—–+———+——-+ 类型改为varchar
8 rows in set (0.01 sec)
mysql> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_auth_config.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 67 rows affected (0.01 sec)
Records: 34  Deleted: 33  Skipped: 0  Warnings: 0
======重要 表数据为如下 ==== 因为时间导出有问题,导入mysql有异常故截取记录 以供查询(有33条记录标记为被正常删除过的) =========
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
=====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000048.page -t car_doc/t_book_type.sql  > dumps/default/t_book_type 2> dumps/default/t_book_type.sql
mysql> source /undropmysql/dumps/default/t_book_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 554 rows affected (0.12 sec)
Records: 554  Deleted: 0  Skipped: 0  Warnings: 0
修改了 t_book_type表的字段approvalHistory的属性text为varchar(20000)
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000049.page -t car_doc/t_car_type.sql  > dumps/default/t_car_type 2> dumps/default/t_car_type.sql
source /undropmysql/car_doc/t_car_type.sql
source /undropmysql/dumps/default/t_car_type.sql
mysql>
mysql>
mysql> source /undropmysql/car_doc/t_car_type.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_car_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 38 rows affected (0.00 sec)
Records: 38  Deleted: 0  Skipped: 0  Warnings: 0
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
source /undropmysql/car_doc/t_count.sql
source /undropmysql/dumps/default/t_count.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_count.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_count.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.01 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000051.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
source /undropmysql/car_doc/t_dict.sql
source /undropmysql/dumps/default/t_dict.sql
空表
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_dict.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_dict.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘stat: No such file or directory
Can’t stat /undropmysql/pages-ibdata1/FIL_PAGE_I’ at line 1
mysql> source /undropmysql/dumps/default/t_dict.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.00 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
source /undropmysql/car_doc/t_feedback.sql
source /undropmysql/dumps/default/t_feedback.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_feedback.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_feedback.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 20 rows affected (0.01 sec)
Records: 20  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000025.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
source /undropmysql/car_doc/t_log.sql
source /undropmysql/dumps/default/t_log.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_log.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_log.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1138 rows affected (0.05 sec)
Records: 1138  Deleted: 0  Skipped: 0  Warnings: 0
========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000055.page -t car_doc/t_manager.sql  > dumps/default/t_manager 2> dumps/default/t_manager.sql
source /undropmysql/car_doc/t_manager.sql
source /undropmysql/dumps/default/t_manager.sql
空表无数据(或者数据丢失)
[root@localhost default]# cat t_manager
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
[root@localhost default]# cat t_manager.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/undropmysql/dumps/default/t_manager’ REPLACE INTO TABLE `t_manager` CHARACTER SET UTF8 FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘t_manager\t’ (`id`, `account`, `password`, `isdisable`);
— STATUS {“records_expected”: 2, “records_dumped”: 0, “records_lost”: true} STATUS END
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_manager.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000058.page -t car_doc/t_manager_authbak.sql  > dumps/default/t_manager_authbak 2> dumps/default/t_manager_authbak.sql
source /undropmysql/car_doc/t_manager_authbak.sql
source /undropmysql/dumps/default/t_manager_authbak.sql
倒数第二个字段有乱码,无法识别因此保留数据给予手工录入
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000062.page -t car_doc/t_manager_role.sql  > dumps/default/t_manager_role 2> dumps/default/t_manager_role.sql
source /undropmysql/car_doc/t_manager_role.sql
source /undropmysql/dumps/default/t_manager_role.sql
Database changed
mysql> source /undropmysql/car_doc/t_manager_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000063.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000035.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
source /undropmysql/car_doc/t_meals.sql
source /undropmysql/dumps/default/t_meals.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/dumps/default/t_meals.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
source /undropmysql/car_doc/t_news.sql
source /undropmysql/dumps/default/t_news.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_news.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_news.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
source /undropmysql/car_doc/t_order.sql
source /undropmysql/dumps/default/t_order.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_order.sql
Query OK, 0 rows affected (0.03 sec)
mysql> source /undropmysql/dumps/default/t_order.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 318 rows affected (0.01 sec)
Records: 318  Deleted: 0  Skipped: 0  Warnings: 0
===========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
source /undropmysql/car_doc/t_role.sql
source /undropmysql/dumps/default/t_role.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> source /undropmysql/car_doc/t_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.01 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
source /undropmysql/car_doc/t_role_auth.sql
source /undropmysql/dumps/default/t_role_auth.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> source /undropmysql/car_doc/t_role_auth.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role_auth.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 103 rows affected (0.01 sec)
Records: 103  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
source /undropmysql/car_doc/t_user.sql
source /undropmysql/dumps/default/t_user.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_user.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 271 rows affected (0.01 sec)
Records: 271  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING.sql  > dumps/default/WARNING 2> dumps/default/WARNING.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
source /undropmysql/dumps/default/WARNING.sql
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use PLEASE_READ_ME_VVV
Database changed
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”WARNING'(
        ‘id’ INT NOT NULL,
        ‘warning’ TEXT,
        ‘Bitcoin_A’ at line 1
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/WARNING.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
source /undropmysql/dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use PLEASE_READ_ME_VVV
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
ERROR 1050 (42S01): Table ‘WARNING@00231’ already exists
mysql> source /undropmysql/dumps/default/WARNING@00231.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000071.page -t PLEASE_READ_ME_VVV/WARNING@00232.sql  > dumps/default/WARNING@00232 2> dumps/default/WARNING@00232.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00232.sql
source /undropmysql/dumps/default/WARNING@00232.sql
mysql> use PLEASE_READ_ME_VVV
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/dumps/default/WARNING@00232.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
恢复完成

==================================================================

==========================

 

 

Mysql data unload – undrop for innodb

Automatic indexing是Oracle Database 19c开始新增加的特性,依据应用负载的变化自动/动态地进行索引的管理任务,比如创建(create index)、重构(rebuild index)和删除(drop index),从而提高数据库性能,这个特性也是Oracle 自治数据库云服务自我优化的一个基础。
Automatic indexing 主要功能
1)定期在预定义的时间间隔内在后台运行自动索引过程
2)分析应用程序工作负载,并根据分析报告相应地创建必要的新的索引,并删除现有耗费性能的

Automatic Indexing相关的数据字典
DBA_AUTO_INDEX_CONFIG –描述当前自动索引的配置
DBA_INDEXES/ALL_INDEXES/USER_INDEXES –新增加的AUTO列标识是自动索引(YES)还是手动索引(NO)
DBA_AUTO_INDEX_EXECUTIONS –显示历史自动索引任务执行
DBA_AUTO_INDEX_STATISTICS –显示与自动索引相关的统计信息
DBA_AUTO_INDEX_IND_ACTIONS –显示在自动索引上执行的操作
DBA_AUTO_INDEX_SQL_ACTIONS –显示在SQL上执行的验证自动索引的操作

DBMS_AUTO_INDEX.CONFIGURE包相关参数
AUTO_INDEX_DEFAULT_TABLESPACE –指定自动索引创建所存储的表空间
AUTO_INDEX_MODE –指定自动索引的模式(开关),当前3个值,默认OFF,表示特性关闭;IMPLEMENT表示自动创建创建、测试、并报告,最终索引是visible状态; REPORT ONLY 会创建索引但是invisible,不会影响SQL,只是意图生成报告。
AUTO_INDEX_REPORT_RETENTION –自动索引报告历史保留的天数 默认31天
AUTO_INDEX_RETENTION_FOR_AUTO — 自动创建的索引从上次使用后多少天不再使用的索引可以删除,默认373天
AUTO_INDEX_RETENTION_FOR_MANUAL — 手动创建的索引从上次使用后多少天不再使用的索引可以删除,默认永远
AUTO_INDEX_SPACE_BUDGET — 自动索引可以使用表空间大小的百分比,默认 50%

Automatic Indexing工作原理
索引管理后台进程TASK调用,可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。
也是基于传统手动优化SQL的思路,基于SQL中的列使用识别可以创建的索引,然后验证自动索引对性能的影响,然后按预设的值去创建索引,只不过整个过程是自动的,并且整个过程都有审核报告。

Automatic Indexing测试体验
1,查看Oracle数据库版本
[oracle19@source ~]$ sqlplus / as sysdba

Version 19.3.0.0.0

Copyright ? 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 banner_full from v$version;

BANNER_FULL
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select * from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY

AUTO_INDEX_DEFAULT_TABLESPACE

AUTO_INDEX_MODE OFF —并未启用该参数

AUTO_INDEX_REPORT_RETENTION 31

AUTO_INDEX_RETENTION_FOR_AUTO 373

AUTO_INDEX_RETENTION_FOR_MANUAL

AUTO_INDEX_SCHEMA

AUTO_INDEX_SPACE_BUDGET 50

2,开启该特性,可以在CDB也可以在PDB
12:29:43 SYS@ludadbhost(source)> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
1
2 PDB$SEED READ ONLY NO
3 ludadbhostPDB READ WRITE NO
1
2
12:29:58 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

12:30:29 SYS@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);
BEGIN DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’); END;

ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_AUTO_INDEX_INTERNAL”, line 9180
ORA-06512: at “SYS.DBMS_AUTO_INDEX”, line 283
ORA-06512: at line 1
通过MOS查询需要开启一个隐患参数并重启数据库服务
12:33:45 SYS@ludadbhost(source)> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

12:33:48 SYS@ludadbhost(source)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:34:22 SYS@ludadbhost(source)> startup
ORACLE instance started.

Total System Global Area 2147483552 bytes
Fixed Size 9146272 bytes
Variable Size 1090519040 bytes
Database Buffers 1023410176 bytes
Redo Buffers 24408064 bytes
Database mounted.
Database opened.
12:34:47 SYS@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);

PL/SQL procedure successfully completed. —在PDB中进行完成
12:36:12 SYS@ludadbhost(source)> col PARAMETER_VALUE for a20
12:36:19 SYS@ludadbhost(source)> /

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED

AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT 07-JAN-20 12.35.14.000000 PM
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50

8 rows selected.
3,创建用户以及对应数据表空间
10:55:52 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

10:55:59 SYS@ludadbhost(source)> select file_name from dba_data_files;

/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/system01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/sysaux01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/undotbs01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/users01.dbf

10:56:16 SYS@ludadbhost(source)> CREATE TABLESPACEluda_data DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_data01.dbf’ SIZE 5G AUTOEXTEND Off;
Tablespace created.

10:58:33 SYS@ludadbhost(source)> 10:58:33 SYS@ludadbhost(source)> CREATE TABLESPACEluda_idx DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_idx01.dbf’ SIZE 2G AUTOEXTEND Off;
Tablespace created.

10:58:45 SYS@ludadbhost(source)> 10:58:45 SYS@ludadbhost(source)> create userluda IDENTIFIED BYluda ACCOUNT UNLOCK DEFAULT TABLESPACEluda_data TEMPORARY TABLESPACE TEMP;
User created.

10:58:55 SYS@ludadbhost(source)> grant connect,resource toluda;
Grant succeeded.

10:59:00 SYS@ludadbhost(source)> grant select any table toluda;
Grant succeeded.

4,创建数据
11:37:34luda@ludadbhost(source)> create table index_test as select * from dba_objects;

Table created.

11:37:47luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
72406

11:38:50luda@ludadbhost(source)> insert into index_test select * from index_test;

72406 rows created.

11:39:03luda@ludadbhost(source)> insert into index_test select * from index_test;

144812 rows created.

11:39:07luda@ludadbhost(source)> insert into index_test select * from index_test;

289624 rows created.

11:39:09luda@ludadbhost(source)> insert into index_test select * from index_test;

579248 rows created.

11:39:13luda@ludadbhost(source)> insert into index_test select * from index_test;

1158496 rows created.

11:39:16luda@ludadbhost(source)> insert into index_test select * from index_test;

2316992 rows created.

11:39:22luda@ludadbhost(source)> insert into index_test select * from index_test;

4633984 rows created.

11:39:33luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
9267968
11:39:45luda@ludadbhost(source)> insert into index_test select * from index_test;

9267968 rows created.

11:40:13luda@ludadbhost(source)> 11:40:13luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
18535936

11:41:49luda@ludadbhost(source)> update index_test set object_id=rownum;

18535936 rows updated.

11:45:06luda@ludadbhost(source)> commit;

5,模拟日常查询并查看执行计划
13:59:06luda@ludadbhost(source)> select object_type from index_test where object_id=5555;

OBJECT_TYPE
VIEW

13:59:21luda@ludadbhost(source)> explain plan for SELECT OBJECT_NAME FROM INDEX_TEST WHERE OBJECT_ID=1;

Explained.

13:59:35luda@ludadbhost(source)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 356488860

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 40 | 392 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_TEST | 1 | 40 | 392 (1)| 00:00:01 |

Predicate Information (identified by operation id):

1 – filter(“OBJECT_ID”=1)

13 rows selected.
该访问路径是全表扫描的方式;我们多次执行以下语句,并静等15分钟查看相关试图是否有结果
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=5559;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=50;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=51;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=52;
14:05:03luda@ludadbhost(source)> select created from index_test where object_id=345;
15:38:33luda@ludadbhost(source)> select * from DBA_AUTO_INDEX_EXECUTIONS;

15:55:34luda@ludadbhost(source)> select index_name,table_name,command,statement from DBA_AUTO_INDEX_IND_ACTIONS where execution_name=‘SYS_AI_2020-01-07/15:40:57’ order by action_id;

INDEX_NAME TABLE_NAME COMMAND STATEMENT

SYS_AI_66825yg9wksv1 INDEX_TEST CREATE INDEX CREATE INDEX “LUDA”.“SYS_AI_66825yg9wksv1” ON “LUDA”.“INDEX_TEST”(“OBJECT_ID”) T
SYS_AI_66825yg9wksv1 INDEX_TEST REBUILD INDEX ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” REBUILD ONLINE
SYS_AI_66825yg9wksv1 INDEX_TEST ALTER INDEX VISIBLE ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” VISIBLE
分了三步,第一创建一个索引,第二步设置online属性,第三步设置为可见状态;

6,检查该SYS_AI索引是否真实存在
15:56:43luda@ludadbhost(source)> explain plan for select object_name from index_test where object_id=1;

案例参考csdn

Oracle 19c Automatic indexing

Oracle 19c 新特性一览
可用性
一般
简化了 DG Broker中对于数据库参数的管理
动态修改Fast-Start Failover (FSFO)目标库
Broker的FSFO支持仅观察模式
当主库闪回时,备库也会跟着闪回
将主库还原点传播
DG多实例REDO应用支持IM
ADG中DML重定向
PDB支持恢复目录
定期清除闪回日志以提高FRA大小的可预估
DG中引入新的参数用于调整自动解决中断方案
更细粒度的补充日志
分片
跨分片传播参数值值
同一个CDB中支持多个PDB分片
System-Managed Sharding支持多表家族
支持在备用分片目录数据库上执行多分片查询
跨分片生成唯一的序列
大数据和数据仓库
一般
提升SQL诊断和修复能力
自动索引
基于Bitmap的count distinct的SQL函数
大数据和In-Memory外部表性能提升
自动解决SQL计划回归
实时统计信息
高频率的优化器统计信息收集
混合分区表
数据库总体新特性
自动化安装、配置和打补丁
DBCA静默方式复制一个数据库
DBCA静默方式克隆一个远端PDB
DBCA静默方式将一个PDB迁移到另一个CDB中
简化基于镜像的Oracle客户端安装
安装Oracle数据库root脚本支持自动执行
支持Oracle集群升级的干运行验证(Dry-Run Validation)。
自动化升级、迁移和工具
数据泵在导入时支持排除加密字句
数据泵在TTS导入过程中,允许表空间保持只读
数据泵中传输表空间的测试模式
数据泵支持资源限制
一般新特性
数据泵命令行参数:ENABLE_SECURE_ROLES
Data Pump Import supports wildcard dump file names for URL-based dump files maintained in object stores
Data Pump command-line parameter CREDENTIAL allows Import from object stores
性能
一般新特性
SQL隔离
为IM自动启用资源管理
在填充时,IM会等待
Memoptimized Rowstore – Fast Ingest
Automatic Database Diagnostic Monitor (ADDM)支持PDB
实时SQL监控
PDB中负载捕捉和重放
RAC和GRID
一般新特性
奇偶校验保护文件–Parity Protected Files
自动化PDB迁移
Automated Transaction Draining for Oracle Grid Infrastructure Upgrades
Oracle 支持重新升级和打补丁
Oracle Grid支持零停机打补丁
安全
一般新特性
ALTER SYSTEM命令中新的字句 FLUSH PASSWORDFILE_METADATA_CACHE
在非OMF模式下透明在线转换支持自动重命名
Key Management of Encrypted Oracle-Managed Tablespaces in Transparent Data Encryption
支持离线表空间加密的附加算法
Support for Host Name-Based Partial DN Matching for Host Certificates
Privilege Analysis Now Available in Oracle Database Enterprise Edition
Support for Oracle Native Encryption and SSL Authentication for Different Users Concurrently
能够从仅模式帐户授予或撤消管理权限
Automatic Support for Both SASL and Non-SASL Active Directory Connections
统一审计TOP语句
从Oracle数据库帐户中删除的密码
Signature-Based Security for LOB Locators
New EVENT_TIMESTAMP_UTC Column in the UNIFIED_AUDIT_TRAIL View
New PDB_GUID Audit Record Field for SYSLOG and the Windows Event Viewer
Database Vault Operations Control for Infrastructure Database Administrators
Database Vault Command Rule Support for Unified Audit Policies
可用性
一般
简化了 DG Broker中对于数据库参数的管理
用户可以通过ALTER SYSTEM命令或DGMGRL中EDIT DATABASE … SET PARAMETER命令来管理,设置所有DG相关的参数。而且 可以通过ALL来一次性修改所有DG环境中的数据库某个参数的,而不用一个一个去修改。

动态修改Fast-Start Failover (FSFO)目标库
目前,DBA必须禁用Fast-Start Failover (FSFO)才能更改FSFO目标备用数据库。 从19c开始,令允许用户动态地将FSFO目标备用数据库更改为目标列表中的另一个备用数据库,而无需先禁用FSFO。

Broker的FSFO支持仅观察模式
当数据库管理员配置DG Broker的FSFO功能时,现在可以将其配置为仅观察模式用来创建测试模式,以查看在正常生产处理期间何时发生故障转移或其他交互。这允许用户更精确地调整FSFO参数属性,并发现其环境中的哪些情况会导致自动故障转移发生。这样可以更容易地证明使用自动故障转移来减少故障转移的恢复时间。

此配置允许用户测试自动故障转移配置,而不会对生产数据库产生任何实际影响。这改进了Broker中已存在的现有故障转移验证,并帮助用户更轻松地了解FSFO自动故障转移过程。

当主库闪回时,备库也会跟着闪回
闪回数据库将整个数据库移动到较旧的时间点,并使用RESETLOGS打开数据库。在DG中,如果主数据库闪回,则备库不再与主库同步。在以前的版本中,需要将备库设置为与主库相同的时间点需要手动过程来闪回备用数据库。 19c中引入了一个新参数,该参数使备库能够在主库上执行闪回数据库时自动闪回。

通过在主数据库闪回时自动闪回备用数据库,减少了时间,精力和人为错误,从而实现更快的同步和缩短的恢复时间目标(RTO)。

将主库还原点传播
在此之前,在主库上定义正常还原点或保证还原点,以便在出现任何逻辑损坏问题时实现快速时间点恢复。 但是,此还原点存储在控制文件中,不会传播到备库。如果发生故障转移,备库成为主库,并且还原点信息将丢失。 而这个新特性可确保还原点从主库传播到备库,以便即使在故障转移事件后还原点也可用。

DG多实例REDO应用支持IM
在此之前,多实例REDO应用和IM列式存储不能同时启用。从19c开始,可以同时启用。

ADG中DML重定向
ADG DML重定向允许在ADG备库上执行DML。执行DML时,该操作将传递到它相关的主库上执行,并且事务的REDO将应用到备库。简而言之,就是

PDB支持恢复目录
支持可插拔数据库(PDB)作为目标数据库,并且可以使用虚拟专用目录(VPC)用户更精细地控制在PDB级别执行备份和还原操作的权限。 元数据视图也是有限的,因此VPC用户只能查看用户已被授予权限的数据。 在以前的版本中,不支持在目标数据库是PDB时与恢复目录的连接。

Oracle 19c为容器数据库(CDB)和PDB级备份和还原提供了完整的备份和恢复灵活性,包括恢复目录支持。

定期清除闪回日志以提高FRA大小的可预估
当拥有许多都使用快速恢复区(FRA)的数据库。 他们通常使用recovery_dest_size初始化参数设置FRA。 而当需要足够的FRA空间时,闪回日志是不会被清除的,这样就会造成FRA压力。 在许多情况下,唯一的补救措施是关闭闪回日志记录并将其重新打开。 而在19c中,此功能使闪回空间的使用从存储管理角度变得可预测,因为闪回不会占用保留所需的空间。此功能还允许用户通过调整闪回日志保留时间来控制空间压力。

FRA对数据库至关重要,因为它存储备份,联机重做日志,归档重做日志和闪回日志。当FRA空间使用满了,会影响数据库的正常使用,后果非常严重。

DG中引入新的参数用于调整自动解决中断方案
DG在主库和备库上有多个进程,用于处理重做传输和归档,这些进程通过网络相互通信。在某些故障情况下,网络挂起,断开连接和磁盘I/O问题,这些进程可能会挂起,可能导致重做传输和GAP解决的延迟。 DG有一个内部机制来检测这些挂起的进程并终止它们,从而允许正常的中断解决方案发生。 在Oracle 19c中,DBA可以使用两个新参数DATA_GUARD_MAX_IO_TIME和DATA_GUARD_MAX_LONGIO_TIME来调整此检测周期的等待时间。 这些参数允许根据用户网络和磁盘I/O行为调整特定DG配置的等待时间。

更细粒度的补充日志
为逻辑备用或完整数据库复制要求设计并实现了补充日志记录。这会在仅复制表的子集的环境中增加不必要的开销。细粒度的补充日志记录为部分数据库复制用户提供了一种方法,可以禁用不感兴趣的表的补充日志记录,这样即使在数据库或模式级别启用了补充日志记录,也不会为不感兴趣的表提供补充日志记录开销。

使用此功能可以显着减少资源使用和重做生成方面的开销,以防数据库中只有部分表需要补充日志记录,例如在GoldenGate部分复制配置中。

分片
跨分片传播参数值值
在19c之前,DBA不得不一个一个去修改每个分片的参数值。而从19c开始,只需要在分片catalog数据库上执行即可。

同一个CDB中支持多个PDB分片
在此之前,只支持同一个CDB中一个PDB作为分片。当然还是有一些限制,如:该CDB中的不同PDB必须是不同分片数据库中的分片。

System-Managed Sharding支持多表家族
在此之前,不管什么方式的Sharding支持一个表家族。

支持在备用分片目录数据库上执行多分片查询
在此之前,只能在主分片目录数据库上支持。

跨分片生成唯一的序列
在此之前,只能通过手动方式来保证序列的唯一性在所有分片数据库上。从19c开始,这一切交给Oracle就可以了。

大数据和数据仓库
一般
提升SQL诊断和修复能力
SQL诊断和修复工具(如SQL Test Case Builder和SQL Repair Advisor)已得到增强,可为管理有问题的SQL语句提供更好的诊断和修复功能。

自动索引
自动索引功能可自动执行索引管理任务,例如根据应用程序工作负载的变化在Oracle数据库中创建,重建和删除索引。

基于Bitmap的count distinct的SQL函数
在12c中就引入了count distinct,用于粗略统计一列不同值的个数。在19c中持续增强,性能和准确性大大提高。

大数据和In-Memory外部表性能提升
IM外部表添加了对ORACLE_HIVE和ORACLE_BIGDATA驱动程序,并行查询,RAC,DG和按需填充的支持。

自动解决SQL计划回归
SQL计划管理在AWR中搜索SQL语句。通过最高负载确定优先级,它在所有可用源中查找备用计划,为SQL计划基准添加性能更好的计划。 Oracle数据库还提供计划比较工具和改进的提示报告。

实时统计信息
Oracle将在DML执行期间,自动收集统计信息。

高频率的优化器统计信息收集
用户可以为某些对象指定更高频率的统计信息收集。从而达到,有力生成更准确的执行计划。。。

混合分区表
混合分区表功能通过使分区驻留在Oracle数据库段以及外部文件和源中来扩展Oracle分区。此功能显着增强了大数据SQL的分区功能,其中表的大部分可以驻留在外部分区中。

数据库总体新特性
自动化安装、配置和打补丁
DBCA静默方式复制一个数据库
可通过DBCA中createDuplicateDB命令来复制一个数据库。

DBCA静默方式克隆一个远端PDB
可通过DBCA中createFromRemotePDB命令来克隆PDB。

DBCA静默方式将一个PDB迁移到另一个CDB中
可通过DBCA中relocatePDB命令来迁移PDB。

简化基于镜像的Oracle客户端安装
从Oracle Database 19c开始,Oracle Database Client软件可用作下载和安装的映像文件。 您必须将映像软件解压缩到您希望Oracle主目录所在的目录中,然后运行runInstaller脚本以启动Oracle Database Client安装。 当然依然提供二进制文件继续以传统格式提供为non-zip文件。

安装Oracle数据库root脚本支持自动执行
从Oracle Database 19c开始,数据库安装程序或设置向导提供了一些选项,用于设置在数据库安装期间根据需要自动运行根配置脚本的权限。 您可以继续手动运行根配置脚本。

支持Oracle集群升级的干运行验证(Dry-Run Validation)。
从19c开始,支持以干运行方式(模拟升级)来验证是否满足升级要求,而不是真正的升级。

自动化升级、迁移和工具
数据泵在导入时支持排除加密字句
可通过新的参数OMIT_ENCRYPTION_CLAUSE来忽略具有加密列的对象。

数据泵在TTS导入过程中,允许表空间保持只读
就是在TTS过程中,可以在源库和目标库上都可以降表空间至于只读模式,可以提供读的服务。而不是之前的只能保证源库的只读,目标库表空间无法正常使用。

数据泵中传输表空间的测试模式
可传输表空间的测试模式使用可传输表空间或完全可传输导出/导入执行仅元数据导出测试。 它还消除了源数据库表空间处于只读模式的要求。

现在,DBA可以更轻松地确定导出所需的时间,并发现闭包检查未报告的无法预料的问题。

数据泵支持资源限制
在数据泵的导出、导入过程中,可以限制其资源的使用。可通过两个新参数来实现:MAX_DATAPUMP_JOBS_PER_PDB 和 MAX_DATAPUMP_PARALLEL_PER_JOB。

一般新特性
数据泵命令行参数:ENABLE_SECURE_ROLES
默认情况下,Data Pump不再启用受密码保护的安全角色。从19c开始,您必须为单个导出或导入作业显启用受密码保护的角色。 添加了一个新的命令行参数,ENABLE_SECURE_ROLES =YES|NO,可用于为单个导出
或导入作业显式启用或禁用这些类型的角色。

Data Pump Import supports wildcard dump file names for URL-based dump files maintained in object stores
Data Pump command-line parameter CREDENTIAL allows Import from object stores
性能
一般新特性
SQL隔离
由于过度消耗CPU和I/O资源而由Oracle资源管理器终止的SQL语句可以自动隔离。与终止的SQL语句关联的执行计划将被隔离,以防止它们再次执行。

为IM自动启用资源管理
当INMEMORY_SIZE不为0时,那么资源管理器将被自动启用。

在填充时,IM会等待
DBMS_INMEMORY_ADMIN.POPULATE_WAIT新函数会让对象一直处于等待状态无法被访问,直到指定优先级的对象已填充到指定的百分比。

新函数确保在允许应用程序访问之前已填充指定的In-Memory对象。例如,数据库可能包含许多具有各种优先级设置的内存中表。 在受限会话中,您可以使用POPULATE_WAIT函数来确保完全填充每个In-Memory表。之后,您可以禁用受限会话,以确保应用程序仅查询表的In-Memory中表示。

Memoptimized Rowstore – Fast Ingest
Automatic Database Diagnostic Monitor (ADDM)支持PDB
实时SQL监控
PDB中负载捕捉和重放
在此之前,只能在CDB root容器级别捕获负载和重放。从19c开始,支持PDB级别。

RAC和GRID
一般新特性
奇偶校验保护文件–Parity Protected Files
REDUNDANCY文件类型属性指定文件组的冗余。 PARITY值指定冗余的单奇偶校验。 奇偶校验适用于一次写入文件,例如存档日志和备份集。

传统的两个或三个ASM镜像用于与数据库备份操作相关联的文件时,会消耗大量空间。 备份文件是一次写入文件,此功能允许保护奇偶校验而不是传统镜像。这样可以节省大量空间。

自动化PDB迁移
在Oracle Grid中,可以使用Fleet Patching和Provisioning自动将PDB从一个CDB重定位到另一个CDB。

Automated Transaction Draining for Oracle Grid Infrastructure Upgrades
Oracle 支持重新升级和打补丁
使用Fleet Patching和Provisioning来打补丁和升级Oracle Restart。 在以前的版本中,Oracle Restart
环境要求用户执行修补和升级操作,通常需要手动干预。Fleet Patching和Provisioning自动执行这些
过程。

Oracle Grid支持零停机打补丁
安全
一般新特性
ALTER SYSTEM命令中新的字句 FLUSH PASSWORDFILE_METADATA_CACHE
ALTER SYSTEM命令中新的子句FLUSH PASSWORDFILE_METADATA_CACHE使用数据库密码文件的最新详细信息刷新元数据缓存。可以通过查询V $ PASSWORDFILE_INFO视图来检索数据库密码文件的最新详细信息。

更改数据库密码文件名或位置时,此功能非常有用,并且需要使用更新的数据库密码文件的详细信息刷新元数据缓存。

在非OMF模式下透明在线转换支持自动重命名
从19c开始,在非OMF模式下的透明数据加密联机转换中,不再需要在ADMINISTER KEY MANAGEMENT SQL语句中包含FILE_NAME_CONVERT子句。 文件名保留其原始名称。

Key Management of Encrypted Oracle-Managed Tablespaces in Transparent Data Encryption
支持离线表空间加密的附加算法
Support for Host Name-Based Partial DN Matching for Host Certificates
Privilege Analysis Now Available in Oracle Database Enterprise Edition
Support for Oracle Native Encryption and SSL Authentication for Different Users Concurrently
能够从仅模式帐户授予或撤消管理权限
Automatic Support for Both SASL and Non-SASL Active Directory Connections
统一审计TOP语句
从Oracle数据库帐户中删除的密码
Signature-Based Security for LOB Locators
New EVENT_TIMESTAMP_UTC Column in the UNIFIED_AUDIT_TRAIL View
New PDB_GUID Audit Record Field for SYSLOG and the Windows Event Viewer
Database Vault Operations Control for Infrastructure Database Administrators
Database Vault Command Rule Support for Unified Audit Policies

https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-06A15128-1172-48E5-8493-CD670B9E57DC

Oracle 19c 新特性一览

19c的重要特性之一DML重定向,原理发送到ADG备库上的DML操作,自动转发到主库执行,然后通过主库日志传递到备库实时应用,在保证了ACID的前提下,增强了备库的功能性

在 18c 中,这个特性是否启用通过隐含参数 _enable_proxy_adg_redirect 控制;

在 19c 中,则由显式参数 ADG_REDIRECT_DML控制;

SQL> show parameter ADG_REDIRECT_DML

NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean FALSE

SQL> alter system set adg_redirect_dml=true scope=both;

System altered.

SQL> create user test identified by test;
create user test identified by test
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

SQL> create table test(id int,sdate char(20));
create table test(id int,sdate char(20))
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

SQL> conn luda/luda
Connected.
SQL> show parameter ADG_REDIRECT_DML

NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean TRUE
SQL>
alter session set events ‘10046 trace name context forever ,level 12′;

insert into test values (1, to_char(current_timestamp at time zone dbtimezone,’dd-mon-rr hh:mi:ss’));

alter session set events ‘10046 trace name context off’;

select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;

SID PID
———- ———-
TRACEFILE
——————————————————————————–
223 44
/u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc

格式化输出

tkprof /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc /home/oracle/orcl_ora_1768553.out

[oracle@oracle19c ~]$ cat orcl_ora_1768553.out

TKPROF: Release 19.0.0.0.0 – Development on Mon Apr 27 07:56:09 2020

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

Trace file: /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

insert into test values (1, to_char(current_timestamp at time zone dbtimezone,’dd-mon-rr hh:mi:ss’))

Error encountered: ORA-01157
********************************************************************************

SQL ID: 8r8b2pyy9qjm2 Plan Hash: 0

alter session set events ‘10046 trace name context forever, level 12’

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 26.12 47.13
SQL*Net break/reset to client 3 0.00 0.00
single-task message 1 0.01 0.01
SQL*Net message from dblink 7 0.01 0.02
SQL*Net message to dblink 6 0.00 0.00
SQL*Net vector data to dblink 1 0.00 0.00
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
control file sequential read 8 0.00 0.00
********************************************************************************

SQL ID: 06nvwn223659v Plan Hash: 0

alter session set events ‘10046 trace name context off’

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106

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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 26.12 47.13
SQL*Net break/reset to client 3 0.00 0.00
single-task message 1 0.01 0.01
SQL*Net message from dblink 7 0.01 0.02
SQL*Net message to dblink 6 0.00 0.00
SQL*Net vector data to dblink 1 0.00 0.00
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
control file sequential read 8 0.00 0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
——- —— ——– ———- ———- ———- ———- ———-
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc
Trace file compatibility: 12.2.0.0
Sort options: default

1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
88 lines in trace file.
47 elapsed seconds in trace file.

Oracle 19C ADG的自动DML重定向 ADG_REDIRECT_DML

RDA Remote Diagnostic Agent

RDA Remote Diagnostic Agent远程诊断代理是Oracle Support售后服务使用的标准工具之一,当用户在Metalink上提交SR(TAR)时可能Oracle GCS(Global Customer Service)支持会需要让用户从MOS上下载RDA工具,通过RDA收集丰富的数据库环境信息(如包含OS、DB、CRS等),以便原厂售后直接从RDA report中抓取诊断信息,避免了因诊断信息不足 而反复信息交互所浪费的时间 ; 此外Oracle的一些ACS高级客户服务的现场服务过程中也会利用到RDA,例如当用户要求ACS到现场进行月度或季度巡检是RDA就是标准的检查工具。

 

说具体参考My Oracle Support(metalink)的《Remote Diagnostic Agent (RDA) 4 – Getting Started [ID 314422.1]》专栏中下载到各个平台的最新版RDA。

首先配置就是选择我们要使用的RDA module和一些临时设置,先来认识一下有哪些module:

 

将下载到的rda zip包解压

[oracle@vrh8 ~]$ unzip /tmp/p9079828_418_LINUX.zip 

[oracle@vrh8 ~]$ cd rda

[oracle@vrh8 rda]$ ./rda.sh -h
Usage: rda.pl [-bcdflntvwxy] [-ABCDEHIKLMPQRSTV] [-e list] [-m dir]
              [-s name] [-o out] [-p prof] arg ...
        -A      Authentify user through the setup file
        -B      Start background collection
        -C      Collect diagnostic information
        -D      Delete specified modules from the setup
        -E      Explain specified error numbers
        -H      Halt background collection
        -I      Regenerate the index
        -K      Kill background collection
        -L      List the modules available
        -M      Display the related manual pages
        -O      Render output specifications from STDIN
        -P      Package the reports (tar or zip)
        -Q      Display the related setup questions
        -R      Generate specified reports
        -S      Setup specified modules
        -T      Execute test modules
        -V      Display component version numbers
        -b      Don't backup setup file before saving
        -c      Check the RDA installation and exit
        -d      Set debug mode
        -e list Specify a list of alternate setting definitions (var=val,...)
        -f      Set force mode
        -h      Display the command usage and exit
        -l      Use a lock file to prevent concurrent usage of a setup file
        -m dir  Specify the module directory ('modules' by default)
        -n      Start a new data collection
        -o out  Specify the file for background collection output redirection
        -p prof Specify the setup profile ('Default' by default)
        -q      Set quiet mode
        -s name Specify the setup name ('setup' by default)
        -t      Set trace mode
        -v      Set verbose mode
        -w      Wait as long as the background collection daemon is active
        -x      Produce module cross reference
        -y      Accept all defaults and skip all pauses

列出所有可用module

[oracle@vrh8 rda]$ ./rda.sh -L Module

Available data collection modules are:
  ACFS     Collects ASM Cluster File System Information
  ACT      Collects Oracle E-Business Suite Application Information
  ADBA     Collects ACS Oracle Database Assessment
  ADX      Collects AutoConfig and Rapid Clone Information
  AGT      Collects Enterprise Manager Agent Information
  APEX     Collects APEX Information
  ASAP     Collects Oracle Communications ASAP Information
  ASBR     Collects Application Server Backup and Recovery Information
  ASG      Collects Application Server Guard Information
  ASIT     Collects Oracle Application Server Installation Information
  ASM      Collects Automatic Storage Management Information
  B2B      Collects Oracle Business to Business Information
  BAM      Collects Business Activity Monitoring Information
  BEE      Collects Beehive Information
  BI       Collects Oracle Business Intelligence Enterprise Edition Info.
  BPEL     Collects Oracle BPEL Process Manager Information
  BR       Collects Database Backup and Recovery Information
  BRM      Collects Oracle Communications BRM Information
  CCR      Collects OCM Diagnostic Information
  CFG      Collects Key Configuration Information
  COHR     Collects Oracle Coherence Information
  CONT     Collects Oracle Content Services Information
  CRID     Collects Oracle Access Manager (COREid) Information
  D2PC     Collects Distributed Transaction Information
  DB       Controls RDBMS Data Collection
  DBA      Collects RDBMS Information
  DBC      Collects Database Control Information
  DBM      Collects RDBMS Memory Information
  DEV      Collects Oracle Developer Information
  DG       Collects Data Guard Information
  DNFS     Collects Direct NFS Information
  DSCS     Collects Discussions Information
  DSCV     Collects Oracle Discoverer Information
  ECM      Controls Oracle Enterprise Content Management 11g Data Collection
  EM       Collects Enterprise Manager OMS and Repository Info (Obsolete)
  END      Finalizes the Data Collection
  EPMA     Collects Enterprise Performance Management Architect Information
  ESB      Collects Enterprise Service Bus Information
  ESS      Collects Oracle Essbase Information
  ESSO     Collects Oracle Enterprise Single Sign-On Information
  EXA      Collects Exadata Information
  FLTR     Controls Report Content Filtering
  GRDN     Collects Oracle Guardian Information
  GRID     Controls Grid Control Data Collection
  GTW      Collects Transparent/Procedural Gateway Information
  HFM      Collects Oracle Hyperion Financial Management information
  HPL      Collects Oracle Hyperion Planning Information
  IA       Collects Intelligent Agent Information
  IAS      Collects Web Server Information
  IFS      Collects iFS (iFS, CMSDK, Files) Information
  INI      Initializes the Data Collection
  INST     Collects the Oracle Installation Information
  IPSA     Collects Oracle Communications IP Service Activator Information
  J2EE     Collects J2EE/OC4J Information
  JDBC     Collects Oracle Java DB Connectivity (JDBC) Information
  JDEV     Collects Oracle JDeveloper Information
  JIVE     Collects Jive Information
  LANG     Collects Oracle Language Information
  LOAD     Produces the External Collection Reports
  LOG      Collects Database Trace and Log Files
  MAIL     Collects Oracle Collaboration Suite Mail Information
  MSLG     Collects Microsoft Languages Information
  ND       Collects Oracle Communications Network Discovery Information
  NET      Collects Network Information
  NM       Collects Oracle Communications Network Mediation Information
  NPRF     Samples Performance Information (root not required)
  OCAL     Collects Oracle Calendar Information
  OCFS     Collects Oracle Cluster File System Information
  OCM      Setting up Configuration Manager Interface
  OCS      Controls Oracle Collaboration Suite Data Collection
  ODI      Collects Oracle Data Integrator Information
  ODM      Collects Oracle Data Mining Information
  OES      Collects Oracle Express Server Information
  OID      Collects Oracle Internet Directory Information
  OIM      Collects Oracle Identity Manager Information
  OLAP     Collects OLAP Information
  OMM      Collects Oracle Multimedia or Oracle interMedia Information
  OMS      Collects Oracle Management Server Information (obsolete)
  ONET     Collects Oracle Net Information
  OS       Collects the Operating System Information
  OVD      Collects Oracle Virtual Directory Information
  OVMM     Collects Oracle VM Manager Information
  OVMS     Collects Oracle VM Server Information
  OWB      Collects Oracle Warehouse Builder Information
  OWSM     Collects Oracle Web Services Manager Information
  PDA      Collects Oracle Portal Information
  PDBA     Collects PeopleSoft Information from an Oracle Database
  PERF     Collects Performance Information
  PLNC     Collects Oracle PL/SQL Native Compilation Information
  PROF     Collects the User Profile
  PS       Collects Oracle Communications Policy Services Information
  PWEB     Collects PeopleSoft Information from Web Application Server
  RAC      Collects Cluster Information
  RACD     Performs a Database Hang Analysis
  RDSP     Produces the Remote Data Collection Reports
  RET      Collects Oracle Retail Information
  REXE     Performs the Remote Data Collections
  RPRF     Samples Performance Information (root privileges required)
  RSRC     Collects Database Resource Manager Information
  RTC      Collects Real Time Communication Information
  SEBL     Collects Siebel Information
  SES      Collects Oracle Secure Enterprise Search Information
  SMPL     Controls Sampling
  SOA      Collects Oracle SOA Suite Information
  SP       Collects SQL*Plus/iSQL*Plus Information
  SSO      Collects Single Sign-On Information
  STC      Collects Streams Configuration Information
  STM      Collects Streams Monitoring Information
  TOPL     Collects Oracle TopLink Information
  TTEN     Collects Oracle TimesTen In-Memory Database Information
  UCM      Collects Oracle Universal Content Management Information
  UOA      Collects Oracle Universal Online Archive 11g Information
  WAC      Collects Web Access Client Information
  WCI      Collects Oracle WebCenter Information
  WEBC     Collects Oracle Web Cache Information
  WKSP     Collects Workspaces Information
  WLS      Collects Oracle WebLogic Server Information
  WMC      Collects Webmail Client Information
  WRLS     Collects Wireless Information
  XDB      Collects XDB Information
  XSMP     Samples User Defined Data
  XTRA     Collects User Defined Data

 

 

如以上列表中RAC模块用来Collects Cluster Information收集集群信息, 而RACD模块则负责收集RAC数据库挂起的相关信息Performs a Database Hang Analysis。

 

我们在配置RDA的时可以直接执行./rda.sh, 脚本会提示我们需要选择启用哪些Module,但是因为Module过多,整个配置过程就会浪费很多时间。

 

为了避免每配置一套新环境都要大费周章确认那么多模块, 所以在RDA中定义了很多典型场景使用的profile, 这些profile已经配好了固定的一些Module , 下面我们来看一下RDA profile:

 

 

列出所有目前可用的profile

[oracle@vrh8 rda]$ ./rda.sh -L profiles
Available profiles are:
  9iAS               Oracle Application Server 9i problems
  AS10g              Oracle Application Server 10g problems
  AS10g_Identity     Oracle Identity Management 10g problems
  AS10g_MidTier      Oracle Application Server 10g Middle Tier problems
  AS10g_Repository   Oracle Application Server 10g metadata repository problems
  AS10g_WebTier      Oracle Application Server 10g WebTier problems
  AS_BackupRecovery  Oracle Application Server backup/recovery problems
  Act                Oracle Application Overview
  AppsCheck          Equivalent to AppsCheck
  AsmFileSystem      Oracle ASM Cluster File System problems
  Bam                Business Activity Monitoring problems
  Beehive            Oracle Beehive problems
  DB10g              Oracle Database 10g problems
  DB11g              Oracle Database 11g problems
  DB8i               Oracle Database 8i problems
  DB9i               Oracle Database 9i problems
  DB_Assessment      Oracle Database assessment collections
  DB_BackupRecovery  Oracle Database backup and recovery problems
  DB_Perf            Oracle Database performance problems
  DataGuard          Data Guard problems
  DirectNFS          Direct NFS problems
  Discoverer10g      Oracle Discoverer 10g problems
  Discoverer11g      Oracle Discoverer 11g problems
  EnterpriseSearch   Oracle Secure Enterprise Search problems
  Essbase            Oracle Essbase problems
  FM11g_Bi           Business Intelligence Enterprise Edition 11g problems
  FM11g_Ecm          Oracle Enterprise Content Management 11g problems
  FM11g_Forms        Oracle Forms 11g problems
  FM11g_Identity     Oracle Identity Management 11g problems
  FM11g_Odi          Oracle Data Integrator Standalone 11g problems
  FM11g_Portal       Oracle Portal 11g problems
  FM11g_Reports      Oracle Reports 11g problems
  FM11g_Soa          Oracle SOA Suite 11g problems
  FM11g_WebTier      Oracle Fusion Middleware 11g Web Tier problems
  FM11g_WlsBi        Business Intelligence Enterprise Edition 11g with WLS
  FM11g_WlsForms     Oracle Forms 11g with WLS problems
  FM11g_WlsIdentity  Oracle Identity Management 11g with WLS problems
  FM11g_WlsOdi       Oracle Data Integrator Suite 11g with WLS problems
  FM11g_WlsPortal    Oracle Portal 11g with WLS problems
  FM11g_WlsReports   Oracle Reports 11g with WLS problems
  FM11g_WlsWebTier   Oracle Fusion Middleware 11g Web Tier with WLS problems
  FinManagement      Oracle Hyperion Financial Management problems
  GridControl        Grid Control problems
  InterMedia         Oracle interMedia problems
  Linux              Linux problems
  LinuxPerf          Linux performance problems
  Maa_Assessment     Maximum Availability Architecture assessment collections
  Multimedia         Oracle Multimedia problems
  OSMonitor          Operating System performance sampling
  OVMManager         Oracle VM Manager problems
  Pda10g             Portal 10g problems
  Pda11g             Portal 11g problems
  Pda9i              Portal 9i problems
  PeopleSoft_DB      PeopleSoft Oracle Database tier assessment collections
  PeopleSoft_Web     PeopleSoft Web application server assessment collections
  Rac                Real Application Cluster problems
  Rac_AdvancedAsm    Cluster with ASM problems (ASM advanced mode)
  Rac_Asm            Cluster with ASM problems
  Rac_Assessment     Real Application Cluster assessment collections
  Rac_Perf           Cluster performance problems
  Retail             Oracle Retail problems
  Security           Filter sensitive information from the reports
  SupportInformer70  Oracle Communication BRM 7.0 problems
  SupportInformer72  Oracle Communication BRM 7.2 problems
  SupportInformer73  Oracle Communication BRM 7.3 problems
  SupportInformer74  Oracle Communication BRM 7.4 problems
  TimesTen           Oracle TimesTen problems
  TopLink10g         Oracle TopLink 10g problems
  WebCenter10g       Oracle WebCenter 10g problems
  WebCenter11g       Oracle WebCenter 11g problems
  WebCenterCont10g   Oracle WebCenter 10g with Oracle Content Services problems
  WebLogicServer     Oracle WebLogic Server problems

 

 

上例列出了该版本RDA默认就有的Profile , 如DB11g这个profile是用来收集11g Database数据库的诊断信息的, 而DB10g 是收集10g Database诊断信息的, DB_Perf是收集数据库性能诊断信息的。

可以具体了解 这些profile 预设了哪些Module:

 

 

[oracle@vrh8 rda]$ ./rda.sh -M -p DB11g
NAME
    Profile DB11g - Oracle Database 11g problems

MODULES
    The DB11g profile uses the following modules:
      OS        Collects the Operating System Information
      PROF      Collects the User Profile
      PERF      Collects Performance Information
      NET       Collects Network Information
      ONET      Collects Oracle Net Information
      INST      Collects the Oracle Installation Information
      DB        Controls RDBMS Data Collection
      DBA       Collects RDBMS Information
      DBM       Collects RDBMS Memory Information
      LOG       Collects Database Trace and Log Files
      DNFS      Collects Direct NFS Information
      SP        Collects SQL*Plus/iSQL*Plus Information
      GRID      Controls Grid Control Data Collection
      AGT       Collects Enterprise Manager Agent Information
      DBC       Collects Database Control Information

[oracle@vrh8 rda]$ ./rda.sh -M -p DB10g
NAME
    Profile DB10g - Oracle Database 10g problems

MODULES
    The DB10g profile uses the following modules:
      OS        Collects the Operating System Information
      PROF      Collects the User Profile
      PERF      Collects Performance Information
      NET       Collects Network Information
      ONET      Collects Oracle Net Information
      INST      Collects the Oracle Installation Information
      DB        Controls RDBMS Data Collection
      DBA       Collects RDBMS Information
      DBM       Collects RDBMS Memory Information
      LOG       Collects Database Trace and Log Files
      SP        Collects SQL*Plus/iSQL*Plus Information
      GRID      Controls Grid Control Data Collection
      AGT       Collects Enterprise Manager Agent Information
      DBC       Collects Database Control Information

 

 

除了module之外profile可能还定义了一些临时变量如force_onet_tests 是否强制做oracle net网络测试等, 可以用-f( Set force mode)选项来列出这些temporary settings:

 

 

[oracle@vrh8 rda]$ ./rda.sh -fM -p DB10g
NAME
    Profile DB10g - Oracle Database 10g problems

MODULES
    The DB10g profile uses the following modules:
      OS        Collects the Operating System Information
      PROF      Collects the User Profile
      PERF      Collects Performance Information
      NET       Collects Network Information
      ONET      Collects Oracle Net Information
      INST      Collects the Oracle Installation Information
      DB        Controls RDBMS Data Collection
      DBA       Collects RDBMS Information
      DBM       Collects RDBMS Memory Information
      LOG       Collects Database Trace and Log Files
      SP        Collects SQL*Plus/iSQL*Plus Information
      GRID      Controls Grid Control Data Collection
      AGT       Collects Enterprise Manager Agent Information
      DBC       Collects Database Control Information

SETTINGS
    The DB10g profile sets the following temporary settings:
      force_db_tests=1
      force_dba_tests=1
      force_dbm_tests=1
      force_log_tests=1
      force_onet_tests=1

 

 

也可以列出全部预定义的profile的Module信息:

 

 

[oracle@vrh8 rda]$ ./rda.sh -xv profiles
Treating profiles ...
Profile Cross Reference

Defined Profiles:
  9iAS               S100OS, S105PROF, S110PERF, S120NET, S130INST, S300IAS,
                     S305ASBR, S306ASG, S310J2EE, S330SSO, S340OID, S350WEBC
  AS10g              S100OS, S105PROF, S110PERF, S120NET, S130INST, S300IAS,
                     S305ASBR, S306ASG, S310J2EE, S330SSO, S340OID, S350WEBC
  AS10g_Identity     S100OS, S105PROF, S110PERF, S120NET, S130INST, S300IAS,
                     S305ASBR, S306ASG, S310J2EE, S330SSO, S340OID, S342OVD
  AS10g_MidTier      S100OS, S105PROF, S110PERF, S120NET, S130INST, S249WRLS,
                     S290DEV, S300IAS, S310J2EE, S325PDA, S350WEBC, S390DSCV
  AS10g_Repository   S100OS, S105PROF, S110PERF, S120NET, S130INST, S300IAS,
                     S305ASBR, S306ASG, S310J2EE
  AS10g_WebTier      S100OS, S105PROF, S110PERF, S120NET, S130INST, S300IAS,
                     S310J2EE, S350WEBC, S410GRID
  AS_BackupRecovery  S100OS, S300IAS, S305ASBR
  Act                S100OS, S105PROF, S110PERF, S130INST, S500ACT
  AppsCheck          S100OS, S105PROF, S110PERF, S130INST, S500ACT
  AsmFileSystem      S100OS, S105PROF, S120NET, S122ONET, S130INST, S402ASM,
                     S403ACFS
  Bam                S100OS, S105PROF, S110PERF, S120NET, S374BAM
..........

 

 

 

使用-Q 选项可以更详细地列出profile相关的问题:

 

 

[oracle@vrh8 rda]$ ./rda.sh -Q -p DB11g

NAME
    S120NET - Collects Network Information

SETTING DESCRIPTION
  NETWORK_PING_TESTS
    "Do you want RDA to perform the network ping tests (Y/N)?"

  LOCAL_NODE
    "Enter the name of the node the script is running on (used for ping
    tests)"

  WAN_NODE
    "Enter a remote node connecting to this server (used for ping tests)"

  RDBMS_NODE
    "Enter the node hosting the database instance (used for ping tests)"

  WEB_NODE
    "Enter the node where the Web Server/Forms server is on (used for ping
    tests)"

...............

 

 

通过继承profile的定义可以快速配置RDA,例如我们尝试使用DB11g这个profile:

 

 

[oracle@vrh8 rda]$ ./rda.sh -S -p DB11g

使用profile后RDA问你的问题明显减少了哦

之后在运行rda.sh就会收集信息了

[oracle@vrh8 rda]$ ./rda.sh
-------------------------------------------------------------------------------
RDA Data Collection Started 06-Feb-2012 01:23:22
-------------------------------------------------------------------------------
Processing Initialization module ...
Enter the password for 'SYSTEM':
Please re-enter it to confirm:
Processing OCM module ...
Processing PERF module ...
Processing CFG module ...
Processing OS module ..

 

 

可能你还是觉得麻烦:”我使用oracle操作系统用户登录的,为啥每次还要输密码呢?直接sysdba不行吗?” 对于有些内部视图X$ View的查询也确实需要sysdba权限, 我们可以如下设置来使用sysdba身份:

 

[oracle@vrh8 rda]$ ./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB11g

[oracle@vrh8 rda]$ ./rda.sh
-------------------------------------------------------------------------------
RDA Data Collection Started 06-Feb-2012 01:27:37
-------------------------------------------------------------------------------
Processing Initialization module ...
Processing OCM module ...
Processing PERF module ...
Processing CFG module ...
Processing OS module ...

 

 

除了单独使用某个profile外,我们还可以组合使用多个profile,使用-p profile1-profile2这样的语法即可,如:

 

[oracle@vrh8 rda]$ ./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB11g-DataGuard

使用-p profile1-profile2这样的模式即可

 

 

RDA经过配置后会默认将配置信息写到其目录下的setup.cfg文件中,之后再使用rda.sh收集信息即会沿用该cfg文件:

 

 

cat setup.cfg

# Oracle Remote Diagnostic Agent - Setup Information
###############################################################################

#------------------------------------------------------------------------------
# Data Collection Overview
#------------------------------------------------------------------------------
# S000INI=pending
# S010CFG=pending
# S020SMPL=pending
# S090OCM=pending
# S100OS=pending
# S105PROF=pending
# S110PERF=pending
# S120NET=pending
# S122ONET=pending
# S130INST=pending
# S200DB=skip
# S201DBA=pending
# S203DBM=pending
# S204LOG=pending
# S205BR=pending
# S212DNFS=skip
# S213SP=skip
# S400RAC=pending
# S400RACD=skip
# S401OCFS=skip
# S405DG=pending
# S410GRID=skip
# S420AGT=skip
# S430DBC=skip
# S909RDSP=skip
# S919LOAD=pending
# S990FLTR=skip
www.askmaclean.com
www.askmaclean.com
# S999END=pending
.................

 

 

除了最常见的DB11g 、 DB10g外还有一些很有用的profile可以加速我们对问题的诊断, 在这里分享一下:

 

 

For 11g

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB11g

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p DB11g
--收集alert文本

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1,DBCONTROL_SERVER_IN_USE=1 -p DB11g
--收集DBcontrol信息

./rda.sh -vSCRPfy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p DB11g
--收集诊断信息并打包

DB10g 

./rda.sh -S -p DB10g

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB10g

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,DBCONTROL_SERVER_IN_USE=1 -p DB10g

DB9i

./rda.sh -S -p DB9i

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB9i

DB_BackupRecovery 收集备份恢复信息

./rda.sh -S -p DB_BackupRecovery

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,RMAN_IN_USE=0 -p DB_BackupRecovery
--不使用RMAN备份

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,RMAN_IN_USE=1,RMAN_CATALOG=0 -p DB_BackupRecovery
--使用RMAN但不使用CATALOG恢复目录

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,RMAN_IN_USE=1,RMAN_CATALOG=1,RMAN_SCHEMA=rman,RMAN_EXPORT_USER=rman@catlogdb -p DB_BackupRecovery

DB_Perf 收集数据库性能信息

./rda.sh -S -p DB_Perf

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DB_Perf

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,PERF_START_TIME=11-Mar-2010_12:00,PERF_END_TIME=11-Mar-2010_13:00 -p DB_Perf
--指定收集性能信息的时间段 

DataGuard 收集dg 信息

./rda.sh -S -p DataGuard

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DataGuard

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ONET_IN_USE=1,ALERT_TEXT=1 -p DataGuard
--同时也收集Oracle Net Services信息

RAC 收集Real Application Cluster CRS信息

./rda.sh -S -p Rac

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p Rac

RAC ASM   收集 Rac + Clusterware + Asm 信息

./rda.sh -S -p Rac_Asm

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ASM_ORACLE_SID=+ASM1 -p Rac_Asm

Rac_AdvancedAsm 收集更详细的Rac + Clusterware + Asm 信息

./rda.sh -S -p Rac_AdvancedAsm

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ASM_ORACLE_SID=+ASM1 -p Rac_AdvancedAsm

Rac_Perf  收集RAC数据库性能信息

./rda.sh -S -p Rac_Perf

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p Rac_Perf

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,PERF_START_TIME=11-Mar-2010_12:00,PERF_END_TIME=11-Mar-2010_13:00 -p Rac_Perf

DirectNFS 

./rda.sh -S -p DirectNFS

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p DirectNFS

AsmFileSystem

./rda.sh -S -p AsmFileSystem

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p AsmFileSystem

DB_Assessment 

./rda.sh -S -p Rac_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p Rac_Assessment

 Rac_Assessment

./rda.sh -S -p Rac_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p Rac_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ASM_ORACLE_SID=+ASM1 -p Rac_Assessment

 Maa_Assessment

./rda.sh -S -p Maa_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/ -p Maa_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ASM_ORACLE_SID=+ASM1 -p Maa_Assessment

Exadata_Assessment

./rda.sh -S -p Exadata_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p Exadata_Assessment

 ./rda.sh -vSCRPfy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1,EXA_COLLECT_CELL=0 -p Exadata_Assessment

 ./rda.sh -vSCRPfy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p Exadata_Assessment

Maa_Exa_Assessment

./rda.sh -S -p Maa_Exa_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p Maa_Exa_Assessment

./rda.sh -Sy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1,EXA_COLLECT_CELL=0 -p Maa_Exa_Assessment

./rda.sh -vSCRPfy -e SQL_SYSDBA=1,SQL_LOGIN=/,ALERT_TEXT=1 -p Maa_Exa_Assessment

 

 

 

还可以利用rda对OS做数据库软件安装前的预检查,如将需要安装11.2g,则执行 ./rda.sh -T hcve:

 

 

[oracle@vrh8 rda]$ ./rda.sh -T hcve
Processing HCVE tests ...
Available Pre-Installation Rule Sets:
1. Oracle Database 10g R1 (10.1.0) Preinstall (Linux-x86)
2. Oracle Database 10g R1 (10.1.0) Preinstall (Linux AMD64)
3. Oracle Database 10g R1 (10.1.0) Preinstall (IA-64 Linux)
4. Oracle Database 10g R2 (10.2.0) Preinstall (Linux AMD64)
5. Oracle Database 10g R2 (10.2.0) Preinstall (IA-64 Linux)
6. Oracle Database 10g R2 (10.2.0) Preinstall (Linux-x86)
7. Oracle Database 11g R1 (11.1.0) Preinstall (Linux AMD64)
8. Oracle Database 11g R1 (11.1.0) Preinstall (Linux-x86)
9. Oracle Database 11g R2 (11.2.0) Preinstall (Linux-x86)
ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00010 OS Certified?        PASSED  Adequate
A00050 Enter ORACLE_HOME    RECORD  /s01/oracle/product/10.2.0/db_1
A00060 ORACLE_HOME Valid?   PASSED  OHexists
A00070 O_H Permissions OK?  PASSED  CorrectPerms
A00080 oraInventory Permiss PASSED  oraInventoryOK
A00090 Got ld,nm,ar,make?   PASSED  ld_nm_ar_make_found
A00100 Umask Set to 022?    PASSED  UmaskOK
A00120 Limit Processes      PASSED  Adequate
A00130 Limit Descriptors    PASSED  Adequate
A00140 LDLIBRARYPATH Unset? PASSED  UnSet
A00180 JAVA_HOME Unset?     PASSED  UnSet
A00190 Enter JDK Home       RECORD
A00200 JDK Version          FAILED  JDK home is missing
A00210 Other O_Hs in PATH?  FAILED  OratabEntryInPath
A00220 Other OUI Up?        PASSED  NoOtherOUI
A00230 /tmp Adequate?       PASSED  TempSpaceOK
A00240 Disk Space OK?       PASSED  DiskSpaceOK
A00250 Swap (in MB)         RECORD  5951
A00260 RAM (in MB)          PASSED  3955
A00270 Swap OK?             PASSED  SwapToRAMOK
A00280 Network              PASSED  Connected
A00290 IP Address           RECORD  192.168.1.191
A00300 Domain Name          RECORD  oracle.com
A00310 DNS Lookup           FAILED  nslookup host.domain
A00320 /etc/hosts Format    FAILED  Missing host.domain
A00330 Kernel Parameters OK PASSED  KernelOK
A00380 Tainted Kernel?      PASSED  NotVerifiable
A00400 ip_local_port_range  PASSED  RangeOK
A00480 EL4 RPMs OK?         SKIPPED NotEL4
A00490 EL5 RPMs OK?         FAILED  [kernel-headers(i386)] not installed ..>
A00530 RHEL4 RPMs OK?       SKIPPED NotRedHat
A00540 RHEL5 RPMs OK?       SKIPPED NotRedHat
A00570 SUSE SLES10 RPMs OK? SKIPPED NotSuSE
A00580 SUSE SLES11 RPMs OK? SKIPPED NotSuSE
Result file: /home/oracle/rda/output/RDA_HCVE_A200DB11R2_lnx_res.htm

 

 

上例对OS做了Oracle Database 11g R2 (11.2.0) Preinstall的预安装检查 ,并给出了检查结果。

 

 

 

还可以使用./rda.sh命令对现有的RDA软件做完整性检测,保证RDA没有被修改过:

 

 

[oracle@vrh8 rda]$ ./rda.sh -cv
Loading the file list ...
Checking the directory '.' ...
Checking the directory 'RDA' ...
Checking the directory 'RDA/Handle' ...
Checking the directory 'RDA/Library' ...
Checking the directory 'RDA/Library/Remote' ...
Checking the directory 'RDA/Local' ...
Checking the directory 'RDA/Object' ...
Checking the directory 'RDA/Operator' ...
Checking the directory 'RDA/Value' ...
Checking the directory 'hcve' ...
Checking the directory 'modules' ...
No issues found
Oracle RDA Remote Diagnostic Agent 使用