My Certification

Oracle Database 10g OCP
Oracle Database 11g OCP
Oracle Database 12c OCP
Oracle GoldenGate 10 OCS
Oracle 11gR2 RAC OCE
MySQL 5.6 Database OCP
Novell SUSE Linux 10 CLA
IBM Power System AIX 7.1



MySQL 8.0 默认参数值变更列表

解析关于Oracle Database 11.2.0.4在2019.6.23自动调整current maximun scn limit后10.2.0.5.0解决方案

The SCN is a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN. The value of a SCN is the logical point in time at which changes are made to a database. This number is utilized by Oracle to log the changes made to the database.

The database uses SCNs to query and track changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction typically have the same SCN. When a transaction commits, the database records an SCN for this commit. Multiple transactions that commit at the same time may share the same SCN.

Given that there is an upper limit, it is important that any given Oracle Database does not run out of available SCNs. The Oracle Database uses a time based rationing system to ensure that this does not happen. 

 

SCN是一个持续增长的sequence,最大值2^(6*8)=281,474,976,710,656.
Oracle使用database's current maximum SNC limit来限制当前最大的SCN值。使用自1988.01.01 00:00:00到当前时间的秒数*16,384来得到当前可用的最大SCN。
select (((to_number(to_char(sysdate, 'YYYY')) – 1988) * 12 * 31 * 24 * 60 * 60) +
       ((to_number(to_char(sysdate, 'MM')) – 1) * 31 * 24 * 60 * 60) +
       (((to_number(to_char(sysdate, 'DD')) – 1)) * 24 * 60 * 60) +
       (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
       (to_number(to_char(sysdate, 'MI')) * 60) +
       (to_number(to_char(sysdate, 'SS')))) * (16 * 1024)
  from dual;

  
  
Oracle Database 11.2.0.4将在2019.6.23自动调整current maximun scn limit,这可能导致10.2.0.5与11.2.0.4的数据库的dblink无法使用。
the unpatched database(10.2.0.5.0) will have a lower SCN rate or lower current max SCN limit.
The newer or patched databases(11.2.0.4.0) will have higher SCN rate or higher current max SCN limit.
Therefore, there can be situations when the patched database is at a higher SCN level (due to the higher SCN rate allowance) and the unpatched database is at a much lower SCN level (due to lower SCN rate allowance).
When you open a dblink between these two databases, it has to sync the SCN levels of both the databases and if the SCN increase needed in the unpatched database for this sync is beyond it’s allowed SCN rate or current max SCN limit, then the dblink connection cannot be established.
This situation will not rise immediately after the change, but can potentially arise any time after 23rd June 2019.
You should be aware about potential dblink issues in future and consider about upgrading the databases or not using dblinks with newer versions of databases . If you continue to have such database links after June 2019, you may get run-time errors during database link operations (as explained above) and you would need to disconnect those database links at that time.
Based on customer feedback, we are providing patch on 10.2.0.5. 10g databases requires 10.2.0.5.171017PSU and Patch 14121009. Please note that these patches require extended support license

 

SCN Headeroom:即当前可用最大SCN与当前实际SCN之间的差异值(单位天)


select ((sysdate – to_date('1988/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')) * 24 * 60 * 60 *
       (16 * 1024) – dbms_flashback.get_system_change_number) /
       (16 * 1024 * 60 * 60 * 24)
  from dual;



  


CHECK RESULT:(SCN Headeroom>=62为A,<10为C)


A:SCN health is good at this time. The majority of databases are expected to fall into this category


B:SCN health is low. You are recommended to ensure that your database is patched to the current level as indicated by "My Oracle Support" recommendations immediately rather than waiting for your normal maintenance window. Once patched the headroom is expected to increase over time


C:SCN health is low. This database appears to have a high rate of SCN increase. You are recommended to ensure that your database is patched to the current level as indicated by "My Oracle Support" recommendations immediately


针对10.2.0.5.0的库,oracle发布了10.2.0.5.171017PSU(26493118)(需要扩展支持服务)及patch 14121009的补丁.(14121009先决条件补丁程序:16619894 DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013))影响SCN的主要是14121009这个补丁,可以基于PSU 10.2.0.5.12打14121009这个补丁解决PSU 10.2.0.5.171017无法获取的问题。 

 

 

Bug 18411339 – Low performance or ORA-1220 for query on V$ARCHIVE_GAP on 11.2.0.4 or later release

Low performance or ORA-1220 thrown for query on V$ARCHIVE_GAP on 11.2.0.4 or later release
due to execution plan on V$ARCHIVE_GAP using MERGE JOIN CARTESIAN internally involving  
X$KCCLH, X$KCCLH, X$KCCAL.

dggap

 

Versions confirmed as being affected
12.1.0.2 (Server Patch Set)
12.1.0.1 (Base Release)
11.2.0.4
 
The fix for 18411339 is first included in
12.2.0.1 (Base Release)
 
