准确查询表空间使用情况

11g以前,一般查询表空间都是通过DBA_DATA_FILE和DBA_FREE_SPACE两个视图来查询。但从11g开始,如果你习惯看EM的话,会发现表空间使用率跟我们以前的sql查出来的结果有出入。这是因为EM采用了新的视图dba_tablespace_usage_metrics,针对自动扩展的表空间,得出来的结果就会不一样。 dba_tablespace_……

阅读全文

Oracle Block Checksum

引入一段文档对于checksum的说明 A number calculated by the database from all the bytes stored in a data or redo block. If the DB_BLOCK_CHECKSUM initialization parameter is enabled, then the database calculates the checksum for every data file or online redo log block and stores it in the block header when writing to disk. The database can use the checksum value to check consistency. 数据块中的checksum值储存在每个块的第16个字节,可以通过bbed查看 BBED> set dba 5,131 DBA 0x01400083 (20971651 5,131) BBED> p kcbh struct kcbh, 20 bytes @0 ub1 type_kcbh @0 0x06 ub1 frmt_kcbh @1 0xa2 ub1 spare1_kcbh @2 0x00 ub1 spare2_kcbh @3 0x00 ub4 rdba_kcbh @4 0x01400083 ub4 bas_kcbh @8 0x0013f022 ub2 wrp_kcbh @12 0x0000 ub1 seq_kcbh @14 0x01 ub1 flg_kcbh @15 0x04 (KCBHFCKV) ub2 chkval_kcbh @16 0x2dd5 ub2 spare3_kcbh @18 0x0000 fl……

阅读全文

Pipelined Table Functions

TABLE函数 table function是一个用户自定义的PL/SQL函数,它能返回一个结果集(关联数组、嵌套表),你能像查询普通表一样查询它,通过SELECT语句里的TABLE表达式 SELECT * FROM TABLE(table_function_name(parameter_list)) table函数能将行的集合作为入参,也就是说它的入参可以是嵌套表、数组或者游标等,因此table函数tf1的结果集可以作为tf2的入参,同理函数tf2的结果集也可以作为tf3的……

阅读全文

11gR2 Clusterware and Grid Home – What You Need to Know

这篇文章详细的介绍了11g clusterware整体结构,记录一下 DETAILS 11gR2 Clusterware Key Facts 11gR2 Clusterware is required to be up and running prior to installing a 11gR2 Real Application Clusters database. The GRID home consists of the Oracle Clusterware and ASM. ASM should not be in a separate home. The 11gR2 Clusterware can be installed in “Standalone” mode for ASM and/or “Oracle Restart” single node support. This clusterware is a subset of the full clusterware described in this document. The 11gR2 Clusterware can be run by itself or on top of vendor clusterware. See the certification matrix for certified combinations. Ref: Note: 184875.1 “How To Check The * Certification Matrix for Real Application Clusters” The GRID Home and the RAC/DB Home must be installed in different locations. The 11gR2 Clusterware requires a shared OCR files and voting files. These can be stored on ASM or a cluster filesystem. The OCR is backed up automatically every……

阅读全文

12cR2 使用 opatchauto 安装 GI PSU

测试环境:

oracle 12.2.0.1 rac 2 nodes

环境配置:

type homepath owner version shared
GI /u01/app/12.2.0/grid grid 12.2.0.1 no
DB1 /u01/app/oracle/product/12.2.0/dbhome_1 oracle 12.2.0.1 no
DB2 /u01/app/oracle/product/12.2.0/dbhome_1 oracle 12.2.0.1 no

下载最新版本的opatch 6880880

解压到GI 和 DB 目录下面,两个节点都要操作

使用grid用户

[grid@rac1 grid]$ unzip /home/oracle/p6880880_122010_Linux-x86-64.zip -d .

使用oracle用户

[oracle@rac1 dbhome_1]$ unzip /home/oracle/p6880880_122010_Linux-x86-64.zip -d .

patch 文件

[root@rac2 oracle]# unzip p28183653_122010_Linux-x86-64.zip

