为什么oracle standby database需要standby log? 为什么需要比主库online redo多一组?

很多时候我们在安装配置oracle dataguard时,需要在standby db 增加standby log, 但是有一些限制,如standby log file size和主库的online redo log size相同, standby log每个thread要比主库的online redo log要多1组,为什么需要standby log?为什么会有这样的要求呢?

,

Troubleshooting CRS start fail when Private IP using 169.254.*.* due to HAIP drop route table

Oracle RAC众所周知通常需要PUBLIC和Interconnect network两个网络,当然从12c使用FLEX ASM可以增加ASM network, 而interconnect network也就是private network在11g r2后可以配置多个网卡,引入了haip实现了private networkIP的HA, 以169.254.*.*的随机IP绑定在private network网卡上。169.254因为是一个保留网段, ORACLE RAC在获取HAIP时有判断是否已使用防止IP冲突,但是最近一有个客户RAC一个节点CRASH后,private network无法互相ping通,结果发现private network安装初期使用的也是169.254.9.*。

Troubleshooting to connect DB failed with Ora-12541 due to adump trace Inode usage 100%

在kill crsd.bin后,listener上的public ip会立即自动消失, 当crsd.bin 自动启动后,public ip又会再次注册到listener上, 以上工作均有oraagent完成,无需任何操作,当 crsd 和 oraagent 进程自动重新启动时,应在 1 分钟左右注册丢失的 endpoint。 当然影响的不只是listener还有scan listener.

Troubleshooting to connect DB failed with ORA-12154 due to User Password have “@” (at mark) char

对于ORA-12nnn错误并不陌生,ORA-12154也是如配置tsnnames.ora 的alias错误常见,但是今天这个案例“若不是亲眼所见,我是万万不敢相信”, 在sqlplus中使用EZCONNECT尝试数据库时总时提示ora-12154, 而部分用户或jdbc应用使用相同用户可以正常链接,后分析密码中带了”@“符号导致。

Oracle、PostgreSQL、MySQL、MogDB/openGuass数据库比较系列(六): 分区键更新

今天在看MogDB的官方文档时看到一条关于分区的支持“Update操作时,支持数据跨分区移动(不支持Partition/SubPartition Key为List或Hash分区类型)” 也就是分区键更新, 那默认情况下Oracle、MySQL、PostGreSQL、MogDB(opengauss)横向对比一下表现,在rang\list partition update分区键在跨分区的表现。

Troubleshooting Oracle RAC node crash frequently on OEL 6.9 (Kernel panic)

有个客户的多套RAC节点总是频繁的重启, 故障并没有时间规律,环境Oracle RAC 11G r2 ON OEL 6.9 , 当然首先建议要排除一些硬件共性的配置,如是否电源电压不稳?机器所在区域空调是否差? 是否同一个宿主机上的VM? 硬件是否有报错? 一线同学说都排查过也做了些调整并未解决。 下面分享一些处理建议。

,

How to Recover Oracle Standby Database Using an Incremental Backup?

Oracle Standby database容灾库有时因为一些原因缺少了Primary database的archivelog未应用而中断同步,如备库归档目录使用率100%,或主库未传输删除了归档等。重新搭建DATAGUARD对于较大的数据库可能是费时费力,通常可以采用增量备份的方式,最近刚好是在处理一个ogg不同步的问题时。

,

Scripts: SQL 找出不连续的数值多种方法(gap)

像v$archived_gap一样有时需要找出一些序列中跳过的数值,如1,2,4,6,10,需要找出3,5,7-9,在数据库中使用SQL可以有多种方法,以下列出几种供参考

Troubleshooting Exadata to Non Exadata ORA-64307 HCC not supported

当从Oracle Exadata工程系统迁移到非Exadata环境时,或配置Datagurad时需要注意,有些功能时Exadata专属特性,如EHCC (hybrid columnar compression )的表对象在standby 查询或使用datapump迁移时会遇到下面的错误
ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage

