The Oracle Instructor
The Data Guard Broker is recommended for various reasons, this one is less obvious: It prevents a Split-Brain problem that may otherwise occur in certain situations. Let me show you:
[oracle@uhesse ~]$ dgmgrl sys/oracle@prima DGMGRL for Linux: Version 188.8.131.52.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> show configuration; Configuration - myconf Protection Mode: MaxAvailability Members: prima - Primary database physt - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 18 seconds ago)
This is my setup with 12c, but the demonstrated behavior is the same with 11g already. I will cause a crash of the primary database now, without damaging any files – like a power outage on the primary site:
[oracle@uhesse ~]$ ps -ef | grep smon oracle 6279 1 0 08:30 ? 00:00:00 ora_smon_prima oracle 6786 1 0 08:32 ? 00:00:00 ora_smon_physt oracle 7168 3489 0 08:43 pts/0 00:00:00 grep --color=auto smon [oracle@uhesse ~]$ kill -9 6279
Don’t do that at home Now the primary is gone, but of course I can failover to the standby:
[oracle@uhesse ~]$ dgmgrl sys/oracle@physt DGMGRL for Linux: Version 184.108.40.206.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDBA. DGMGRL> failover to physt; Performing failover NOW, please wait... Failover succeeded, new primary is "physt"
So far so good, my end users can continue to work now on the new primary. But what happens when the power outage is over and the ex-primary comes back up again?
[oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba SQL*Plus: Release 220.127.116.11.0 Production on Wed May 18 08:47:30 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2923920 bytes Variable Size 452985456 bytes Database Buffers 788529152 bytes Redo Buffers 13852672 bytes Database mounted. ORA-16649: possible failover to another database prevents this database from being opened
The DMON background process of the new primary communicates with the DMON on the ex-primary, telling it that there cannot be two primary databases within the same Data Guard Broker configuration! Try the same scenario without the broker and you will observe the ex-primary coming up until status OPEN. Just wanted to let you know
Tagged: Data Guard
Join us with this FREE event on May 19, 13:00 CET and register now, because seats are limited.
I will be talking about
- how to create and maintain a 12c Standby Database in the most efficient way
- how to do switchover and failover
- how to keep up client connectivity after role changes
These topics will be live demonstrated – positively no slide show reading.
After this major part of the event, we will briefly advertise our digital learning offerings that relate to Oracle Database Core Technology and how you may take advantage of them.
Hope to see YOU in the session
If you speak often at conferences, sharing your screen to demo things, this could be helpful:
Throughout your presentation, the audience will be able to see your Twitter Handle, reminding them to include it with tweets about the event. I used to include it in the slides, but this is better, because it works also with live demonstrations where no slides are being showed. Which is incidentally my favorite way to do presentations
Now how can you do it? Quite easy, you open the Windows Control Panel and click on Region and Language. Then click on Additional settings:
Then you insert your Twitter Handle (or any other text you like to see on the taskbar) as AM and PM symbol. Make sure to select Time formats with trailing tt:
That’s it. If you want the font size as large as on the first picture above, that can be done here:
I did that with Windows 7 Professional 64 bit. Hope you find it useful
Tagged: speaker tip
Straight after the Oracle University Expert Summit in Berlin – which was a big success, by the way – the circus moved on to another amazing place: Istanbul!
The Turkish Oracle User Group (TROUG) did its annual conference in the rooms of the Istanbul Technical University with local and international speakers and a quite attracting agenda.
Do you recognize anyone here?
I delivered my presentation “Best of RMAN” again like at the DOAG annual conference last year:
Many thanks to the organizers for making this event possible and for inviting us speakers to dinner
The conference was well received and in my view, it should be possible to attract even more attendees in the coming years by continuing to invite high-profile international speakers
My special thanks to Joze, Yves and Osama for giving me your good company during the conference – even if that company was sometimes very tight during the car rides
Today, I got this message in my alert.log file:
Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 709 MBs bigger than current size.
When I look at the datafile sizes and compare them with the buffer cache size, it shows:
SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo; NAME MB -------------------------------------------------- ---------- Fixed SGA Size 2,80265045 Redo Buffers 13,1953125 Buffer Cache Size 3296 In-Memory Area Size 2048 Shared Pool Size 736 Large Pool Size 32 Java Pool Size 16 Streams Pool Size 0 Shared IO Pool Size 208 Data Transfer Cache Size 0 Granule Size 16 Maximum SGA Size 6144 Startup overhead in Shared Pool 181,258133 Free SGA Memory Available 0 14 rows selected. SYS@cloudcdb > select sum(bytes)/1024/1024 as mb from v$datafile; MB ---------- 3675
It is true, the database doesn’t fit completely into the buffer cache, missing roughly that amount of space mentioned. There is no such parameter as DEFAULT_CACHE_SIZE, though.
What we have instead is DB_CACHE_SIZE. In order to fix that issue, I was using this initialization parameter file to create a new spfile from:
[oracle@uhesse-service2 dbs]$ cat initCLOUDCDB.ora *.audit_file_dest='/u02/app/oracle/admin/CLOUDCDB/adump' *.audit_trail='db' *.compatible='18.104.22.168.0' *.control_files='/u02/app/oracle/oradata/CLOUDCDB/control01.ctl','/u03/app/oracle/fra/CLOUDCDB/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='CLOUDCDB' *.db_recovery_file_dest='/u03/app/oracle/fra' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u02/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=CLOUDCDBXDB)' *.enable_pluggable_database=true *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.sga_target=6g *.pga_aggregate_target=2g *.inmemory_size=1g *.db_cache_size=4g
That reduced the size of the In-Memory Column Store to make room for the buffer cache. Now the database fits nicely into the buffer cache again:
SYS@cloudcdb > select name,bytes/1024/1024 as mb from v$sgainfo; NAME MB -------------------------------------------------- ---------- Fixed SGA Size 2,80265045 Redo Buffers 13,1953125 Buffer Cache Size 4256 In-Memory Area Size 1024 Shared Pool Size 800 Large Pool Size 32 Java Pool Size 16 Streams Pool Size 0 Shared IO Pool Size 0 Data Transfer Cache Size 0 Granule Size 16 Maximum SGA Size 6144 Startup overhead in Shared Pool 181,290176 Free SGA Memory Available 0 14 rows selected.
Accordingly the message in the alert.log now reads
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
Don’t get me wrong: I’m not arguing here against the In-Memory Option or in favor of Full Database Caching. Or whether it makes sense to use any of them or both. This post is just about clarifying the strange message in the alert.log that may confuse people.
And by the way, my demo database is running in the Oracle Cloud
Tagged: 12c New Features
Join us in Berlin, 18th – 20th April. The event will take place at the Adlon Hotel with Jonathan Lewis, Pete Finnigan, Christian Antognini, Javier de la Torre Medina and myself as speakers. We have over 70 enrollments already, so take action to secure your seat in time!
My topic will be Rolling Upgrade from 11g to 12c, with a special focus on doing it with Transient Logical Standby. In a live demonstration I will start with a Primary and a Physical Standby Database both running 22.214.171.124 in Maximum Availability protection mode using the Data Guard Broker. This is likely one of the most common setups today. We will then see how to upgrade this configuration to 126.96.36.199 with minimum downtime.
When I did this seminar before during another Expert Summit in Dubai, I was still using 188.8.131.52 as the initial release, then upgrading it to 184.108.40.206. It took me some time and effort to update my demo environment and the lessons to cover more recent and meanwhile more relevant versions. Hope to see you there