bill 发布的文章

通过top -p mysqlpid -H 可以找到thread_os_id ->

                    通过thread_os_id 和  performance_schema.threads 找到thread_id ->
                     通过thread_id和performance_schema.events_statements_current 找到sql语句

[root@node2 ~]# ps -ef | grep -i mysql
mysql 2296 1211 18 14:17 ? 00:03:46 /home/db/mysql/product/bin/mysqld --basedir=/home/db/mysql/product --datadir=/mysqldata/data --plugin-dir=/home/db/mysql/product/lib/plugin --user=mysql --log-error=/mysqldata/logs/mysql_error.log --pid-file=/mysqldata/data/mysqld.pid --socket=/home/db/mysql/product/mysql.sock --port=13306

[root@node2 ~]# top -p 2296 -H
top - 14:59:55 up 42 min, 4 users, load average: 0.41, 0.81, 1.39
Threads: 67 total, 2 running, 65 sleeping, 0 stopped, 0 zombie
%Cpu(s): 94.7 us, 4.7 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.7 si, 0.0 st
KiB Mem : 2238208 total, 77696 free, 943520 used, 1216992 buff/cache
KiB Swap: 2621436 total, 2621436 free, 0 used. 1139404 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAN
2966 mysql 20 0 6212608 868916 30128 R 97.3 38.8 11:46.51 mysqld
2460 mysql 20 0 6212608 868916 30128 R 0.3 38.8 0:09.28 mysqld
2296 mysql 20 0 6212608 868916 30128 S 0.0 38.8 0:00.79 mysqld

系统PID=2966 占用CPU 97%
通过performance_schema.threads.thread_os_id=2966可以找到thread_id,processlist_id ;

root@localhost 15:03:34 [performance_schema]>select thread_id,name ,PROCESSLIST_ID,THREAD_OS_ID from threads where thread_os_id = 2966 ;
thread_idnamePROCESSLIST_IDTHREAD_OS_ID
78thread/sql/one_connection102966

+-----------+---------------------------+----------------+--------------+

通过performance_schema.events_statements_current.thread_id = 78 可以找到当前占用cpu的SQL：

root@localhost 15:07:37 [performance_schema]>select DIGEST_TEXT from performance_schema.events_statements_current where thread_id = 78 ;
DIGEST_TEXT
SELECT * FROM t AS t1 , t AS t2 , t AS t3 , t AS t4

DIGEST_TEXT 的长度由变量max_digest_length控制:

root@localhost 16:08:01 [performance_schema]>show variables like 'max_digest_length' ;
Variable_nameValue
max_digest_length1024

参数group_replication_consistency：
1.EVENTUAL
RO 和 RW事务不会等待在当前之前事务执行完才执行。RW事务不会等待其他成员执行完，意味着一个事物可能单独在一个成员中存在比其它先。当发生failover时，新primary能在当前之前的事务执行被应用之前，接收新的RO和RW. RO可以结果是旧数据，RW可能导致冲突而引起回滚。

2.BEFORE_ON_PRIMARY_FAILOVER
新选取的主库，当前旧主库的blacklog时，新的RO和RW事各会一直held住，直到backlog被应用完。
当failover时，client能看到主库最新的值，确保一致性。但会client会在处理blacklog时产生延时，延时的大小取决于blacklog日志大小。

3。BEFORE
RW事务会等待之前事务完成才会被应用，RO事务会等待之前事物完成才会被执行。确保读事务永远是最新值。This reduces the overhead of synchronization on every RW transaction, by ensuring synchronization is used only on RO transactions.
（读少写多，一致性包含BEFORE_ON_PRIMARY_FAILOVER）

4。AFTER
RW事务一直等到其它成员已经应用后。对RO事务没影响。当一个事务本地提交时，随后其它节点能读到最新的值。主导RO。This reduces the overhead of synchronization on every RO transaction, by ensuring synchronization is used only on RW transactions
（读多写少，一直性包含BEFORE_ON_PRIMARY_FAILOVER）

5。BEFORE_AND_AFTER
RW事务等待 1。之前事务完成，2。更改被其他成员应用。RO会等待之前事务完成才发生。
（BEFORE_ON_PRIMARY_FAILOVER ）

BEFORE / BEFORE_AND_AFTER : RO,RW
AFTER : no impact RO

怎么选：
1.读多写少 -> AFTER
2 读少写多 -》 BEFORE
3。总是读到最新值 -》 BEFORE

  1. 主导RO事务，只要rw事务一旦提交，随后能读到最新的 AFTER
  2. 主导RO事务，RW事总是读最新数据，一旦提交并应用到其他成员中，随后读最新数据　－》BEFORE_AND_AFTER
    ６.　系统处理时，一些需求要一致性，一些不需要一致性

