Your Site Title

Oracle

Oracle数据库可以单机和集群实现, 集群也分为高可用集群和负载均衡集群(以负载均衡集群为主) Oracle集群是一组协同工作的服务器或节点，为Oracle数据库和应用程序提供高可用性和可伸缩性。 服务器或节点通过共享网络和存储基础设施连接，允许它们作为单个系统一起工作。 集群配置提供跨多个节点分布工作负载的能力，允许在节点故障时改进性能和自动故障转移。

Oracle Grid Infrastructure(GI)

Grid Infrastructure (简称GI)是11gR2版本新出现的安装包，使用独立的grid用户进行安装。 如果要使用Clusterware、ASM、ACFS、ASM动态卷等功能时都需要先安装此包。 Grid Infrastructure封装了Clusterware集群软件和ASM存储软件，此外， 对ASM的管理也同样独立出来，新建了ASMADMIN、ASMDBA和ASMOPER 3个系统组来完善对ASM的管理。 11g 版本中的ASM能够存放包括OCR和Votedisk在内的文件。

包含以下组件:

• ASM：Automatic Storage Management; oracle
• ACFS：ASM Cluster File System; 工具
• ACFS snapshot; spa
• Oracle Clusterware; 操作系统
• Oracle Restart;

SCAN ip

Oracle 11gR2 中，引入了SCAN（Single ClientAccess Name）的特性 为了简化客户端配置, 如果oracle集群新加一个节点, 那么所有客户端需要新加一个vip配置. 使用SCAN 客户端就不用加配置

srvctl status scan // 查看状态
srvctl config scan // 查看scan配置

打补丁

shutdown immediate

$ORACLE_HOME/OPatch   打补丁工具     
OPatch/opatch apply

ZOP-51: The patch location is not valid for apply.
需要在补丁目录(是数字代码目录) 有etc files子目录

chown -R oracle:oinstall $ORACLE_HOME/bin

startup


// 补丁回滚
1、关闭oracle相关服务
2、opatch rollback -id 补丁ID

常用语句

• 查看表空间: select * from dba_tablespaces; select * from dba_data_files; SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES;

• 查看是否支持分区: select * from v$option; Partitioning

• 查看磁盘组: SELECT * FROM V$ASM_DISKGROUP; SELECT * FROM V$ASM_DISKGROUP WHERE NAME = ‘DATA’;

• 查看service_name show parameter service;

• 查看数据库状态 select status from v$instance;

• 查看表空间占用
  SELECT A.TABLESPACE_NAME, A.FILENUMBER, ROUND(A.TBSCURRENTSIZEINMB/1024, 2) TOTAL_SIZE_G, ROUND(B.SEGMENTSIZEINMB/1024, 2) USED_SIZE_G, ROUND((A.TBSCURRENTSIZEINMB-B.SEGMENTSIZEINMB)/1024, 2) FREE_SIZE_G, ROUND(NVL(B.SEGMENTSIZEINMB, 0) / A.TBSCURRENTSIZEINMB, 4) * 100 CAPACITYUSED FROM (SELECT TABLESPACE_NAME, COUNT(1) FILENUMBER, SUM(BYTES) / 1024 / 1024 TBSCURRENTSIZEINMB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 SEGMENTSIZEINMB FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) –AND (A.TABLESPACE_NAME LIKE ‘%ODSREP%’ OR A.TABLESPACE_NAME LIKE ‘%DMREP%’ OR A.TABLESPACE_NAME LIKE ‘%BLREP%’) –AND B.SEGMENTSIZEINMB / A.TBSCURRENTSIZEINMB < 0.5 –AND A.TBSCURRENTSIZEINMB > 10 * 1024 –AND a.tablespace_name LIKE ‘BLAP%’ AND A.TABLESPACE_NAME NOT LIKE ‘%UNDO%’ ORDER BY CAPACITYUSED;

• 查看表空间占用百分比 SELECT A.TABLESPACE_NAME “表空间名”, TOTAL / 1024 / 1024 “表空间大小单位M”, FREE / 1024 / 1024 “表空间剩余大小单位M”, (TOTAL - FREE) / 1024 / 1024 “表空间使用大小单位M”, ROUND((TOTAL - FREE) / TOTAL, 4) * 100 “使用率 [[%]]”FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

数据库库名：数据库的标识(一台机器可以装多个数据库,库名是每个数据库的标识)
show parameter db_name select name,dbid from v$database; 数据库服务名:数据库对于外部客户端的名称(tnsnames.ora中的SERVICE_NAME) show parameter service_name
数据库实例名:用户访问数据库的媒介 show parameter instance
select instance_name from v$instance; 数据库sid:对于操作系统数据库实例的标识

select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='用户名';
select sequence_name, last_number, min_value, max_value, increment_by from user_sequences;
select sequence_name, last_number, min_value, max_value, increment_by from user_sequences where sequence_name='SEQ_SYS_USER_TO_ROLE';

