How to configure OLTP with reporting queries

If you have an extremely busy OLTP system with a physical standby ready for a manual role transition, and you want to run very heavy reporting queries without affecting the system, consider using a separate report database with downstream capture configuration. Yes, it is very easy to configure, and it will have no performance impact on the OLTP system whatsoever.

In this blog, I will show how to do this, and how to maintain the archivelog transportation during a manual switchover.

Below is the configuration of the three databases I have for testing:

Production DB

Host – PRDSRV
DB name – TESTDB
DB unique name – PRDDB
OS – Linux x86_64
Oracle – 11.1.0.7
OLTP schema – OLTPUSER
log_archive_dest_1='location=use_db_recovery_file_dest mandatory'
log_archive_dest_2='service=STBDB lgwr async valid_for=(primary_role,online_logfiles) db_unique_name=STBDB reopen=15'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
archive_lag_target=1200
fal_client=PRDDB
fal_server=STBDB

Standby DB

Host – STBSRV
DB name – TESTDB
DB unique name – STBDB
OS – Linux x86_64
Oracle – 11.1.0.7
OLTP schema – OLTPUSER
log_archive_dest_1='location=use_db_recovery_file_dest mandatory'
log_archive_dest_2='service=PRDDB lgwr async valid_for=(primary_role,online_logfiles) db_unique_name=PRDDB reopen=15'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
archive_lag_target=1200
fal_client=STBDB
fal_server=PRDDB

Report DB

Host – RPTSRV
DB name – RPTDB
OS – Linux x86_64
Oracle – 11.1.0.7
Streams schema - STRMADMIN
log_archive_dest_1='location=use_db_recovery_file_dest mandatory'
log_archive_dest_state_1=enable

So let’s start. First, I will configure the downstream capture.

1. Add RPTDB to tnsnames.ora on PRDSRV and STBSRV.

RPTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = RPTSRV)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RPTDB)
)
)

2. Set up log_archive_dest_3 on PRDDB and STBDB.

alter system set log_archive_dest_3='service=RPTDB arch noregister template=+DG_FRA/TESTDB/ARCHIVELOG/TESTDB%r_%t_%s.log
valid_for=(standby_role,all_logfiles) reopen=15' scope=both;
alter system set log_archive_dest_state_3=enable scope=both;

3. Add supplemental log data on PRDDB and build dictionary.

alter database add supplemental log data;
select supplemental_log_data_min from v$database;
exec dbms_capture_adm.build;
select name,sequence#,first_change# from v$archived_log where dictionary_begin='YES';

4. Prepare OLTPUSER schema for instantiation on PRDDB.

exec dbms_capture_adm.prepare_schema_instantiation(schema_name => 'OLTPUSER');
select * from dba_capture_prepared_schemas;

5. Export the OLTPUSER schema on PRDDB.