SET @@SESSION.group_replication_consistency= 'AFTER'
SET @@SESSION.group_replication_consistency= 'EVENTUAL'
７.在６基础之上，有些在需求要读最新值
SET @@SESSION.group_replication_consistency= ‘BEFORE’

迁移时在目标库上兼容性检查：
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'PDB1.xml',pdb_name =>'testpdb2')
WHEN TRUE THEN 'YES'  
ELSE 'NO'

END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;

select con_id, name, type, message, statusfrom PDB_PLUG_IN_VIOLATIONSwhere status<>'RESOLVED' order by name,time;

创建管理用户：

使用SEED创建PDB
CREATE PLUGGABLE DATABASE salespdb
ADMIN USER salesadm IDENTIFIED BY password
ROLES = (dba)
DEFAULT TABLESPACE sales

DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON

FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/',

                   '/disk1/oracle/dbs/salespdb/')

STORAGE (MAXSIZE 2G)
PATH_PREFIX = '/disk1/oracle/dbs/salespdb/';

从现有的一个PDB克隆一个新的PDB
REATE PLUGGABLE DATABASE newpdb FROM salespdb
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/salespdb/', '/disk1/oracle/dbs/newpdb/')
PATH_PREFIX = '/disk1/oracle/dbs/newpdb';

把一个PDB放进另外一个CDB
CREATE PLUGGABLE DATABASE salespdb
USING '/disk1/usr/salespdb.xml'
SOURCE_FILE_NAME_CONVERT =

('/disk1/oracle/dbs/salespdb/', '/disk2/oracle/dbs/salespdb/')

NOCOPY
STORAGE (MAXSIZE 2G)
TEMPFILE REUSE;

例子：把一个PDB放进另外一个CDB
有两个实例oracle12c和cdb1，原oracle12c实例有一个pdb2，现在需要把这个pdb2插入到cdb1实例中。
解决方法：
方法1
export ORACLE_SID=oracle12c
sqlplus / as sysdba
alter pluggable database pdb2 open read only;
begin
dbms_pdb.describe
(
pdb_descr_file=>'/u01/pdb2.xml',
pdb_name=>'pdb2'
);
end;
/
不unplug pdb2直接把pdb2 插入到cdb1容器中
export ORACLE_SID=cdb1
sqlplus / as sysdba
create pluggable database salespdb using '/u01/pdb2.xml'
copy
file_name_convert=('/u01/app/oracle/oradata/pdb2/','/u01/app/oracle/oradata/cdb1/pdb2/');
alter pluggable database salespdb open;

方法2
也可以通过把pdb2先unplug 然后在插入到cdb1容器中如下
export ORACLE_SID=oracle12c
sqlplus / as sysdba
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 unplug into '/u01/pdb2.xml';
unplug之后的pdb是不能打开的，把unplug的pdb2插入到cdb1中
加上AS CLONE字句可以确保新的pdb有唯一的标识
create pluggable database salespdb as clone using '/u01/pdb2.xml'
copy
file_name_convert=('/u01/app/oracle/oradata/pdb2/','/u01/app/oracle/oradata/cdb1/pdb2/');

方法3
通过dblink方式进行源库到目标库的pdb复制迁移
源库
export ORACLE_SID=oracle12c
sqlplus / as sysdba
alter session set container=pdb1;
create user createdb identified by createdb;
grant create session,create pluggable database to creatadb;
目标库
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
PDB1 =
(DESCRIPTION =

(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = pdb1)
)

)
export ORACLE_SID=cdb1
sqlplus / as sysdba
create public database link node2_node1 connect to createdb identified by createdb
using 'pdb1';
create pluggable database pdb1 from pdb1@node2_node1
file_name_convert=('/u01/app/oracle/oradata/oracle12c/pdb1/','/u01/app/oracle/oradata/cdb1/pdb1/');

show con_name

查看数据库是否为CDB
SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
NAME Multitenant Option OPEN_MODE CON_ID
--------- ----------------------------- -------------------- ----------
EPPS Multitenant Option enabled READ WRITE 0
YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)

查看容器：
show con_name
select name from v$containers ;
SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;
show pdbs ;

确认当前是cdb还是no-cdb ?
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ZARADB YES

查询pdb
col pdb_name for a30
select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;
select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

PDB_ID PDB_NAME                             DBID STATUS        CREATION_SCN

---------- ------------------------------ ---------- ------------- ------------

     3 PDB1                           3337485269 NORMAL             1909544
     2 PDB$SEED                       4072348290 NORMAL             1720741

SQL>show pdbs

CON_ID       DBID NAME                           OPEN_MODE

---------- ---------- ------------------------------ ----------

     2 4072348290 PDB$SEED                       READ ONLY
     3 3337485269 PDB1                           READ WRITE

直接创建pdb
CREATE PLUGGABLE DATABASE pdb2
ADMIN USER pdb2dba IDENTIFIED BY anbaisheng
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE tbs_pdb2 DATAFILE '/opt/oracle/oradata/ZARADB/datafile/pdb2_001.dbf' SIZE 25M AUTOEXTEND ON;

