Beiträge getaggt mit High Availability
Real-Time Query in 11gR2
You may have noticed the spectacular 11g (R1) New Feature Real-Time Query already, that makes it possible to use a Physical Standby Database for Reporting while it is still applying Redo-Protocol received from the Primary Database. There are some additions to that feature in 11gR2.
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxAvailability
Databases:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database physt
Database - physt
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
physt
Database Status:
SUCCESS
I think the red sections above are new in 11gR2 and make it easier to recognize that we are using Real-Time Query – therefore we may want to license Active Data Guard, if not done already 🙂
Now let’s suppose we make use of the Physical Standby for Reporting but want to make sure that the Reports are up to date with the Primary Changes:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
SQL> col name for a30
col value for a20
select name,value from v$dataguard_stats;
NAME VALUE
------------------------------ --------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time +00 00:00:00.000
estimated startup time 25
There is no Apply Lag right now between Primary and Standby. The shown value of OPEN_MODE is also a New Feature in 11gR2, as well as the following parameter:
SQL> connect scott/tiger@physt
Connected.
SQL> alter session set STANDBY_MAX_DATA_DELAY=1;
Session altered.
That setting means that in Scott’s session on the Physical Standby, we will only tolerate a Lag Time of 1 second between the Data on the Standby and the Data on the Primary. A value of zero would require full synchronicity. I will make sure that this condition cannot be met by interrupting the Redo Transport from Primary to Standby from another session:
SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
Have you noticed that there are now 31 Log Archive Destinations, by the way? I will now change data on the Primary while the Standby cannot apply, thereby creating a Transport Lag and also an Apply Lag. It’s easier for me to demonstrate it that way as by generating a so high load on the Primary that this leads to a significant Apply Lag on the Standby because MRP can’t keep the pace. Same session:
SQL> connect scott/tiger@prima
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 ACCOUNTING CHICAGO
40 OPERATIONS BOSTON
SQL> update dept set dname='A' where deptno=10;
1 row updated.
SQL> commit;
Commit complete.
Back to the Scott session on the Physical Standby:
SQL> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 1 seconds exceeded
The Standby is lagging too far behind the demanded maximum Lag Time. In 11gR1, this would have given the old result set like that:
SQL> alter session set STANDBY_MAX_DATA_DELAY=none; Session altered. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 ACCOUNTING CHICAGO 40 OPERATIONS BOSTON
Another 11gR2 New Feature is the option to synchronize the Standby explicitly before a query:
SQL> alter session set STANDBY_MAX_DATA_DELAY=1; Session altered.
SQL> alter session sync with primary;
ERROR:
ORA-03173: Standby may not be synced with primary
SQL> host oerr ora 3173
03173, 00000, "Standby may not be synced with primary"
// *Cause: ALTER SESSION SYNC WITH PRIMARY did not work because either the
// standby was not synchronous or in a data-loss mode with regard
// to the primary, or the standby recovery was terminated.
// *Action: Make the standby synchronous and no-data-loss with regard
// to the primary. Make the standby recovery active.
The SYNC is not possible because of the DEFER I did above. I renable Redo Transport and then try again:
SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> connect scott/tiger@physt
Connected.
SQL> alter session set STANDBY_MAX_DATA_DELAY=1;
Session altered.
SQL> alter session sync with primary;
Session altered.
Conclusion: With the 11gR2 addition STANDBY_MAX_DATA_DELAY, we can now enforce a certain level of synchronicity during the usage of Real-Time Query in order to make the results we get from the Physical Standby reliable.
A Practical Guide to Data Guard: Real-Time Query https://t.co/77dmMULkZF #Oracle Learning Streams: FREE for OCP, OCE pic.twitter.com/5Nv9v3Q9OX
— Uwe Hesse (@UweHesse) October 23, 2015
Snapshot Standby Database in Action
Just finished an Oracle Database 11g Data Guard course where we implemented a Snapshot Standby Database – which is a new feature of 11g. Particularly the possibility to receive redo from the Primary still while the Standby is used for testing purpose is new in 11g.
DGMGRL> show configuration
Configuration
Name: myconfig
Enabled: YES
Protection Mode: MaxAvailability
Databases:
prima - Primary database
physt - Physical standby database
logst - Logical standby database
Fast-Start Failover: DISABLED
Current status for "myconfig":
SUCCESS
This is my configuration with the Physical Standby physt in place. You may create that configuration following the PDF 11g Data Guard on the command line from my Downloads page yourself. Now I create the Snapshot Standby:
DGMGRL> convert database physt to snapshot standby;
Converting database "physt" to a Snapshot Standby database, please wait...
Database "physt" converted successfully
DGMGRL> show configuration;
Configuration
Name: myconfig
Enabled: YES
Protection Mode: MaxAvailability
Databases:
prima - Primary database
physt - Snapshot standby database
logst - Logical standby database
Fast-Start Failover: DISABLED
Current status for "myconfig":
SUCCESS
Prerequisite for that action is that physt is in Flashback Mode. The Data Guard Broker silently created a guaranteed restore point to ensure that it can flashback the Snapshot Standby back to a Physical Standby after testing is done:
$ sqlplus sys/oracle@physt as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 24 15:30:01 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
I am going to test here while my Primary is still transmitting Redo Protocol to the Standby Site. It gets written into Standby Logs there and archived as usual. Only the Redo Apply is stopped while in Snapshot Standby Role:
SQL> drop user scott cascade;
User dropped.
SQL> create tablespace nu datafile '/home/oracle/physt/nu01.dbf' size 10m;
Tablespace created.
SQL> alter database datafile '/home/oracle/physt/users01.dbf' resize 500m;
Database altered.
SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select bytes/1024/1024 as mb from v$datafile
where name='/home/oracle/prima/users01.dbf';
MB
----------
10
During testing, I dropped my „Application User“ scott, increased the size of one datafile and even created a new tablespace. I will now do some „productive changes“ on the Primary:
SQL> connect scott/tiger@prima Connected. SQL> create table test as select * from dept; Table created.
Now I want my Physical Standby back:
DGMGRL> convert database physt to physical standby;
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
Is everything on the Standby again as on the Primary?
$ sqlplus sys/oracle@physt as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 24 15:44:39 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select bytes/1024/1024 as mb from v$datafile
where name='/home/oracle/physt/users01.dbf';
MB
----------
10
SQL> select * from scott.test;
select * from scott.test
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from scott.test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
The datafile got resized back (!) to 10m, the user scott is back and the change done on the Primary during the testing time is also present on the Standby now. Even the new tablespace nu, created on the Snapshot Standby disappeared:
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
3 TEMP NO NO YES
4 USERS YES NO YES
It also removed the datafile from the OS Filesystem. One thing (at least) that should not be done during testing: A drop tablespace is not tolerated. You can do it on the Snapshot Standby, but afterwards it cannot get reconverted into Physical Standby again.
Conclusion: We now have a very comfortable and fast way to use our Physical Standby as a testing system – maybe together with the 11g New Feature Database Replay – without losing our Disaster Protection. Only the Failover Time will increase in case, because the Snapshot Standby has first to be reconverted and collected Redo has to be applied before the Failover can succeed.
Data Guard & Oracle Restart in 11gR2
Just playing around with Data Guard & Oracle Restart in the current release 11gR2 – it’s really cool! Look at Joel Goodmans introduction into Oracle Restart, if you never heared of it before. At first, I installed Oracle Database 11gR2 Grid Infrastructure for Standalone Server to get Oracle Restart, then Oracle Database 11gR2 Software only. I did not use ASM in this setup – everything is running inside a VM on my notebook with only one single hard drive really, so ASM is of not much help here 🙂
Listener and Database must be registered manually if NETCA and DBCA have not been used to create them. I did that on the command line, therefore the following steps.
First registering the listener:
$ srvctl add listener -o /u01/app/oracle/product/11.2.0/dbhome_1
$ srvctl add database -d prima -o /u01/app/oracle/product/11.2.0/dbhome_1 -r PRIMARY
Above added the Primary to the Oracle Local Registry (OLR)
$ srvctl add database -d physt -o /u01/app/oracle/product/11.2.0/dbhome_1 -r PHYSICAL_STANDBY -s MOUNT
Above added the Physical Standby to the OLR. -s is the startmode which defaults to OPEN. My Standby shall get mounted automatically. I would choose OPEN if I am inclined to use the 11g New Feature Real-Time Query – supposed I had purchased Active Data Guard.
I will now add an Application Service to my Configuration – with the same purpose demonstrated already in this posting, but without having to use DBMS_SERVICE. Instead, Oracle Restart will take care for the start of the services in my Data Guard environment according to the assigned Database Role. The creation of a Database Trigger for that purpose is no longer necessary. Instead, the usage of Oracle Restart is recommended:
$ srvctl add service -d prima -s prod -l PRIMARY -e SELECT -m BASIC
-l is the Database Role in which this service prod is supposed to get started, -e is the Failover Type and -m the Failover Method. Next I configure Failover Retries (-z) and Failover Delays (-w):
$ srvctl modify service -d prima
-s prod
-z 180
-w 1
The same has to be done for the Standby Database also – the -l PRIMARY parameter takes care that the service will not be offered unless this database becomes Primary:
$ srvctl add service -d physt -s prod -l PRIMARY -e SELECT -m BASIC $ srvctl modify service -d physt -s prod -z 180 -w 1
The nect command is going to start the service actually. We need to do this only for the first time manually – again without needing DBMS_SERVICE for it:
$ srvctl start service -d prima -s prod
Now let’s investigate the properties of our new service:
$ srvctl config service -d prima -s prod
Service name: prod Service is enabled Cardinality: SINGLETON Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Failover type: SELECT Failover method: BASIC TAF failover retries: 180 TAF failover delay: 1 Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE
I have prepared a connect descriptor prod in the same way as in this posting shown already. Using it to connect as system user. Now I kill the SMON process of my Primary Instance (Don’t do that at home). Then a select on v$instance in my user system session waits for the reestablishing of the connection (maximum 180 seconds with my settings above) – after less than 5 seconds, the ohasd (Oracle High Availabilty Services Daemon) recognizes this and restarts the Primary Instance. After less than 20 seconds, I see my select on v$instance come through. This was a Transparent Application Failover without another Instance or Database in the game 🙂 Really cool, isn’t it?
Now let’s do a switchover and see how Oracle Restart will take care to start the prod service on the new Primary and how the connection again is reestablished there:
$ dgmgrl sys/oracle@prima
DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxAvailability
Databases:
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to physt
Performing switchover NOW, please wait...
New primary database "physt" is opening...
Operation requires shutdown of instance "prima" on database "prima"
Shutting down instance "prima"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prima" on database "prima"
Starting instance "prima"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "physt"
The switchover was done as usual. Now what about the service and the session?
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-SEP-2010 08:45:25
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 06-SEP-2010 07:40:29
Uptime 0 days 1 hr. 4 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/
network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/
tnslsnr/uhesse/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uhesse)(PORT=1521)))
Services Summary...
Service "logst_DGMGRL" has 1 instance(s).
Instance "logst", status UNKNOWN, has 1 handler(s) for this service...
Service "physt" has 1 instance(s).
Instance "physt", status READY, has 1 handler(s) for this service...
Service "physt_DGB" has 1 instance(s).
Instance "physt", status READY, has 1 handler(s) for this service...
Service "physt_DGMGRL" has 1 instance(s).
Instance "physt", status UNKNOWN, has 1 handler(s) for this service...
Service "prima" has 1 instance(s).
Instance "prima", status READY, has 1 handler(s) for this service...
Service "prima_DGB" has 1 instance(s).
Instance "prima", status READY, has 1 handler(s) for this service...
Service "prima_DGMGRL" has 1 instance(s).
Instance "prima", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
Instance "physt", status READY, has 1 handler(s) for this service...
The command completed successfully
The service was started on the new Primary by Oracle Restart automatically. Now the session:
SYSTEM@prod > select instance_name from v$instance;
INSTANCE_NAME
----------------
physt
SYSTEM@prod > select username,FAILED_OVER,FAILOVER_METHOD,FAILOVER_TYPE
from v$session where username='SYSTEM';
USERNAME FAI FAILOVER_M FAILOVER_TYPE
------------------------------ --- ---------- -------------
SYSTEM YES BASIC SELECT
The session was again „unsinkable“ and survived the switchover by failing over to the new Primary. When I reboot my server, ohasd together with DMON now take care that Listener, Primary, Standby and Service all get started in the correct order.
Pickleball spielen auf YouTube
Pickleball spielen 