Beiträge getaggt mit OU EMEA Newsletter
Failover to Snapshot Standby
In a recent Data Guard course that I tought, the question came up whether it is possible to failover to a Snapshot Standby if the Primary is lost. My answer was that this is of course possible but will take a longer time, because first the Snapshot Standby need to be converted back to Physical Standby and then all the collected Redo Protocol on the Standby site needs to be applied.
However, when we tried to do it, the Broker refused to do the convert and the failover, complaining about the Primary not being reachable. We needed to do the convert without the Broker then. Today, when I wanted to reproduce the issue, it worked like a charm, though:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
This is the same version we had in the classroom. Now the configuration:
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxPerformance
Databases:
prima - Primary database
physt - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
I will now shutdown the Primary like there is an emergency on the Primary Site:
[oracle@uhesse ~]$ sqlplus sys/oracle@prima as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 14 09:42:47 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown abort ORACLE instance shut down.
Now with the Broker, a convert fails:
[oracle@uhesse ~]$ dgmgrl sys/oracle@physt DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> convert database physt to physical standby; Converting database "physt" to a Physical Standby database, please wait... Error: ORA-01034: ORACLE not available Error: ORA-16625: cannot reach database "prima" Failed. Failed to convert database "physt"
That was until this point the same as in the class. But now my failover succeeds, while it did not in the class:
DGMGRL> failover to physt; Converting database "physt" to a Physical Standby database, please wait... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Continuing to convert database "physt" ... Operation requires shutdown of instance "physt" on database "physt" Shutting down instance "physt"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "physt" on database "physt" Starting instance "physt"... ORACLE instance started. Database mounted. Database "physt" converted successfully Performing failover NOW, please wait... Failover succeeded, new primary is "physt"
All is fine now. It worked as it was supposed to. But it was a little scary, though, that it wasn’t possible to do the very same in the class – not sure what was different there. My advice based upon this experience would be to test the failover to a Snapshot Standby that has no connection to the Primary. Also, consider to configure two Standby Databases and use only one as Snapshot Standby. And notice the manual convert in case:
SQL> alter database convert to physical standby;
DBMS_COMPRESSION Example
During my present Exadata course in Munich, we have discussed Hybrid Columnar Compression (HCC) and how to estimate the size of tables after compression. In addition to my previous posting that introduced HCC, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example, but not in this case. Also my brief research in the web did not retrieve a (simple) example.
Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on) also to get an estimation about HCC, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.
SQL> set serveroutput on
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('&ScratchTBS'),
ownname => user,
tabname => upper('&TableName'),
partname => NULL,
comptype => dbms_compression.comp_for_query_high,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str);
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/
Addendum: See Jonathan Lewis‘ nice cheat to keep the HCC compressed table that gets created during the evaluation under the covers – even if you are NOT running DBMS_COMPRESSION on Exadata!
Second addendum: This posting was published in the current Oracle University Newsletter.
My adrci posting is published in the OU EMEA Newsletter
I proudly notice that my posting about adrci made it into the current Oracle University EMEA Newsletter:
The topic seems indeed of interest to many DBAs – it is already one of my all-time most popular postings with about 3500 hits presently.
Pickleball spielen auf YouTube
Pickleball spielen 