模板创建pdb
以现有pdb为模板创建,现有pdb必须在read only模式下才能被创建，否则报错
以pdb1为模板创建pdb2
drop pluggable database pdb2 including datafiles;
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;
create pluggable database pdb2
from pdb1
file_name_convert =('/opt/oracle/oradata/ZARADB/E98BEB85B1A80C56E0439A02A8C05841/datafile/','/opt/oracle/oradata/ZARADB/datafile/pdb2');

打开、关闭pdb
alter pluggable database pdb2 close;
alter pluggable database all close;
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 open;
alter pluggable database all open;

删除pdb,与删除表空间类似,可以级联删除数据文件
drop pluggable database pdb2;
drop pluggable database pdb2 including datafiles;

unplug后pdb只能mount不能open
alter pluggable database pdb2 unplug into '/tmp/pdb2.xml';

unplug后删除pdb，再使用xml文件加回pdb
drop pluggable database pdb2;
create pluggable database pdb2 using '/tmp/pdb2.xml' nocopy;

在pdb管理用户权限：
alter session set container=PDBORCL;
GRANT CONNECT,RESOURCE,CREATE VIEW TO COMPANY IDENTIFIED BY <password> ;
grant create any directory to company;
ALTER USER companycompany QUOTA UNLIMITED ON sales;
GRANT RESOURCE TO company;
ALTER USER COMPANY DEFAULT TABLESPACE company_data TEMPORARY TABLESPACE company_temp;

连接到CDB
[oracle@Ora12c /]$ sqlplus / as sysdba
--查看CDB中可用的service：
SQL> SELECT name,pdb FROM v$services ORDER BY name;

--通过lsnrctl 也可以判断：
oracle@node1:/home/db/oracle$ lsnrctl status
....
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "a2f51f35f5e34975e0536542a8c0adea" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testpdb" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@Ora12c /]$

通过这些service，就可以远程连接CDB。
--EZCONNECT
oracle@node1:/home/db/oracle$ sqlplus system/oracle@127.0.0.1:1521/testdb
oracle@node1:/home/db/oracle$ sqlplus system/oracle@127.0.0.1:1521/testpdb

--通过TNSNAMES.ORA连接：
在tnsnames.ora 中配置如下：
testdb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

--连接：
oracle@node1:/home/db/oracle$ sqlplus system/oracle@testdb
SQL> show con_name

CON_NAME

CDB$ROOT

pdb切换
SQL> select con_id,name from v$pdbs ;

SQL> show con_name
SQL> SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;

SQL> show con_name
SQL> alter session set container=testpdb;
Session altered

SQL> show con_name ;
SQL> SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;

SYS_CONTEXT('USERENV','CON_NAM

TESTPDB
SQL>
SQL> alter session set container=CDB$ROOT ;
SQL> show con_name ;
SQL> SELECT SYS_CONTEXT('USERENV','CON_NAME') FROM dual;

SYS_CONTEXT('USERENV','CON_NAM

CDB$ROOT

ORA-28040: No matching authentication protocol
ORA-28040: 没有匹配的验证协议

问题原因：
原因客户端与服务器段的密码生成版本（dba_users.password_versions）不一致导致
解决方法：
在数据库服务器上的$ORACLE_HOME/network/admin/sqlnet.ora文件添加相应参数

注：单实例或RAC都是此目录的sqlnet.ora文件

Oracle12c以下版本
SQLNET.ALLOWED_LOGON_VERSION= 8

Oracle12c及以上版本

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

两者区别

SQLNET.ALLOWED_LOGON_VERSION_SERVER：控制可以连接到12c数据库的客户端版本（client -->12c server ）

SQLNET.ALLOWED_LOGON_VERSION_CLIENT：控制12c数据库可以连到哪些版本的数据库（12c server -->其它版本dbserver），例如：控制通过DB LINK可连接到哪些版本的oracle库。

添加参数以后无需重启数据库或监听，但需要重置数据库用户密码，否则会报错
ORA-01017: 用户名/口令无效; 登录被拒绝

根据MOS文档 (ID 755605.1)，ORA-28040的错误需要在Oracle 用户（非grid用户）的sqlnet.ora 文件中添加：
SQLNET.ALLOWED_LOGON_VERSION=8
或者使用更高版本的客户端。
但实际上，根据MOS文档(ID 2111876.1)， 在Oracle 12c 以后的版本，
SQLNET.ALLOWED_LOGON_VERSION 参数已经弃用了，应该使用以下2个参数代替：
SQLNET.ALLOWED_LOGON_VERSION_SERVER = n
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = n

这里的n默认为11. 第一个参数是客户端连接到服务器的时候启作用，第二个是做为客户端去连接其它数据库的时候启作用。例如创建db link。

  1. 1
  2. 2
  3. 3