Database, SQL and PL/SQL

Write in a Read-Only Database

Run reports and DML against your standby database with Oracle Active Data Guard.

By Connor McDonald

July/August 2016

“Extension 5111. That’s never a good sign,” James thought as he looked down at his office phone. James knew that number—it was the operations manager, and it usually meant there was a problem with one of the company’s applications or servers. And because, just a few days ago, James had deployed a suite of enhancements to the company financial systems to enable users in the accounting and CRM departments to perform near-real-time analytical queries, he was concerned that the timing of the call was more than just coincidental. All the testing had been successful, but James knew that some of the queries would need to digest a large amount of historical data to provide the analytical insights the users had requested.

James took the call and was correct in his fears. His customers were happy with the new analytic data he was providing, but the operations manager, Dan, was not. “We are getting large spikes in disk activity on our servers when your scheduled tasks execute, and it is having an impact on the response times of some of the OLTP [online transaction processing] applications,” said Dan. “We’ve activated some workload management adjustments to guarantee service levels for the OLTP apps, but you’ll need to reassess how you execute the reports you’ve built. Can you run them on the Oracle Active Data Guard node?”

James was unaware that the production databases were protected by a high-availability architecture. He’d known that the organization had a secondary data center but never really understood how the data was safeguarded across the two sites. A quick visit to the Oracle documentation brought him up to speed on Oracle Active Data Guard and its features. He called Dan back and confirmed that the production databases were being protected with Oracle Active Data Guard, so there was a real-time, read-only copy of the production database. His resource-intensive reports could be rerouted to use the Oracle Active Data Guard database and eliminate the impact on the primary database.

After being granted access to the Oracle Active Data Guard database, James tried one of his most resource-intensive queries on the standby database:

SQL> SELECT f.fiscal_quarter,
  2         sum(f.forecast),
  3         sum(f.actuals)
 59  FROM   financial_results f
 60  WHERE  f.account_num in
 61    ( SELECT account_num
 62      FROM   account_extract
 63    )
 64  AND  f.fiscal_year > add_months(sysdate,-120)
 65  GROUP BY f.fiscal_quarter;
no rows selected
Elapsed: 00:00:00.79

The “no rows selected” result was certainly unexpected. After a moment’s confusion, James remembered that the first stage in his suite of report generation routines was to populate several extract tables, which were then used to drive the report extraction. The ACCOUNT_EXTRACT table was empty, which explained why the query was so fast (and the results were missing). He ran the populate routine and hit a problem:

ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "REPORT_EXTRACT", line 4
ORA-06512: at line 1

James’ initial optimism faded when it dawned on him that because the Oracle Active Data Guard database is read-only, his entire design methodology of loading extract tables to subsequently generate reports was not going to work. With a little more perusal of the documentation, he saw that global temporary tables persist their data to temporary segments, so perhaps they might be a solution in the read-only environment. He fired up his laptop to test the hypothesis, creating a global temporary version of his existing ACCOUNT_EXTRACT table.

SQL> create global temporary table gtt_account_extract
  2    ( pk_seq int,
  3      account_num int )
  4  on commit preserve rows;
Table created.

After shutting down his database on his laptop and reopening it in read-only mode, James attempted to perform a simple data manipulation language (DML) operation on his global temporary table.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database open read only;
Database altered.
SQL> insert into gtt_account_extract values (1,1);
insert into gtt_account_extract values (1,1)
ERROR at line 1:
ORA-16000: database open for read-only access

Disheartened, James called Dan to inform him that even with global temporary tables, he didn’t think the Oracle Active Data Guard database could be used solve the original problem, because of the need to perform DML operations. It looked like James would need to rethink the entire solution. But Dan had some good news.

“James, you were doing your testing on a read-only database on your own laptop. But a read-only database is not exactly the same as a read-only standby database. In particular, because we’re on Oracle Database 12c, you can promote your global temporary table changes through development and testing. Come see me again when the changes go into production.”

A few days later, James visited Dan, and they connected to the Oracle Active Data Guard instance. Dan requested that James run his code, and James was expecting to see the same errors he’d encountered before.

PL/SQL procedure successfully completed.