select dbms_flashback.get_system_change_number from dual;
expdp system/*** directory=EXP_DIR dumpfile=oltpuser.dmp schema=OLTPUSER logfile=oltpuser.log flashback_scn={SCN from the previous query}

6. Setup queue, rules, rule set, and apply and capture processes on RPTDB.

begin
dbms_streams_adm.set_up_queue(
queue_table => 'STRMADMIN.TESTDB_QUEUE_TABLE',
queue_name => 'STRMADMIN.TESTDB_QUEUE',
queue_user => 'STRMADMIN');
end;
/
begin
dbms_aqadm.start_queue(queue_name => 'TESTDB_QUEUE');
end;
/
begin
dbms_rule_adm.create_rule(
rule_name => 'TESTDB_DML',
condition => '(:dml.get_object_owner() = “OLTPUSER”)',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT',
rule_comment => 'testdb streams_dml');
end;
/
begin
dbms_rule_adm.create_rule(
rule_name => 'TESTDB_DDL',
condition => '(:ddl.get_object_owner() = “OLTPUSER” or :ddl.get_base_table_owner() = “OLTPUSER”)',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT',
rule_comment => 'testdb streams_dml');
end;
/
begin
dbms_rule_adm.create_rule_set(
rule_set_name => 'TESTDB_RULE_SET',
evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT',
rule_set_comment => 'testdb streams');
end;
/
begin
dbms_rule_adm.add_rule(
rule_name => 'TESTDB_DML',
rule_set_name => 'TESTDB_RULE_SET',
rule_comment => 'testdb streams');
end;
/
begin
dbms_rule_adm.add_rule(
rule_name => 'TESTDB_DDL',
rule_set_name => 'TESTDB_RULE_SET',
rule_comment => 'testdb streams');
end;
/
begin
dbms_apply_adm.create_apply(
queue_name => 'STRMADMIN.TESTDB_QUEUE',
apply_name => 'TESTDB_APPLY'
,apply_user => 'STRMADMIN'
,source_database => 'TESTDB'
,apply_captured => true
);
end;
/
begin
dbms_capture_adm.create_capture(
queue_name => 'STRMADMIN.TESTDB_QUEUE'
,capture_name => 'TESTDB_CAPTURE'
,rule_set_name => 'STRMADMIN.TESTDB_RULE_SET'
,source_database => 'TESTDB'
,first_scn => {FIRST_CHANGE# from step 3}
,start_scn => {SCN from step 5}
,capture_user => 'STRMADMIN'
,checkpoint_retention_time => 0.5);
end;
/

7. Copy export file from PRDSRV to RPTSRV.

8. Import OLTPUSER schema on RPTDB.

impdp system/*** directory=IMP_DIR dumpfile=oltpuser.dmp schema=OLTPUSER logfile=oltpuser_imp.log

9. Start apply and capture on RPTDB.

exec dbms_apply_adm.start_apply('TESTDB_APPLY');
exec dbms_capture_adm.start_capture('TESTDB_CAPTURE');

10. Check the capture state and if there is a gap, copy the missing archivelogs from PRDDB to RPTDB and register them. Then add a record into OLTPUSER test table on PRDDB switch the logfiles and check the data on RPTDB.

select capture_name,state,state_change_time,capture_message_create_time from v$streams_capture;
select * from dba_registered_archived_log where source_database='TESTDB';
alter database register logical logfile '{archivelog name}' for 'TESTDB_CAPTURE';

11. The last thing is to create a job to clean registered archivelogs. If the archivelogs are on an ASM diskgroup, they can be deleted by the following command:

alter diskgroup {diskgroup name} drop file '{file name}';

Next, I will show how to maintain the archivelog transportation during a manual switchover.

1. Perform the switchover on the primary database PRDDB.

select name,db_unique_name,open_mode,database_role,switchover_status from v$database;
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate
startup mount

2. Switch the target physical standby database STBDB role to the primary role.

select name,db_unique_name,open_mode,database_role,switchover_status from v$database;
alter database commit to switchover to primary;
alter database open;

3. Start Redo Apply on PRDDB.

alter database recover managed standby database using current logfile disconnect from session;
select process,status,client_process,sequence#,delay_mins from v$managed_standby;

4. There will be one missing archivelog on RPTDB, the one that was created with End-Of-Redo indicator. It can be found in PRDDB alert log.

ARCH: Noswitch archival of thread 1, sequence 1051
ARCH: End-Of-Redo Branch archival of thread 1 sequence 1051
Archived Log entry 194 added for thread 1 sequence 1051 ID 0xd7df275e dest 1:
ARCH: Archiving is disabled due to current logfile archival
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
LOG_ARCHIVE_DEST_3 currently has a gap

5. Copy the missing archivelog from PRDDB to RPTDB and register it.

-- On PRDSRV
select name from v$archived_log where sequence#=1051;
rman target /
copy archivelog '{archivelog name}' to '{temp folder}';
exit

-- On RPTSRV
alter database register logical logfile '{archivelog name}' for 'TESTDB_CAPTURE';

Now, after the Downstream capture is set up and tested, one can run any reports on the report database, and there will be absolutely no impact on the production OLTP database. Moreover, since reporting queries can use different type of indexes and indexes on different columns, the report database indexes can be reorganized to better suit the queries.

Oracle 11g SE switchover

Recently, I tested a switchover on Oracle 11g SE1.
As you know, Oracle Database Standard Edition One, as well as Standard Edition, does not have Data Guard feature. Therefore, I had to do everything manually.
The whole process took less than 15 minutes. This includes less than 5 minutes of FULL downtime to restart the database in READ ONLY mode and less than 10 minutes of READ ONLY downtime.
Of course, it depends on the size of Redo logs and the network speed to move Redo logs from the primary server to standby.

Here is what I had
The primary database and one physical standby database

OS - SUSE Linux ES10 (SP2) x86_64
Oracle - Release 11.1.0.7.0 64bit SE1

First of all, I switched the standby database to the primary role.

Step 1. Shutdown the primary database

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2. Make a copy of controlfile, spfile, and redo logs

SQL> !cp control01.ctl copy/control01.ctl.primary
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.primary
SQL> !cp *.log copy/

Step 3. Startup the primary database in READ ONLY mode

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

Step 4. Copy all needed archive logs, the copy of controlfile, spfile, and redo logs to the standby server ora2

oracle@ora1 /u01/app/oracle/testdb/oradata> scp ../archivelogs/1_152_333215132.dbf oracle@ora2:/u01/app/oracle/testdb/archivelogs/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/control01.ctl.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/spfilepb.ora.primary oracle@ora2:/u01/app/oracle/testdb/oradata/copy/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo01.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo02.log oracle@ora2:/u01/app/oracle/testdb/oradata/
oracle@ora1 /u01/app/oracle/testdb/oradata> scp copy/redo03.log oracle@ora2:/u01/app/oracle/testdb/oradata/

Step 5. Apply all needed archive logs on the standby database

oracle@ora2 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> recover standby database until cancel;
ORA-00279: change 2244877 generated at 10/07/2008 14:40:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf
ORA-00280: change 2244877 for thread 1 is in sequence #152

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2245162 generated at 10/07/2008 14:53:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/testdb/archivelogs/1_153_333215132.dbf
ORA-00280: change 2245162 for thread 1 is in sequence #153
ORA-00278: log file '/u01/app/oracle/testdb/archivelogs/1_152_333215132.dbf' no
longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Step 6. Shutdown the standby database

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Step 7. Make a copy of controlfile and spfile. To make it easier I just switched spfiles.

SQL> !cp control01.ctl copy/control01.ctl.stndby
SQL> !scp copy/control01.ctl.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/
SQL> !cp /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora copy/spfiletestdb.ora.stndby
SQL> !scp copy/spfilepb.ora.stndby oracle@ora1:/u01/app/oracle/testdb/oradata/copy/

Step 8. Replace the standby controlfile and spfile by the copy of primary controlfile and spfile

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/spfiletestdb.ora.primary /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.primary control01.ctl
SQL> !cp copy/control01.ctl.primary control02.ctl
SQL> !cp copy/control01.ctl.primary control03.ctl

Step 9. Switch the standby database to the primary role

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/testdb/oradata/temp01.dbf' size 100m reuse autoextend on next 100m maxsize 2048m;

Tablespace altered.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE, STANDBY_BECAME_PRIMARY_SCN from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE    STANDBY_BECAME_PRIMARY_SCN
------- ---------- ---------------- --------------------------
CURRENT READ WRITE PRIMARY                             2244877

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

And finally, I switched the old primary database to the standby role.

Step 10. Shutdown the old primary database

oracle@ora1 /u01/app/oracle/testdb/oradata> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 11. Replace the old primary controlfile and spfile by the copy of standby controlfile and spfile

SQL> !rm control*.ctl
SQL> !rm /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !rm temp01.dbf
SQL> !rm *.log
SQL> !cp copy/spfiletestdb.ora.stndby /u01/app/oracle/product/11.1.0/db_1/dbs/spfiletestdb.ora
SQL> !cp copy/control01.ctl.stndby control01.ctl
SQL> !cp copy/control01.ctl.stndby control02.ctl
SQL> !cp copy/control01.ctl.stndby control03.ctl

Step 12. Switch the old primary database to the standby role

SQL> startup mount
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2159312 bytes
Variable Size             754978096 bytes
Database Buffers          503316480 bytes
Redo Buffers                8912896 bytes
Database mounted.

SQL> select CONTROLFILE_TYPE, OPEN_MODE, DATABASE_ROLE from v$database;

CONTROL OPEN_MODE  DATABASE_ROLE
------- ---------- ----------------
STANDBY MOUNTED    PHYSICAL STANDBY

SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

So you can see that Oracle 11g SE1 (SE) switchover of primary database and one physical database located on different servers is a very simple process. But if you have the physical standby on the same server and use OMF you should consider 2 things.

Issue 1: How to change the path to the datafiles and redo logs in the controlfiles;
Solution: In Step 9 after the database is mounted and before it is opened, all datafiles and redo logs should be renamed by ‘ALTER DATABASE RENAME FILE’ command.
Issue 2: How not to remove the old datafiles. OMF will remove the old files after RENAME command.
Workaround: Move the files to temporary folder and move them back after RENAME command. The old primary database should be down.

The same process can be done with a RAC database.
And do not forget to make a database backup in case something goes wrong.

Oracle DBA Guy post is born

Oracle DBA Guy post is born!
I have been working with Oracle since 1998 but just now I realised that I have some Oracle knowledge to be shared. So let's start...