coding

oracle 19c pdb操作命令

作者: bill
时间: 20200416
分类: oracle

查看数据库是否为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

Oracle 低版本客户端连接19C报错ORA-28040

作者: bill
时间: 20200416
分类: oracle

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。

oracle 19c 自动 SQL 执行管理与 SQL 隔离

作者: bill
时间: 20200416
分类: oracle

手动隔离-为SQL语句的执行计划创建隔离配置

SQL只要执行计划不变，每次都会被直接终止

使用以下任一包函数为SQL语句的执行计划创建隔离配置：DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_IDDBMS_SQLQ .CREATE_QUARANTINE_BY_SQL_TEX

DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => ’8vu7s907prbgr’, PLAN_HASH_VALUE => '3488063716');
END;
/

DECLARE
quarantine_config VARCHAR2(30);
BEGIN

quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID( SQL_ID => '152sukb473gsk');

END;
/
DECLARE

quarantine_config VARCHAR2(30);

BEGIN
quarantine_config:= DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT( SQL_TEXT => to_clob('select count(*) from emp'));
END;

手动隔离-在隔离配置中指定隔离阈值
可指定如下隔离阈值：
-CPU time
-Elapsed time
-I/O in megabytes
-Number of physical I/O requests
-Number of logical I/O requests

BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(

       QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 
       PARAMETER_NAME => 'CPU_TIME', 
       PARAMETER_VALUE => '5');

DBMS_SQLQ.ALTER_QUARANTINE(

      QUARANTINE_NAME => 'SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 
       PARAMETER_NAME => 'ELAPSED_TIME', PARAMETER_VALUE => '10');

END;

Git查看文件的历史修改记录

作者: aaron
时间: 20200326
分类: git

# 查看修改日志
git log [filename]

# 查看内容修改日志
git log -p [filename]

利用curl监控网络

作者: aaron
时间: 20200323
分类: linux

curl -o /dev/null -s -w %{http_code}:%{time_connect}:%{time_starttransfer}:%{time_total} https://www.elooy.com;

-o /dev/null 丢弃输出
-s 静默模式
-w 格式化输出, 变量有url_effective, http_code, http_connect, time_total,time_namelookup, time_connect, time_appconnect, time_pretransfer, time_redirect,time_starttransfer, size_download, size_upload, size_header, size_request, speed_download, speed_upload, content_type,num_connects, num_redirects, redirect_url, ftp_entry_path, ssl_verify_result

# 每隔5秒访问一次
while true; do curl -o /dev/null -s -w %{http_code}:%{time_connect}:%{time_starttransfer}:%{time_total} https://www.elooy.com; echo;sleep 5;done

oracle 19c pdb操作命令

CON_NAME

SYS_CONTEXT('USERENV','CON_NAM

SYS_CONTEXT('USERENV','CON_NAM

Oracle 低版本客户端连接19C报错ORA-28040

oracle 19c 自动 SQL 执行管理与 SQL 隔离

Git查看文件的历史修改记录

利用curl监控网络

最新文章

分类

归档

其它