Archive for September, 2010

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.

, , , ,

17 Comments

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.

, , ,

10 Comments

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.

 

, , ,

50 Comments

%d bloggers like this: