flutter Slide拖动滑条


main.dart 

import 'package:flutter/material.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
        debugShowCheckedModeBanner: false,
        title: 'This is a Slide Example',
        home: Scaffold(
            appBar: AppBar(
              title: Text('ABC'),
            ),
            body: Column(children: [
              Padding(
                child: Text(
                  'This is a Slide Example',
                  style: TextStyle(fontSize: 20),
                ),
                padding: EdgeInsets.all(50),
              ),
              SlideExample(),
              RangeSliderExample()
            ])));
  }
}

class SlideExample extends StatefulWidget {
  @override
  _Slider1State createState() => _Slider1State();
}

class _Slider1State extends State {
  double rating = 0;
  @override
  Widget build(BuildContext context) {
    // TODO: implement build

    return Slider(
      label: "$rating",
      divisions: 10,
      value: rating,
      onChanged: (newRating) {
        setState(() => rating = newRating);
      },
    );
  }
}

class RangeSliderExample extends StatefulWidget {
  @override
  _RangeSliderExampleState createState() => _RangeSliderExampleState();
}

class _RangeSliderExampleState extends State {
  var selectedRange = RangeValues(0.2, 0.8);

  @override
  Widget build(BuildContext context) {
    // TODO: implement build
    return RangeSlider(
      values: selectedRange,
      onChanged: (RangeValues newRange) {
        setState(() {
          selectedRange = newRange;
        });
      },
    );
  }
}


MySQL INNODB 损坏恢复指南

首要步骤是关闭MYSQL实例和备份数据目录

/etc/init.d/mysqld stop

mkdir /root/myql_backup
tar -jcvf /root/myql_backup/mysql_data.tar.bz2 /var/lib/mysql

 

尝试启动 mysql 实例

/etc/init.d/mysqld start

 

如果实例不崩溃那么 用mysqldump做逻辑备份

 

/etc/init.d/mysql start
mysqldump –single-transaction -AER > /root/dump_wtrans.sql
mysqldump -AER > /root/dump.sql

 

 

如果 MYSQL 实例崩溃则 尝试设置 innodb_force_recovery 参数

 

例如

 

mode=1; sed -i “/^\[mysqld\]/{N;s/$/\ninnodb_force_recovery=$mode/}” /etc/my.cnf

 

注意这些的mode=1 设置了 innodb_force_recovery 参数的级别

 

innodb_force_recovery 的参数级别含义为:

 

Mode 1 当发现一个损坏的数据页时不让MYSQL实例自动崩溃
Mode 2 不启用后台操作
Mode 3 不尝试 回滚事务
Mode 4 不计算状态也不应用buffer change
Mode 5 在启动过程中不去关心undo log
Mode 6 在启动过程中不关心重做日志ib_logfiles, 不去做前滚

 

以上 mode 2 会包含 mode 1, mode 3会包含 mode 2和 mode 1 ,依此类推

 

同时从 MYSQL 5.6.15 开始 mode 4-6 会让mysql 以只读模式运行

 

若已经设置了innodb_force_recovery =6 还是有问题 ,那么需要进一步诊断

 

检查日志

 

tail -200 /var/lib/mysql/`hostname`.err
tail -f /var/log/mysqld.log

如发现

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 515891.

 

 

以上报错说明mysql innodb 引擎发现有数据页损坏 进一步诊断:

 

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
innochecksum $i
done

or

DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd -o -name ibdata* | xargs -I{} innochecksum {}

 

如发现

 

mysql: 120901 9:43:55 InnoDB: Error: page 70944 log sequence number 8 1483471899
mysql: InnoDB: is in the future! Current system log sequence number 5 612394935.
mysql: InnoDB: Your database may be corrupt or you may have copied the InnoDB
mysql: InnoDB: tablespace but not the InnoDB log files. See
mysql: InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.2 Starting InnoDB on a Corrupted Database[/url]

 

以上日志为LSN 不同步问题, 其一般可以通过 设置 innodb_force_recovery解决的

 

如发现

 

