Pickleball spielen

Whenever you want to change certain storage attributes of your production tables, a Logical Standby can help. A common case is to do that during a migration. Not only will Data Guard help you to reduce downtime for the migration to the time it takes to do a switchover! With a Logical Standby, the segments do not need to look exactly (physically) the same as on the Primary. That’s where the name comes from 🙂

[oracle@uhesse1 ~]$ dgmgrl sys/oracle@prima 
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
 Connected.
 DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxPerformance
 Databases:
 prima - Primary database
 logst - Logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
 SUCCESS

My demo setup is on 11.2.0.3, but the shown technique should work similar with older versions. I will now create a demo user with a table that gets the default initial extent of 64k in the default tablespace of the database that uses autoallocate as the extent allocation type:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam@prima
Connected.
SQL> create table t as select * from dual;

Table created.

SQL> select extents,initial_extent from user_segments where segment_name='T';

   EXTENTS INITIAL_EXTENT
---------- --------------
	 1	    65536

SQL> select rowid,t.* from t;

ROWID		   D
------------------ -
AAADSaAAEAAAACTAAA X

I listed the rowid above to show that it will be different on the Logical Standby – but that is no problem because of the supplemental logging we do on the Primary to be able to identify the modified rows on the Logical Standby without that rowid from the Primary. Now let’s assume we want to have the initial extent changed to 8M – a recommendation on Exadata for large segments, by the way. See how easy that can be done:

DGMGRL> edit database logst set state=apply-off;
Succeeded.
DGMGRL> show database logst;

Database - logst

  Role:            LOGICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Instance(s):
    logst

Database Status:
SUCCESS

I need to stop the SQL Apply – not the Redo Transport – before the change on the Logical Standby can be done. Technically speaking, your Recovery Point Objective remains the same as before, but your Recovery Time Objective increases according to the amount of redo that accumulates now in the meantime. Now to the modification of the storage attributes:

SQL> select to_char(event_time,'dd-hh24:mi:ss') as time,event,status from dba_logstdby_events order by 1;

TIME	    EVENT					       STATUS
----------- -------------------------------------------------- -----------------------------------------------------------
29-16:03:07						       Shutdown acknowledged
29-16:03:07						       ORA-16128: User initiated stop apply successfully completed

SQL> alter table adam.t move storage (initial 8m);

Table altered.

If you see any other error message in dba_logstdby_events, fix these errors before you go on with the task. The table uses now 8M initial extents – it is reorganized also during this step. In my simplified example, there is no index on the table. Otherwise indexes need to be rebuilt now. I will restart the SQL Apply and check whether it works again:

DGMGRL> edit database logst set state=apply-on;
Succeeded.
DGMGRL> show database logst;

Database - logst

  Role:            LOGICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Instance(s):
    logst

Database Status:
SUCCESS

SQL> update t set dummy='Y' where dummy='X';

1 row updated.

SQL> commit;

Commit complete.

Now back on the Logical Standby:

SQL> connect adam/adam@logst
Connected.
SQL> select to_char(event_time,'dd-hh24:mi:ss') as time,event,status from dba_logstdby_events order by 1;

TIME	    EVENT					       STATUS
----------- -------------------------------------------------- -------------------------------------------
29-16:16:11						       ORA-16111: log mining and apply setting up
29-16:16:11						       Apply LWM 463670, HWM 463670, SCN 464525
29-16:16:11						       APPLY_UNSET: MAX_SGA
29-16:16:11						       APPLY_UNSET: MAX_SERVERS

SQL> select extents,initial_extent from user_segments where segment_name='T';

   EXTENTS INITIAL_EXTENT
---------- --------------
	 1	  8388608

SQL> select rowid,t.* from t;

ROWID		   D
------------------ -
AAADZrAAEAAAAESAAA Y

As you can see, the storage attribute was changed, and the rowid is different here. After a switchover to the Logical Standby, my production table uses now 8m initial extents.
Conclusion: With a Logical Standby in place, it is relatively easy to change storage attributes. It requires a stop of the SQL Apply process, though. You may want to use this approach during a migration to combine the major task (the migration) with some housekeeping. If you ever wondered why Logical Standby is listed under the Logical Migration Methods although it starts with (and has the same limitations as) a Physical Standby – you have just seen the reason. As always: Don’t believe it, test it 🙂