Goldengate extract from Oracle ADG standby side ALO and ADG mode

Yes, you can extract the data from a Physical Standby database. There are actually two methods available in Oracle GoldenGate.,

1. Archived Log Only Mode – ALO Mode
2. Active Data Guard Mode – ADG Mode

,

Troubleshooting RMAN backup hang waiting for Autobackup, DB alert log ORA-00230

昨天遇到一个问题oracle 19c(19.10), 最近一段时间的RMAN备份任务都失败了,错误中有ORA-00230: operation disallowed: snapshot control file enqueue unavailable, 看着是cf的enqueue请求失败。 处理起来比较简单,找到blocker session就可以解决了, 之前的《Troubleshooting performance event ‘enq: CF – contention’》记录过一些分析方法,本次的案例当前还没有匹配的已知bug,这里简单的记录。

,

How to find full table scan SQL in Oracle,MySQL,Postgresql ?(数据库比较系列五)

Queries that do “full table scan” are the ones that don’t use indexes. However, it is more suitable to use a full table scan for small tables, and it will not cause performance problems. Or when the data on the large table is seriously skewed and a large proportion of data records need to be returned, a full table scan will also be better than an index scan.

Troubleshooting performance event ‘enq:CR – block range reuse ckpt’

enq:CR – block range reuse ckpt 出现该问题时分析等待链通常是前台进程等待CKPT进程在完成checkpoint, 通常是在DBWR进程在争用CPU或I/O 性能时,通常该event会非常短暂,如果该event已经在AWR dbtime中占据了较大占比时,需要引起关注。

Oracle、MySQL、PostGreSQL数据库比较系列(四): 可更新VIEW

最近在测试Oracle语法其它数据库的兼容性时,发现postgreq上对于TABLE上创建的简单view操作时还有个Oracle没有的东西:rule, 在openguass 2.1当通过view 做insert操作时会提示下面的错误,但是在postgreSQL 13.2和MoGdb2.1(支持updatable view)上并未报错:
“You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.”

WARNING OGG-06439 when table have “enable novalidate” Primary KEY

If a table has a PK constraint or a Unique Constraint that is Non-Validated or Invalid, OGG will by default not use those keys to uniquely identify a row. It will use all columns to uniquely identify a row.

Troubleshooting ORA-39002: invalid operation during datapump TZ version Difference

最近有个datapump跨数据库迁移数据时,提示”ORA-39002: invalid operation”错误, 排除过目录文件权限和语法兼容问题,后来发现是目标库的Timezone Version低于源库的TZ version. 需要升级目标库的TZ VERSION

How to config Keepalived VIP Auto Failover for MySQL?

MySQL master slave replication doesn’t provide any solution in case of master failure, in that scenarios we have to manually do the configuration changes to make the next available server as master. Use keepalived to configure VIP, the application uses VIP to connect to the database, when the node is unavailable, the VIP automatically switches to other nodes.

How to start MySQL slave/replica skip missing binlogs?

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Sometimes our MySQL master-slave environment has not been used for a long time,slave server is stopped for some reason for a few hours/days, and the master binlog has been automatically deleted. when you resume replication on the slave it fails with above error, and I don’t care about the loss of transactions between them in my test DB. Of course, the production environment may need to rebuild the slave database.

How to reset root password if forgotten in MySQL 5.7?

The MySQL root password is often used in practical applications. If you accidentally lose the root password, you can recover the MySQL root password through the methods in this article.

Scripts: 查找Oracle数据文件碎片和extents分布

最近有个客户的表空间使用率使用50%左右就出现了ora-1653,我们知道ora-165N是空间无法扩展,这么多的free空间还无法扩展,其中有可能是存在碎片,也就是数据文件中不连续的”洞”free space, 在申请一个比较大的extents时,无法匹配连续空间而失败, 你是否想过查看数据文件上的段分布?或表空间的碎片情况?或move 哪个对象可以让datafile resize更小?