chmod -R 775 28183653

12cR2版本就不需要以前的ocm 响应文件了

创建wallet

如果使用非root用户,则必须要生成对应用户的wallet

命令参数

[grid@rac1 bin]$ pwd
/u01/app/12.2.0/grid/OPatch/auto/core/bin

[grid@rac1 bin]$ ./patchingWallet.sh -help
Usage: run patchingWallet.cmd or patchingWallet.sh with the following parameters:
   [ options ] { -create | -delete | -list } alias ...
    Supported alias format ::  >userName>:>hostName>{:}{/}>protocol>
    Examples : 
            abc:xyz:ssh
            abc:xyz/ssh

       -walletDir - >Path to wallet directory.>
       [-password - If you should be prompted for the wallet password.]
       [-useStdin - >Read passwords from stdin rather than attempting to use Console.>]
            [-map - >Map name within wallet.>]
          [-force - >Force overwrite of existing aliases.>]
           [-list - List wallet]
        [-listmap - List maps available in  wallet]
         [-create - Create alias]
         [-delete - Delete alias]
   [-log_priority - >Log priority. Supported values: OFF, SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST, ALL.>]
            [-log - >Log file. The value can be a filename or one of the special values STDOUT, STDERR, or DISABLE.>]
           [-help - >Displays usage.>]


使用grid用户:

[grid@rac1 bin]$ ./patchingWallet.sh -walletDir /u01/app/grid/wallet -create grid:rac1:ssh grid:rac2:ssh -log /u01/app/grid/wallet/wallet.log
Session log file is /u01/app/grid/wallet/wallet.log
Enter Password for grid:rac1:ssh:
Confirm Password for grid:rac1:ssh:
Enter Password for grid:rac2:ssh:
Confirm Password for grid:rac2:ssh:

采用ssh协议 输入对应的密码即可

检查补丁冲突

[grid@rac1 bin]$ /u01/app/12.2.0/grid/OPatch/opatchauto apply /home/oracle/28183653 -oh /u01/app/12.2.0/grid -analyze -wallet /u01/app/grid/wallet

OPatchauto session is initiated at Wed Sep 26 15:28:29 2018

System initialization log file is /u01/app/12.2.0/grid/cfgtoollogs/opatchautodb/systemconfig2018-09-26_03-28-38PM.log.

Session log file is /u01/app/12.2.0/grid/cfgtoollogs/opatchauto/opatchauto2018-09-26_03-29-24PM.log
The id for this session is SQGY
[sudo] password for grid: grid is not in the sudoers file.  This incident will be reported.

OPatchAuto failed.

OPatchauto session completed at Wed Sep 26 15:29:28 2018
Time taken to complete the session 0 minute, 59 seconds

 opatchauto failed with error code 42

有报错,因为我这里采用的grid用户 提示grid不是sudoer 添加grid到sudo

[root@rac1 oracle]# whereis sudoers
sudoers: /etc/sudoers /etc/sudoers.d /usr/share/man/man5/sudoers.5.gz

Allow root to run any commands anywhere
root    ALL=(ALL)       ALL
grid ALL=(ALL)       ALL
……

阅读全文

12c new features: OPatch Automation Tool – opatchauto

从12c开始,在集群GRID/RAC环境下,通过root用户使用opatchauto命令安装patch,11g 是opatch auto opatchauto能同时对GI集群打补丁,包括grid目录和db目录,能很简单的对单机或rac进行补丁操作。 支持的平台 Oracle Solaris on x86-64 (64-bit) Linux x86-64 Oracle Solaris on SPARC (64-bit) IBM AIX on POWER Systems (64-bit) HP-UX Itanium Linux 准备工作 确定你的opatchauto目录以及确保其为最新版本 设置好正确的……

阅读全文

Oracle Cluster Time Synchronization Service (CTSS)