Use this reconstructed query directly:
select USERENV('Instance'), high.thread#, low.lsq, high.hsq
 from
  (select a.thread#, rcvsq, min(a.sequence#)-1 hsq
   from v$archived_log a,
        (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
           from v$log_history lh, v$database_incarnation di
          where lh.resetlogs_time = di.resetlogs_time
            and lh.resetlogs_change# = di.resetlogs_change#
            and di.status = 'CURRENT'
            and lh.thread# is not null
            and lh.resetlogs_change# is not null
            and lh.resetlogs_time is not null
         group by lh.thread#, lh.resetlogs_change#
        ) b
   where a.thread# = b.thread#
     and a.resetlogs_change# = b.resetlogs_change#
     and a.sequence# > rcvsq
   group by a.thread#, rcvsq) high,
 (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
   from
     (select thread#, min(sequence#)+1 lsq
      from
        v$log_history lh, x$kccfe fe, v$database_incarnation di
      where to_number(fe.fecps) <= lh.next_change#
        and to_number(fe.fecps) >= lh.first_change#
        and fe.fedup!=0 and bitand(fe.festa, 12) = 12
        and di.resetlogs_time = lh.resetlogs_time
        and lh.resetlogs_change# = di.resetlogs_change#
        and di.status = 'CURRENT'
      group by thread#) lh_lsq,
     (select thread#, max(sequence#)+1 lsq
      from
        v$log_history
      where (select min( to_number(fe.fecps))
             from x$kccfe fe
             where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
      >= next_change#
      group by thread#) srl_lsq
   where srl_lsq.thread# = lh_lsq.thread#(+)
  ) low
 where low.thread# = high.thread#
 and lsq < = hsq
 and hsq > rcvsq;

MySQL 8.0.15 InnoDB Cluster Setup

Prepare hosts
Depending on version of OS their might be a need to tweak port openings, a full guide on what port you need to open is available here.

myport

Continue reading MySQL 8.0.15 InnoDB Cluster Setup

Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)

psu1

 

psu2

 

psu3

Oracle GoldenGate 12C Release 3(12.3.0.1.0) Microservices Architecture

Microservices Architecture is a method or approach to developing applications where an application is deployed as a suite of independently deployed small modular services.
Each module supports a specific business goal and uses a simple, light weight  and well-defined interface to communicate with other sets of services.
Oracle GoldenGate Microservices Architecture (MA)  is a similar architecture based on REST APIs which enable us to configure, monitor, and manage Oracle GoldenGate services using a web-based user interface.
In Oracle GoldenGate 12.3, we have two architectures available for deploying GoldenGate – the (original) Classic Architecture and the new Microservices Architecture.

 

Microservices Architecture in Oracle GoldenGate is comprised of five main components:
Service Manager
Administration Server
Distribution Server
Receiver Server
Performance Metrics Server

MA2

Continue reading Oracle GoldenGate 12C Release 3(12.3.0.1.0) Microservices Architecture

WARNING: Granules of pga_aggregate_target 102 cannot be more than memory_target (104) – sga_target (0) or min_sga (5)

DB实例的alert日志显示以下告警

WARNING: Granules of pga_aggregate_target 102 cannot be more than memory_target (104) – sga_target (0) or min_sga (5)

 

MOS给的解决方案如下

To implement a solution for unpublished Bug:8813366, please execute any of the below alternative solutions:

  • Upgrade to 12.1.

OR

  • Apply patchset 11.2.0.2 in which unpublished Bug:8813366 is fixed.

OR

  • Apply patchset update release (PSU) 11.2.0.1.1 in which unpublished Bug:8813366 is fixed.

OR

OR

  • Use one of following workarounds:

    • Reduce the number of shared pool subpools by setting the init.ora parameter
      "_kghdsidx_count"=2

      OR
       

    • Reduce granule size with init.ora parameter:
      "_ksmg_granule_size"=16777216

      NOTE: These require an outage of the database.

 

 

This warning message is raised because the MEMORY_TARGET/MEMORY_MAX_TARGET and SGA_MAX_SIZE/SGA_TARGET parameters are set or default to minimum values, and that the difference between the parameter values is less than the required PGA_AGGREGATE_TARGET value.  In Oracle 11g the MEMORY_TARGET parameter defines the total amount of memory that Oracle can use, which incorporates both SGA and PGA areas.  Therefore if these are set the same as the SGA_MAX_SIZE/SGA_TARGET parameters or the difference between them is too small, then this leaves no memory available for the PGA, and hence this warning message is raised.

Ensure that the MEMORY_TARGET/MEMORY_MAX_TARGET parameter settings are set to at least the sum of SGA_MAX_SIZE/SGA_TARGET plus the PGA_AGGREGATE_TARGET parameter.

Oracle 11.2.0.4 RAC to Single Instance DataGuard Switchover

Checked all the necessary parameters on both sides.
On Primary
SQL> select name, value
  2    from v$parameter
  3   where name in ('log_archive_dest_1',
  4                  'log_archive_dest_2',
  5                  'log_archive_dest_state_1',
  6                  'log_archive_dest_state_2',
  7                  'fal_client',
  8                  'fal_server',
  9                  'local_listener',
 10                  'remote_listener',
 11                  'standby_archive_dest',
 12                  'standby_file_management',
 13                  'service_names',
 14                  'db_unique_name',
 15                  'instance_name',
 16                  'db_name',
 17                  'db_file_name_convert',
 18                  'log_file_name_convert');
 
NAME                           VALUE
—————————— ——————————————————————————————
db_file_name_convert
log_file_name_convert
log_archive_dest_1             location=+FRADG valid_for=(all_logfiles,all_roles) db_unique_name=zwc
log_archive_dest_2             service=zwcdg lgwr async  valid_for=(online_logfiles,primary_role) db_unique_name=zwcdg
log_archive_dest_state_1       enable
log_archive_dest_state_2       ENABLE
standby_archive_dest           ?/dbs/arch
fal_client                     zwc
fal_server                     zwcdg
standby_file_management        AUTO
instance_name                  zwc1
service_names                  zwc
local_listener                  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.12)(PORT=1521))
remote_listener                zwc-cluster-scan:1521
db_name                        zwc
db_unique_name                 zwc
 
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/zwcdg/','+DATADG/zwc/','/u01/app/oracle/oradata/zwcdg/','+FRADG/zwc/' scope=spfile sid='*';
 
System altered.
 
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/zwcdg/','+DATADG/zwc/' scope=spfile sid='*';
 
 
 

Continue reading Oracle 11.2.0.4 RAC to Single Instance DataGuard Switchover