“How did this work?” asked James. Dan explained to him that in Oracle Database 12c, read-only standby databases, which include those configured as Oracle Active Data Guard databases, automatically activate a new feature called temporary undo. In previous releases, even though the data in global temporary tables was stored in temporary segments, the undo information for the temporary data (to enable a transaction to roll back) was still stored in the permanent undo segments, which prohibited their use in a read-only database. In Oracle Database 12c, the undo information for temporary data can also be stored in a temporary segment, thus allowing full DML operations on global temporary tables in a read-only database.

James was thrilled. His reporting solution was back on track. To his surprise, he discovered that his sequence values had been populated without any errors. He took a look at his LOAD_ACCOUNT routine to confirm that he was definitely accessing a sequence:

package body REPORT_EXTRACT is
  procedure load_account is
    delete from gtt_account_extract;
    insert into gtt_account_extract
    select acct_seq.nextval,
    from   ...

James was befuddled by this, because he knew that the database dictionary had to be updated from time to time as sequence values were consumed. So how was that possible in the read-only database? Temporary undo might be one thing, but how was the read-only database keeping track of the sequence values? He performed a couple of standalone queries on the standby database to confirm that sequences could be accessed without any problems:

SQL> select acct_seq.nextval from dual
  2  connect by level <= 5;
5 rows selected.

All seemed normal there, but it was when James queried the sequence values back on the production database that he made a discovery.

SQL> select acct_seq.nextval from dual;

Querying the sequence on the standby database automatically updated the sequence definition in the production database. James was impressed with this. His sequences would not be used in production anyway, because he would be running his reports on the standby database. Besides the minor change to his code to use global temporary tables, his reporting solution was migrated to the Oracle Active Data Guard node and did not have an impact on production applications any longer.

Three weeks later, James’ phone rang. “Extension 5111. That’s never a good sign,” James thought again.

Dan told James that his reporting jobs had all failed during the past evening. When James asked for details, Dan told him that although the production database had been down for security patching, the standby database had remained available, so he was unsure why the outage had affected James’ reporting tasks. When James checked the logs from his instrumentation, he saw the error:

ORA-03181: Sequence object no longer exists for 
Oracle Active Data Guard standby.

He recalled his earlier discovery that consuming sequence values on the standby database was reflected in the data dictionary on the production database. If the production database were not available, then this could not occur and hence his reporting tasks would fail, just as they did the evening before. The unavailability of the production database would be rare, but James was also thinking that it would be a feather in his cap if, even during production outages, his reporting tasks were one of the few, or only, unaffected activities. Temporary undo was a new feature in Oracle Database 12c, so perhaps there were related enhancements to sequences? James’ search of the documentation proved fruitful: he discovered the new SESSION keyword for sequences. To investigate how session sequences worked, he created such a sequence on his laptop database and consumed some values from it.

SQL> create sequence SESS_SEQ session;
Sequence created.
SQL> select sess_seq.nextval from dual connect by level <= 3;
SQL> connect james/james
SQL> select sess_seq.nextval from dual;

James could see that every time he created a new database session, the sequence values were reset. The sequence values were not global across the database but, rather, bound to each individual session. No need, then, to keep a dictionary definition updated, thought James. And the documentation appeared to confirm this: “Session sequences…can be accessed on any read/write or read-only databases.” James raised a change request with his DBA to convert his reporting extract sequences to session sequences, and his reporting jobs ran successfully from that point onward.


Developers often raise the ire of their production DBAs or operations managers when well-intentioned code threatens server resources or application response times. Although the capacity to use Oracle Active Data Guard to offload those expensive queries to a read-only copy of the data has existed for many years, the prohibition of any form of DML activity has often been a roadblock to fully taking advantage of this potential resource. Before Oracle Database 12c, the solutions often forced developers to use out-of-date data or introduce more complexity into their solutions via summary tables or materialized views to reduce the resource costs of their queries. With temporary undo and session sequences in Oracle Database 12c, developers can now utilize DML on global temporary tables to deliver customer solutions and get more value out of their organization’s existing investment in high-availability infrastructure.



Next Steps

 DOWNLOAD Oracle Database 12c.

 LEARN more about Oracle Active Data Guard.


Photography by Dmitri Popov, Unsplash