以前我安装rac的同步都是用的ntp,后来oracle引入ctss,以下为官方介绍 The Cluster Time Synchronization Service (CTSS) is installed as part of Oracle Clusterware. By default the CTSS runs in observer mode if it detects a time synchronization service or a time synchronization service configuration, valid or broken, on the system. If CTSS detects that there is no time synchronization service or time synchronization service configuration on any node in the cluster, then CTSS goes into active mode and takes over time management for the cluster 简单来说 就是ctss是作为grid的一部分安装完成,会自动检查服务器上是否有安装其他时间同步服务。如果没有那么ctss就会自动生……

阅读全文

OEL7上配置dns服务

搭建rac的时候如果选择多个scan ip 则需要考虑配置dns server,多个虚拟机也可以考虑作为公用的dns服务器 安装相关packages [root@xb ~]# yum install bind* -y Loaded plugins: refresh-packagekit Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package bind.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed ---> Package bind-chroot.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed ---> Package bind-devel.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed ---> Package bind-dyndb-ldap.x86_64 0:2.3-8.el6 will be installed ---> Package bind-libs.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed ---> Package bind-sdb.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed ---> Package bind-utils.x86_64 32:9.8.2-0.68.rc1.el6_10.1 will be installed --> Finished Dependency Resolution 主要文件 /etc/named #named目录 /etc/named.conf #主配置文件 /etc/rc.d/init.d/named #BIND开机自动时启动的脚本 /usr/sbin/named #n……

阅读全文

在vmware linux 7.5安装oracle 12c rac

作为测试目的安装,环境如下: OS: OEL 7.5 64-bit (12c 已经不支持32位系统) db verions: 12.2 node: 2 (rac1.oracle.com,rac2.oracle.com) IP规划: 192.0.2.11 rac1 rac1.oracle.com 192.0.2.13 rac1-vip 192.0.2.12 rac2 rac2.oracle.com 192.0.2.14 rac2-vip 192.0.2.15 rac-scan 192.168.2.11 rac1-priv 192.168.2.12 rac2-priv 安装介质 linuxx64_12201_database.zip linuxx64_12201_grid_home.zip oracle linux 7.5.iso Oracle linux 7.5安装 操作步骤略过。 提下vmware 配置共享磁盘, 在vmx文件里添加如下参数 disk.enableUUID = "TRUE" disk.locking="FALSE" diskLib.dataCacheMaxSize = "0" diskLib.dataCacheMaxReadAheadSize = "0" diskLib.DataCacheMinReadAheadSize = "0" diskLib.dataCachePageSize = "4096" diskLib.maxUnsyncedWrites = "0" scsi1:1.SharedBus="Virtual" scsi1:2.SharedBus="Virtual" scsi1:3.SharedBus="Virtual" scsi1:4.SharedBus="Virtual" 配置yum mkdir /opt/media createrepo . yum clean all yum list [myrepo] name=myrepo baseurl=file:///opt/media gpgcheck=0 enabled=1 必备参数 用户 组 yum install oracle-database-server-12cR2-preinstall [root@rac1 yum.repos.d]# id oracle uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba) 可以看到12c默认建了一些……

阅读全文

oracle用户密码错误导致被锁

碰到一个数据库用户总是被锁住 SQL> select name,lcount from user$ where name='WOLF'; NAME LCOUNT ------------------------------ ---------- WOLF 149 从解锁到现在又失败了149次 listner日志里没有可用的内容,被锁时间附近显示的ip配置的密码均正确 开始的想法是做审计,数据库之前已经关闭了审计 要重新开启比较麻烦,需要停机,于是采用触发器的方式 create or replace trigger logon_denied_to_alert after servererror on database declare message varchar2(256); IP varchar2(15); v_os_user varchar2(80); v_module varchar2(50); v_action varchar2(50); v_pid varchar2(10); v_sid number; v_program varchar2(48); v_client_id VARCHAR2(64); v_host varchar2(60); begin IF (ora_is_servererror(1017)) THEN -- get IP for remote connections: if sys_context('userenv','network_protocol') = 'TCP' then IP := sys_context('userenv','ip_address'); end if; select distinct sid into v_sid……

阅读全文