select max(sid) from TB_HBCOM_SEND_ORDER;
select sequence_name, last_number, min_value, max_value, increment_by from user_sequences where sequence_name='SEQ_HBCOM_SEND_ORDER';

shutdown immediate
startup


SELECT constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints WHERE table_name = 'TB_SYS_ERROR_LOG' and owner = 'ZHENGWU';

初始化

1. 创建表空间

   // 本地 create tablespace testhbga datafile ‘/u01/app/oracle/oradata/XE/testhbga.dbf’ size 100m autoextend on next 10m; create temporary tablespace testhbga_temp tempfile ‘/u01/app/oracle/oradata/XE/testhbgatemp.dbf’ size 50m autoextend on next 10m maxsize 20480m extent management local; create user testhbga identified by t1e2s3t4hbga default tablespace testhbga;

   grant connect,resource,dba to testhbga;

   // 集群指定共享存储 Create an ASM disk group: CREATE DISKGROUP EXTERNAL REDUNDANCY DISK '', ''...;

   Create a tablespace in the ASM disk group: CREATE TABLESPACE DATAFILE '+' SIZE [AUTOEXTEND ON] [NEXT ] MAXSIZE UNLIMITED;

   Create a temporary tablespace in the ASM disk group: CREATE TEMPORARY TABLESPACE TEMPFILE '+' SIZE [AUTOEXTEND ON] [NEXT ] MAXSIZE UNLIMITED;

   Create a user in the ASM-enabled database and assign them the tablespace and temporary tablespace: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ;

   ALTER USER QUOTA UNLIMITED ON ; // 分配配额 grant unlimited tablespace to ;

   // 湖北政务 CREATE TABLESPACE ZHENGWU_DATA DATAFILE ‘+DATA’ SIZE 10240M AUTOEXTEND ON NEXT 8192M MAXSIZE UNLIMITED; CREATE TEMPORARY TABLESPACE ZHENGWU_TEMP TEMPFILE ‘+DATA’ SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED; CREATE USER zhengwu IDENTIFIED BY Zhengwu#456 DEFAULT TABLESPACE ZHENGWU_DATA TEMPORARY TABLESPACE ZHENGWU_TEMP;

   alter tablespace ZHENGWU_DATA add datafile ‘+DATA’ size 10240M AUTOEXTEND on next 8192m maxsize UNLIMITED; select sequence_name, last_number, min_value, max_value, increment_by from user_sequences where sequence_name=’SEQ_HBCOM_SEND_ORDER’;

   connect resource create table create view create trigger create procedure create sequence create rollback segment

   select * from dba_directories where directory_name=”DATA_PUMP_DIR”; /u01/app/oracle/12c/rdbms/log impdp zhenwu/Zhengwu#456 directory=DATA_PUMP_DIR dumpfile=zhengwu23.dmp logfile=zhengwu23.log impdp username/password directory=dump_dir dumpfile=mydb_%U.dmp full=y;

   table_exists_action 1) skip：默认操作 2) replace：先drop表，然后创建表，最后插入数据 3) append：在原来数据的基础上增加数据 4) truncate：先truncate，然后再插入数据

   %U 是占位符, 表示从01到99

   步骤一： 删除user

   drop user ×× cascade

   说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

   步骤二： 删除tablespace

   DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES; // 删除表空间(临时表空间一样)

’+’指定盘符为ASM

1. 创建用户

2. 授权用户

   grant connect to testdb; /链接数据库的权限/ grant create any table to testdb;/创建表的权限/ grant create any index to testdb;/创建索引的权限/ grant create any view to testdb;/创建视图的权限/

   –授予dba权限 grant dba to userName –回收dba权限 revoke dba from userName –授予用户登录数据库的权限： grant create session to userName; –授予用户操作表空间的权限： grant unlimited tablespace to userName; grant create tablespace to userName; grant alter tablespace to userName; grant drop tablespace to userName; grant manage tablespace to userName; –授予用户操作表的权限： grant create table to userName; (包含有create index权限, alter table, drop table权限) –授予用户操作视图的权限: grant create view to userName; (包含有alter view, drop view权限) –授予用户操作触发器的权限： grant create trigger to userName; (包含有alter trigger, drop trigger权限) –授予用户操作存储过程的权限： grant create procedure to userName;(包含有alter procedure, drop procedure 和function 以及 package权限) –授予用户操作序列的权限： grant create sequence to userName; (包含有创建、修改、删除以及选择序列) –授予用户回退段权限： grant create rollback segment to userName; grant alter rollback segment to userName; grant drop rollback segment to userName; –授予用户同义词权限： grant create synonym to userName;(包含drop synonym权限) grant create public synonym to userName; grant drop public synonym to userName; –授予用户关于用户的权限： grant create user to userName; grant alter user to userName; grant become user to userName; grant drop user to userName; –授予用户关于角色的权限： grant create role to userName; –授予用户操作概要文件的权限 grant create profile to userName; grant alter profile to userName; grant drop profile to userName; –允许从sys用户所拥有的数据字典表中进行选择 grant select any dictionary to userName;

   回收权限 REVOKE create any index FROM testdb

asm

asmcmd
connect asm

Oracle 体系结构

• non-container database

• multitenant container database

• Database Server: 对外数据库服务
  • Database instance: 管理共享内存和服务进程
  • Database 物理文件, datafiles和systemfiles, 与database instance一对一
• Oracle Real Application Clusters(Oracle RAC): 是GI的一种实现
  • Database instance: 多个
  • Database: 一个

jdbc

dbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=IP1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=IP2)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME=服务名)))

jdbc.url=jdbc:oracle:thin:@//219.140.211.203:11521/XE
jdbc.username=testhbga
jdbc.password=t1e2s3t4hbga

格式一: Oracle JDBC Thin using an SID

jdbc:oracle:thin:@host:port:SID 

格式二: Oracle JDBC Thin using a ServiceName

jdbc:oracle:thin:@//host:port/service_name 

格式三：Oracle JDBC Thin using a TNSName

jdbc:oracle:thin:@TNSName 
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.16.91)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))
 
//RAC集群模式tns:(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.6.10)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.128.6.11)(PORT = 1521)) (LOAD_BALANCE = yes)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))

jdbc.url=jdbc\:oracle\:thin\:@(DESCRIPTION \=(ADDRESS_LIST \=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=10.162.16.48)(PORT\=1521))(ADDRESS\=(PROTOCOL\=TCP)(HOST\=10.162.16.49)(PORT\=1521)))(CONNECT_DATA\=(SERVER\=DEDICATED)(SERVICE_NAME\=custdb)))
jdbc.username=zhengwu
jdbc.password=Zhengwu#456

日志

// 停止trace日志
alter system set trace_enabled=false;
// 查询alert 和 trace日志地址
select * from v$diag_info;

alter system set trace_enabled=true;

配置oracle 白名单

# 没有sqlnet.ora文件  可以从admin/simple/sqlnet.ora中拿
$ORACLE_HOME/network/admin/
#cat $ORACLE_HOME/network/admin/sqlnet.ora
tcp.validnode_checking=yes
tcp.invited_nodes=(192.168.100.28,127.0.0.1)
#tcp.excluded_nodes=(192.168.100.1)

1）tcp.validnode_checking 设置为yes，启用策略
2）tcp.invited_nodes ：允许访问的IP或者主机名，也可以叫做白名单
3）tcp.excluded_nodes：不允许访问的主机名或者IP
一般来说，tcp.invited_nodes 及tcp.excluded_nodes，我们配置配置一项即可

lsnrctl stop
lsnrctl start
lsnrctl reload

fgrep "14-JUL-2022 " listener.log|fgrep "establish" |awk -F '*' '{print $1 " " $3} '|awk -F '(' '{print $4}'|awk -F "=" '{print $2}' |awk -F ")" '{print $1}'|sort -n |uniq

删除session

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'ZHENGWU';
alter system kill session '95,27591';

删除锁表session

SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE B.OBJECT_ID = A.OBJECT_ID;
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME FROM V$LOCKED_OBJECT A, V$SESSION B WHERE A.SESSION_ID = B.SID ORDER BY B.LOGON_TIME;
ALTER SYSTEM KILL SESSION '15368,19257';

删除表

BEGIN
  FOR c IN ( SELECT table_name FROM user_tables WHERE table_name LIKE 'EXT_%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name;
  END LOOP;
END;

Reference

• Oracle的grid及ASM
• 【Oracle学习01】Oracle12c图形化安装及问题处理
• 【Oracle学习02】CentOS6静默安装Oracle12c
• 【Oracle学习03】DBCA创建及配置数据库
• 【Oracle学习04】Oracle Grid Infrastructure (GI)
• 【Oracle学习05】Oracle DDL和模式对象
• 【Oracle学习06】DML与并发性,UNDO,死锁
• Oracle Grid Infrastructure包含哪些组件及其新特性？
• Oracle12c 中RAC功能增强新特性之ASM&Grid
• 详解Oracle数据库表空间设计–ASM/BFT/OMF综合使用
• Understanding the Oracle Database 12cR2 Technical Architecture
• Oracle Real Application Clusters – Overview
• Oracle11g RAC集群启动流程
• Oracle 19c 技术架构
• ORACLE ASM环境中如何删除和创建表空间（数据文件）
• Oracle临时表空间作用
• oracle的用户权限有哪些
• ORA-39171: 作业出现可恢复的等待
• ORA-01658: 无法为表空间XXX段创建 INITIAL 区
• Oracle 设置白名单访问控制

This site is open source. Improve this page.