[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!

InnoDB: Error: table ‘database/table’
InnoDB: in InnoDB data dictionary has tablespace id 423,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?

[ERROR] Cannot find or open table database/table from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.

可能丢失了表的.frm 或 .ibd文件 ,找回这些文件

 

当实例可以启动 那么如何检测表是否存在讹误?

 

可以使用 check table 命令

 

 

mysql> CHECK TABLE roundcube.users;
+—————–+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————–+——-+———-+———-+
| roundcube.users | check | status | OK |
+—————–+——-+———-+———-+
1 row in set (0.13 sec)

mysql> CHECK TABLE roundcube.dictionary;
+———————-+——-+———-+—————————————————————-+
| Table | Op | Msg_type | Msg_text |
+———————-+——-+———-+—————————————————————-+
| roundcube.dictionary | check | Warning | InnoDB: Tablespace is missing for table ’roundcube/dictionary’ |
| roundcube.dictionary | check | Error | Table ’roundcube.dictionary’ doesn’t exist |
| roundcube.dictionary | check | status | Operation failed |
+———————-+——-+———-+—————————————————————-+
3 rows in set (0.00 sec)

 

 

如果以上检测发现讹误,则可能导致mysql崩溃 ,那么可能需要使用 innodb_force_recovery=1 来启动实例 否则可能启动时实例崩溃

可以使用 mysqlcheck 来检测一个数据库

 

[root@ocp ~]# mysqlcheck -A -uroot -p
Enter password:
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
recovery.testeasy OK
sys.sys_config OK

 

如何从frm文件中恢复create table建表语句

 

可以从 https://downloads.mysql.com/archives/utilities/ 下载 MySQL Utilities

 

 

tar xvzf mysql-utilities*

cd mysql-utilities-1.4.3
chmod +x setup.py
./setup.py build
./setup.py install

 

 

mysqlfrm 会自己建一个mysql实例 需要单独的端口

mysqlfrm –basedir=/usr –user=mysql –port=3308 /var/lib/mysql/testdb/staff.frm

# Spawning server with –user=mysql.
# Starting the spawned server on port 3308 … done.
# Reading .frm files
#
# Reading the staff.frm file.
#
# CREATE statement for staff.frm:
#

CREATE TABLE `staff` (
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`address_id` smallint(5) unsigned NOT NULL,
`picture` blob,
`email` varchar(50) DEFAULT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT ‘1’,
`username` varchar(16) NOT NULL,
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`staff_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#…done.

 

 

有时需要 SET FOREIGN_KEY_CHECKS=0; 才能建表成功

在实例 没崩溃情况下 如何重建有问题的表

 

 

mysql -u root -p

USE dbname;
CREATE TABLE tablename_recovered LIKE tablename;
INSERT INTO tablename_recovered SELECT * FROM tablename;

 

这里 先见表 后插入

 

插入如果 遇到问题页面 ,那么可以用limit

 

insert ignore into tablename_recovered select * from tablename limit 10;
insert ignore into tablename_recovered select * from tablename limit 50;
insert ignore into tablename_recovered select * from tablename limit 100;
insert ignore into tablename_recovered select * from tablename limit 200;

 

 

测试 下插入到哪个位置 才报错

 

如果成功复制表 那么把原表删除 新表重命名

 

 

DROP dbname.tablename;
RENAME TABLE dbname.tablename_recovered TO dbname.tablename;

 

 

还原 所有数据库和 重置 ibdata/ib_log files

首先要有 有效的备份, 做这些操作前 确认这个备份可用!!

mysqldump -AER > /root/recovery_dump.sql

干掉所有 有问题的db

 

 

mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP DATABASE db1;
mysql> DROP DATABASE db2;

重启mysql 实例

mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop

sed -i ‘/innodb_force_recovery/d’ /etc/my.cnf

nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err

 

 

恢复 数据

 

mysql < /root/recovery_dump.sql

 

对于 Log Sequence Number Mismatched/In Future 可以考虑重建整个数据库 也可以重建重做日志

 

 

mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
/etc/init.d/mysql start

或者 临时交换存储引起 为myisam 然后换回来

 

mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=MYISAM;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘InnoDB’ AND TABLE_TYPE = ‘BASE TABLE'”

/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start
mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=InnoDB;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘MyISAM'”

 

如何合适的删除.ibd文件 可以使用如下命令 SET FOREIGN_KEY_CHECKS=0;

 

mysql -e “ALTER TABLE roundcube.staff DISCARD TABLESPACE”

 

如果误删除了 .ibd文件 如何 基于备份恢复前的清理工作

 

[ERROR] MySQL is trying to open a table handle but the .ibd file for table dbname/tblname does not exist.

ALTER TABLE dbname.tblname DISCARD TABLESPACE;

InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.
InnoDB: But let us remove the insert buffer entries for this tablespace.

DROP TABLE dbname.tblname;

InnoDB: We removed now the InnoDB internal data dictionary entry InnoDB: of table `dbname/tblname`.

 

 

如上完成后 再恢复重建整个表 .frm 文件在 但数据字典里没这个表了 先备份.frm 文件

 

然后执行 create table重建表 丢失.frm文件

 

但字典里有这个表

InnoDB: Error: table dbname/tblname already exists in
InnoDB internal InnoDB: data dictionary.

Have you deleted the .frm file

InnoDB: and not used DROP TABLE? …
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.

 

 

可以在别的db下重建这个表获得.frm 文件 然后拷贝过去

# mysql

mysql> CREATE DATABASE test2;
mysql> CREATE TABLE … CHARSET=utf8;
mysql> quit
# cp /var/lib/mysql/test2/test.frm /var/lib/mysql/test/
# mysql
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE test.test;

MySQL Centos/Redhat 6多版本迅速安装脚本

MYSQL 5.5.62

 

#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.5.62

wget --no-check-certificate https://zcdn.askmac.cn/mysql-community-common-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmac.cn/mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmac.cn/mysql-community-libs-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmac.cn/mysql-community-client-5.5.62-2.el6.x86_64.rpm
wget --no-check-certificate https://zcdn.askmac.cn/mysql-community-server-5.5.62-2.el6.x86_64.rpm


#安装

rpm -ivh mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm mysql-community-client-5.5.62-2.el6.x86_64.rpm mysql-community-common-5.5.62-2.el6.x86_64.rpm mysql-community-libs-5.5.62-2.el6.x86_64.rpm mysql-community-server-5.5.62-2.el6.x86_64.rpm



修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysql_install_db


chown -R mysql:mysql /@@datadir 

mysqld_safe  &

mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql




 

MYSQL 5.6.46

 

 

#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.6.46
wget --no-check-certificate   https://zcdn.askmac.cn/mysql-community-common-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmac.cn/mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmac.cn/mysql-community-libs-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmac.cn/mysql-community-client-5.6.46-2.el6.x86_64.rpm
wget --no-check-certificate   https://zcdn.askmac.cn/mysql-community-server-5.6.46-2.el6.x86_64.rpm



#安装


rpm -ivh mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm mysql-community-libs-5.6.46-2.el6.x86_64.rpm mysql-community-client-5.6.46-2.el6.x86_64.rpm mysql-community-common-5.6.46-2.el6.x86_64.rpm  mysql-community-server-5.6.46-2.el6.x86_64.rpm


修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysql_install_db


chown -R mysql:mysql /@@datadir 

mysqld_safe  &


mysql

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql');
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql




 

5.7.28

 

>#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 5.7.28 
wget --no-check-certificate     https://zcdn.askmac.cn/mysql-community-common-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmac.cn/mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmac.cn/mysql-community-libs-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmac.cn/mysql-community-client-5.7.28-1.el6.x86_64.rpm
wget --no-check-certificate     https://zcdn.askmac.cn/mysql-community-server-5.7.28-1.el6.x86_64.rpm


rpm -ivh mysql-community-common-5.7.28-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm mysql-community-libs-5.7.28-1.el6.x86_64.rpm  mysql-community-client-5.7.28-1.el6.x86_64.rpm mysql-community-server-5.7.28-1.el6.x86_64.rpm



修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysqld --initialize


chown -R mysql:mysql /@@datadir 

mysqld_safe --skip-grant-tables &

mysql

UPDATE mysql.user SET authentication_string = PASSWORD('mysql') , password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
exit 



/etc/init.d/mysqld stop

/etc/init.d/mysqld start

mysql -uroot -pmysql



8.0.17

 

>>#如果之前有MYSQL 那么先停服务
/etc/init.d/mysqld stop


#如果之前已经装过MYSQL 那么先备份数据目录
tar -jcvf mysql.tar.bz2 /var/lib/mysql 

#卸载之前已经安装过的MYSQL软件RPM 


rpm -qa|grep mysq| xargs rpm -e --nodeps


#下载MYSQL 8.0.17

wget --no-check-certificate    https://zcdn.askmac.cn/mysql-community-common-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmac.cn/mysql-community-libs-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmac.cn/mysql-community-client-8.0.17-1.el6.x86_64.rpm
wget --no-check-certificate    https://zcdn.askmac.cn/mysql-community-server-8.0.17-1.el6.x86_64.rpm



rpm -ivh mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-common-8.0.17-1.el6.x86_64.rpm  mysql-community-libs-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm 
 
修改/etc/my.cnf 中的 datadir 到 你要的目录

mkdir /@@datadir 
chown mysql:mysql /@@datadir 

mysqld --initialize


chown -R mysql:mysql /@@datadir 

mysqld_safe --skip-grant-tables &


 mysql 
 
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;



/etc/init.d/mysqld stop 

/etc/init.d/mysqld start


mysql

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql';
FLUSH PRIVILEGES;
exit;


mysql -uroot -pmysql



Oracle执行计划的IN-OUT字段含义

The In-Out Column  其代表执行步骤的 串行与并行输入输出情况

 

 

SERIAL (blank): Serial execution. Currently, SQL is not loaded in the OTHER column for this case.
SERIAL_FROM_REMOTE (S -> R): Serial execution at a remote site.
PARALLEL_FROM_SERIAL (S -> P): Serial execution. Output of step is partitioned or broadcast to parallel execution servers.
PARALLEL_TO_SERIAL (P -> S): Parallel execution. Output of step is returned to serial QC process.
PARALLEL_TO_PARALLEL (P -> P): Parallel execution. Output of step is repartitioned to second set of parallel execution servers.
PARALLEL_COMBINED_WITH_PARENT (PWP): Parallel execution; Output of step goes to next step in same parallel process. No interprocess communication to parent.
PARALLEL_COMBINED_WITH_CHILD (PWC): Parallel execution. Input of step comes from prior step in same parallel process. No interprocess communication from child.
https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL94734

 

Transaction recovery: lock conflict caught and ignored

Transaction recovery: lock conflict caught and ignored

 

之前有用户在11g上alert.log出现大量Transaction recovery: lock conflict caught and ignored,同时产生大量redo重做日志;观察AWR可以发现大量db block changes发生在UNDO$基础表上。

对于该问题可以尝试如下几种方案:

1、找出Dead Transaction并清理相关对象,如 https://dba010.com/2013/04/30/transaction-recovery-lock-conflict-caught-and-ignored/

 

 

ALERT.LOG:
.....
Transaction recovery: lock conflict caught and ignored
.....
And also some incident files are being created in $ORACLE_BASE/diag/rdbms/dbname/instancename/incident folder.
In my case the error started after SUPPLEMENTAL LOGGING enabled in a RAC environment. After disabling it the messages have not disappeared, but incident files are no longer being created.
1. Dead Trasaction
SQL> select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like ‘%DEAD%’
and a.ktuxeusn = b.us#;
USEG	INSTID	STATUS	XID_USN	XID_SLOT	XID_SEQ	UNDOBLOCKS	TXSTATUS
_SYSSMU7_881277423$	1	3	7	13	1829999	1	ACTIVE
_SYSSMU8_4204495590$	1	3	8	32	3045564	1	ACTIVE
_SYSSMU10_1314081219$	1	3	10	3	11844457	1	ACTIVE
Transaction id is  XID_USN.XID_SLOT.XID_SEQ
So in our case, for the first row it will be 7.13.1829999
2.  Read transaction table from undo header.
ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU7_881277423$’;
….
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ————————————————————————————————
   0x00    9    0x03  0x1bf45c  0x000b  0x0000.789de808  0x00c242eb  0x0000.000.00000000  0x00000001   0x00c242eb  1367258143
   0x01    9    0x00  0x1c031b  0x0014  0x0000.789e6018  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225
   0x02    9    0x00  0x1c147a  0x000e  0x0000.789e694b  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230
   0x03    9    0x00  0x1c06f9  0x0016  0x0000.789e601c  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225
   0x04    9    0x00  0x1c06c8  0x0009  0x0000.789e3566  0x00c242f9  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x05    9    0x00  0x1c1167  0x0015  0x0000.789e357f  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x06    9    0x00  0x1c2716  0x0017  0x0000.789e69e1  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230
   0x07    9    0x00  0x1c1045  0x000c  0x0000.789e1bdb  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367258170
   0x08    9    0x00  0x1c2614  0x0005  0x0000.789e357e  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x09    9    0x00  0x1bfa03  0x0021  0x0000.789e3574  0x00c242f9  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x0a    9    0x00  0x1bf712  0x001e  0x0000.789e3246  0x00c242f1  0x0000.000.00000000  0x00000001   0x00000000  1367258190
   0x0b    9    0x00  0x1c1e01  0x0007  0x0000.789e1bd9  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367258170
   0x0c    9    0x00  0x1c08e0  0x000a  0x0000.789e3244  0x00c242f1  0x0000.000.00000000  0x00000006   0x00000000  1367258190
   0x0d   10    0x90  0x1bec6f  0x0038  0x0000.789e783e  0x00c242fb  0x0000.000.00000000  0x00000001   0x00c242fb  0
   0x0e    9    0x00  0x1c068e  0x0010  0x0000.789e694d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230
   0x0f    9    0x00  0x1c151d  0x0012  0x0000.789e3578  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x10    9    0x00  0x1c26bc  0x0006  0x0000.789e69df  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230
   0x11    9    0x00  0x1c16eb  0x0000  0x0000.789cbd77  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367257923
   0x12    9    0x00  0x1c082a  0x001d  0x0000.789e357c  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x13    9    0x00  0x1c1459  0x001f  0x0000.789e7891  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238
   0x14    9    0x00  0x1c14b8  0x0003  0x0000.789e601a  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225
   0x15    9    0x00  0x1c0457  0x0020  0x0000.789e39d3  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258195
   0x16    9    0x00  0x1c1326  0x0002  0x0000.789e601d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225
   0x17    9    0x00  0x1c0db5  0x001c  0x0000.789e788a  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238
   0x18    9    0x00  0x1bffe4  0x001b  0x0000.789e400d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258200
   0x19    9    0x00  0x1c16e3  0x0001  0x0000.789e5fd2  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225
   0x1a    9    0x00  0x1bdbb2  0x0018  0x0000.789e400b  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258200
   0x1b    9    0x00  0x1c1141  0x0019  0x0000.789e453a  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258204
   0x1c    9    0x00  0x1bc9a0  0x0013  0x0000.789e788e  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238
   0x1d    9    0x00  0x1c02ef  0x0008  0x0000.789e357d  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
   0x1e    9    0x00  0x1c0b6e  0x0004  0x0000.789e3250  0x00c242f9  0x0000.000.00000000  0x00000009   0x00000000  1367258190
   0x1f    9    0x00  0x1c00ad  0xffff  0x0000.789e78a1  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238
   0x20    9    0x00  0x1c166c  0x001a  0x0000.789e39dd  0x00c242fa  0x0000.000.00000000  0x00000002   0x00000000  1367258195
   0x21    9    0x00  0x1c160b  0x000f  0x0000.789e3576  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192
  EXT TRN CTL::
  usn: 7
 
State# 10 means active transaction.
dba points to starting UNDO block address.
usn: Undo segment number
usn.index.wrap# gives transaction id.
 
An active transaction 0x0007.00d.001bec6f is available in slot 0x0d which has a dba of 0x00c242fb (12731131 in decimal)
3. Reading UNDO Block:
Identify fileID and blockID:
fileID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12731131) from x$dual;
3
blockID:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12731131) from x$dual;
148219
Dumping block
alter system dump datafile 3 block 148219;

UNDO BLK: 
xid: 0x0007.00d.001bec6f  seq: 0x41f9 cnt: 0x6   irb: 0x5   icl: 0x0   flg: 0x0000
 
Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
—————————————————————————
0x01 0x1f98     0x02 0x1f2c     0x03 0x1d7c     0x04 0x1d10     0x05 0x1ca0    
0x06 0x1bfc    
 
*—————————–
* Rec #0x1  slt: 0x0d  objn: 0(0x00000000)  objd: 0  tblspc: 0(0x00000000)
*       Layer:   5 (Transaction Undo)   opc: 7  rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*—————————–
uba: 0x00c242fa.41f9.37 ctl max scn: 0x0000.789b7668 prv tx scn: 0x0000.789bb8d7
txn start scn: scn: 0x0000.789e783e logon user: 88
prev brb: 12731116 prev bcl: 0
 
*—————————–
* Rec #0x2  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1  rci 0x00  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0012.01c.00322281 uba: 0x0102c5f0.3fa9.0a
                      flg: C—    lkc:  0     scn: 0x0000.789ca3f4
KDO Op code: LKR row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x038180fc  hdba: 0x018d64e2
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 14 to: 0
 
*—————————–
* Rec #0x3  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x02  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c242fb.41f9.02
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x038180fc  hdba: 0x018d64e2
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 14(0xe) flag: 0x2c lock: 1 ckix: 0
ncol: 9 nnew: 6 size: 0
col  1: [ 7]  78 71 04 1d 13 01 01
col  2: [ 2]  c1 13
col  3: [ 1]  80
col  4: [16]  10 e5 00 2e 10 d1 10 d0 10 d7 10 e3 10 db 10 d8
col  5: [174]
10 d0 10 ed 10 d0 10 e0 10 d8 10 e1 00 20 10 d0 00 2e 10 e0 00 2e 00 20 10
de 10 e0 10 dd 10 d9 10 e3 10 e0 10 d0 10 e2 10 e3 10 e0 10 d8 10 e1 00 20
10 e1 10 d0 10 d2 10 d0 10 db 10 dd 10 eb 10 d8 10 d4 10 d1 10 dd 00 20 10
dc 10 d0 10 ec 10 d8 10 da 10 d8 10 e1 00 20 10 e3 10 e4 10 e0 10 dd 10 e1
00 20 10 d2 10 d0 10 db 10 dd 10 db 10 eb 10 d8 10 d4 10 d1 10 d4 10 da 10
e1 00 20 10 d1 10 d0 10 e2 10 dd 10 dc 00 20 10 d2 10 d8 10 dd 10 e0 10 d2
10 d8 00 20 10 de 10 d4 10 e0 10 d0 10 dc 10 d8 10 eb 10 d4 10 e1 00 2e
col  6: [36]
00 54 00 01 04 0c 00 00 00 02 00 00 00 01 00 00 09 07 b0 63 00 10 09 00 00
00 00 00 00 00 00 00 00 00 00 00
 
*—————————–
* Rec #0x4  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x03  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000c.017.000d65d6 uba: 0x0103df2c.22a5.20
                      flg: C—    lkc:  0     scn: 0x0000.789c4694
KDO Op code: LKR row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x03833994  hdba: 0x0181f832
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 7 to: 0
 
*—————————–
* Rec #0x5  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1  rci 0x04  
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 1
op: C  uba: 0x00c242fb.41f9.04
KDO Op code: LMN row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x03833994  hdba: 0x0181f832
itli: 1  ispac: 0  maxfr: 4858
 
*—————————–
* Rec #0x6  slt: 0x0d  objn: 89703(0x00015e67)  objd: 92020  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1  rci 0x05  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
irb points to last UNDO RECORD in UNDO block.
rci points to previous UNDO RECORD. if rci=0, it’s the first UNDO RECORD.
Recovery operation starts from irb and chain is followed by rci until rci is zero.
The transaction starts recovery from UNDO RECORD of 0x5.
4. Reading UNDO Records:

* Rec #0x5  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x04  
….
* Rec #0x4  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x03  
….
* Rec #0x3  slt: 0x0d objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x02  
…
* Rec #0x2  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)
*       Layer:  11 (Row)   opc: 1   rci 0x00  
…
objn means object id.
5. Find these objects
The following objects need recovery:
select * from dba_objects
where object_id in (89834,110769);
………………………………………………………..
This problem is Oracle Bug:9857702:
.....
Affects:
Product (Component) Oracle Server (Rdbms)  
Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1  
Versions confirmed as being affected
•11.2.0.1 
•11.1.0.7 
 
Platforms affected Generic (all / most platforms affected)  
Fixed:
This issue is fixed in
•12.1 (Future Release) 
•11.2.0.2 (Server Patch Set) 
•11.1.0.7.8 Patch Set Update 
•11.1.0.7 Patch 40 on Windows Platforms  
.....
6. Workaround:
Recreate objects that need recovery.
Or drop them Smile

 

 

2、尝试设置 FAST_START_PARALLEL_ROLLBACK=HIGH 看是否能解决

 

3、尝试设置10513 LEVEL 2 事件,暂时屏蔽回滚事务

 

空闲时间用Flutter写的安卓APP 金庸武侠小说集离线版

空闲时间用Flutter写的安卓APP 金庸武侠小说集离线版 ,Google Play上架中,APK版本直接下载:

https://zcdn.askmac.cn/%E9%87%91%E5%BA%B8%E6%AD%A6%E4%BE%A0%E5%B0%8F%E8%AF%B4%E9%9B%86%E7%A6%BB%E7%BA%BF%E7%89%88.apk

 

 

2019年10月的大阪 姬路 京都之旅

 

 

 

[Read more…]

2019年9月的邮轮 福冈之旅

 

 

[Read more…]

Flutter Widget创建时运行异步操作

样例代码

import 'package:flutter/material.dart';
import 'dart:async';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    // TODO: implement build

    return MaterialApp(
        home: Scaffold(
      body: Center(child: AsyncTest()),
    ));
  }
}

class AsyncTest extends StatefulWidget {
  @override
  _AsyncTestState createState() => _AsyncTestState();
}

int getNumber(int n) {
  if (n == 0) {
    return 0;
  } else if (n == 1) {
    return 1;
  } else {
    return getNumber(n - 1) + getNumber(n - 2);
  }
}

Future loadAsset(BuildContext context) async {
  return await DefaultAssetBundle.of(context)
      .loadString('assets/novels/0-1.txt');
}

class _AsyncTestState extends State {
  bool loading = true;

  @override
  void initState() {
    super.initState();

    loadAsset(context).then((result) {
      debugPrint(
          'consume cpu time function result is ' + getNumber(20).toString());

      Future.delayed(Duration(seconds: 1)).then((result) {
        setState(() {
          loading = false;
        });
      });
    });
  }


  
  @override
  Widget build(BuildContext context) {
    // TODO: implement build
    if (loading == true) {
      return CircularProgressIndicator();
    }

    return Text('load finished ');
  }
}


几点说明:

  1. 使用StatefulWidget 有状态Widget
  2. 在initState函数中执行异步操作
  3. 保证在已有结果的状态下调用setState

 

 

参考:

https://flutter.institute/run-async-operation-on-widget-creation/

 

阿里云 ECS 快速安装Oracle 19c

阿里云

 

 

 

操作系统使用 CentOS 7.6 64位,目前对于oracle没有任何必要使用32位操作系统了!!

磁盘在40GB系统盘的基础上增加一块ESSD云盘 大小40GB,作为存放oracle数据库的基础配置。并启用每日自动备份!

 

 

网络设置使用默认配置,用户实际使用时:

  1. 若应用服务器位于阿里云同机房,一般可以直接使用内网连接,不需要太大的公网带宽
  2. 若应用服务器不在阿里云同机房,需要使用公网连接,则需要提高公网带宽

 

无需启动3389端口

 

 

 

 

安全组使用默认配置,后续需要为ORACLE监听配置安全组网络端口。

 

最终配置报价时317元/每月!

 

最后确认订单,服务器就绪后,ssh远程登陆。

首先划分磁盘并 测试ESSD的IO, 第一块ESSD的路径一般为 /dev/vdb, 在vdb上划分1个分区,并创建XFS文件系统,挂在到  /d01目录下

 

 

fdisk /dev/vdb



Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x5d899fda.

Command (m for help): p

Disk /dev/vdb: 42.9 GB, 42949672960 bytes, 83886080 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x5d899fda

   Device Boot      Start         End      Blocks   Id  System

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-83886079, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-83886079, default 83886079):
Using default value 83886079
Partition 1 of type Linux and of size 40 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.



partprobe /dev/vdb

[root@iZuf6fz9mqmeexkh25fbrbZ ~]# ls -l /dev/vdb1
brw-rw---- 1 root disk 253, 17 Sep 11 12:51 /dev/vdb1


 mkfs.xfs /dev/vdb1
 mkdir /d01
 mount /dev/vdb1 /d01
 
 
[root@iZuf6fz9mqmeexkh25fbrbZ ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   12G   27G  31% /
devtmpfs        3.8G     0  3.8G   0% /dev
tmpfs           3.8G     0  3.8G   0% /dev/shm
tmpfs           3.8G  636K  3.8G   1% /run
tmpfs           3.8G     0  3.8G   0% /sys/fs/cgroup
tmpfs           768M     0  768M   0% /run/user/0
/dev/vdb1        40G   33M   40G   1% /d01


chown oracle /d01
echo "/dev/vdb1  /d01  xfs" >> /etc/fstab


 

 

测试一下ESSD的IO :

 

 

 

yum install fio


cd /d01 

fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4k --iodepth=64 --size=4G --readwrite=randrw --rwmixread=75
test: (g=0): rw=randrw, bs=(R) 4096B-4096B, (W) 4096B-4096B, (T) 4096B-4096B, ioengine=libaio, iodepth=64


fio-3.1
Starting 1 process
test: Laying out IO file (1 file / 4096MiB)
Jobs: 1 (f=1): [m(1)][100.0%][r=11.1MiB/s,w=3744KiB/s][r=2848,w=936 IOPS][eta 00m:00s]
test: (groupid=0, jobs=1): err= 0: pid=21835: Wed Sep 11 13:48:03 2019
   read: IOPS=2847, BW=11.1MiB/s (11.7MB/s)(3070MiB/275992msec)
   bw (  KiB/s): min=10560, max=12232, per=100.00%, avg=11393.26, stdev=241.24, samples=551
   iops        : min= 2640, max= 3058, avg=2848.32, stdev=60.34, samples=551
  write: IOPS=951, BW=3807KiB/s (3898kB/s)(1026MiB/275992msec)
   bw (  KiB/s): min= 3344, max= 4256, per=100.00%, avg=3807.62, stdev=165.68, samples=551
   iops        : min=  836, max= 1064, avg=951.90, stdev=41.42, samples=551
  cpu          : usr=0.59%, sys=2.51%, ctx=951009, majf=0, minf=22
  IO depths    : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=0.1%, >=64=100.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.1%, >=64=0.0%
     issued rwt: total=785920,262656,0, short=0,0,0, dropped=0,0,0
     latency   : target=0, window=0, percentile=100.00%, depth=64

Run status group 0 (all jobs):
   READ: bw=11.1MiB/s (11.7MB/s), 11.1MiB/s-11.1MiB/s (11.7MB/s-11.7MB/s), io=3070MiB (3219MB), run=275992-275992msec
  WRITE: bw=3807KiB/s (3898kB/s), 3807KiB/s-3807KiB/s (3898kB/s-3898kB/s), io=1026MiB (1076MB), run=275992-275992msec

Disk stats (read/write):
  vdb: ios=785887/263160, merge=0/3, ticks=13264955/4397142, in_queue=15400027, util=87.17%


可以看到 单块阿里云ESSD的随机读写  IOPS ,read: IOPS=2847 write: IOPS=951        还是不错的。

 

开始安装oracle 19c  , 首先上传介质到服务器/root目录

使用浏览器下载以下2个介质并上传到服务器目录

https://download.oracle.com/otn/linux/oracle19c/190000/oracle-database-ee-19c-1.0-1.x86_64.rpm
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm


su - root 
yum -y install git 
git clone https://github.com/macleanliu/ora-easy-deploy
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm
bash ora-easy-deploy/create_db.sh


 

bash ora-easy-deploy/create_db.sh ,执行该脚本输入 数据库名 ,数据库目录和SYS/SYSTEM密码等,后自动建库。

 

安装完成后,执行:

 

 

#设置ORACLE OS账户密码
su - root
passwd oracle                     
su - oracle

lsnrctl start 
sql
alter system register;
alter system set "_optimizer_aggr_groupby_elim"=false;
alter system set "_drop_stat_segment"=1;
alter system set "_common_data_view_enabled"=false;
alter system set optimizer_adaptive_features=false;
alter system set "_optimizer_dsdir_usage_control"=0; 
alter system set "_enable_automatic_sqltune"=false scope=both; 
alter system set "_serial_direct_read"=false;
alter system set "_nlj_batching_enabled" = 0; 
alter system set "_optimizer_undo_cost_change" = '10.2.0.4';
alter system set "_optimizer_null_aware_antijoin" = false;
alter system set "_optimizer_extend_jppd_view_types" = false;
alter system set "_replace_virtual_columns" = false;
alter system set "_first_k_rows_dynamic_proration" = false;
alter system set "_bloom_pruning_enabled" = false;
alter system set "_optimizer_multi_level_push_pred" = false;
alter system set "_optim_peek_user_binds"=false; 
alter system set client_result_cache_size=0 scope=spfile;
alter system set result_cache_mode=MANUAL;
alter system set "_diag_hm_rc_enabled"=false; 
alter system set audit_trail=none scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=both; 
alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set deferred_segment_creation=false; 
alter system set "_datafile_write_errors_crash_instance"=false ;
alter system set "_fairness_threshold"=6 scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_defer_time"=3  scope=spfile;
alter system set "parallel_force_local"=false;
alter system set "_gc_bypass_readers"=false;
alter system set "_row_cr"=false;
alter system set ddl_lock_timeout=0;
alter system set "_gby_hash_aggregation_enabled"=False scope=spfile;
alter system set "_cleanup_rollback_entries"=400 scope=spfile;
alter system set "_dbms_sql_security_level"=0                          scope=spfile;
alter system set "_bloom_pruning_enabled"=False                        scope=spfile;
alter system set "_simple_view_merging"=True                           scope=spfile;
alter system set "_enable_NUMA_optimization"=FALSE                     scope=spfile;
alter system set "_fix_control"='9344709:OFF'                           scope=spfile;
alter system set "_px_use_large_pool"=True                              scope=spfile;
alter system set "_mv_refresh_use_hash_sj"=FALSE                      scope=spfile;
alter system set "_mv_refresh_use_stats"=True                          scope=spfile;
alter system set "_like_with_bind_as_equality"=TRUE                    scope=spfile;
alter system set optimizer_secure_view_merging=false                   scope=spfile;
alter system set optimizer_capture_sql_plan_baselines=False            scope=spfile;
alter system set event="10949 TRACE NAME CONTEXT FOREVER:28401 trace name context forever, level 1"  scope=spfile;
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto optimizer stats collection', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto space advisor', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL,window_name => NULL);
commit;
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');


// to disable histogram , you set bucket size to 1

exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );
commit;



// disable 19c automatic indexing feature 

alter system set "_optimizer_auto_index_allow"=NEVER scope=spfile;
alter system set "_optimizer_use_auto_indexes"=OFF scope=spfile;

shutdown immediate;
startup ;


完成配置。

 

以上完成了基础配置,但外网客户端还是无法访问oracle服务器监听的 ,例如使用tnsping工具ping服务器监听,或sqlplus均无法登陆

 tnsping 47.XX.XX.XX

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-9月 -2019 10:35:17

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

已使用的参数文件:
sqlnet.ora

已使用 HOSTNAME 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=47.XX.XX.XX)(PORT=1521)))
TNS-12535: TNS: 操作超时

sqlplus system/oracle@47.XX.XX.XX:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 9月 12 10:39:07 2019

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

ERROR:
ORA-12170: TNS: 连接超时



我们在阿里云控制台,点选ECS服务器,更多=》网络和安全组=》安全组配置=》配置规则

 

 

点击添加安全组规则:

 

选择协议类型,ORACLE 1521 ,如果你要使用非默认ORACLE端口,即不使用1521端口,那么需要在端口范围内自行选择端口;这里我们使用默认端口,因为可以指定默认端口允许的外网IP,所以一般使用默认端口即可; 授权对象中输入 应用服务器的外网IP 。

 

 

 

点击确定后1分钟后, 可以尝试登陆:

 

 

tnsping 47.XX.XX.XX

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-9月 -2019 10:46:46

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

已使用的参数文件:
sqlnet.ora

已使用 HOSTNAME 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=47.XX.XX.XX)(PORT=1521)))
OK (10 毫秒)


sqlplus system/oracle@47.XX.XX.XX:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 9月 12 10:47:33 2019

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

上次成功登录时间: 星期四 9月  12 2019 10:33:17 +08:00

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production



可以看到登陆成功,这样我们就可以愉快的使用阿里云上的 ORACLE 19c了!!

沪ICP备14014813号-2

沪公网安备 31010802001379号