Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace from one database to another:

First, we make our tablespace read only, and Datapump export out the metadata


SQL> alter tablespace DEMO read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\>expdp transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp

Export: Release 12.1.0.2.0 - Production on Tue Apr 18 14:16:06 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_tablespaces=DEMO directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace DEMO:
  C:\ORACLE\ORADATA\NP12\DEMO.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:03 2017 elapsed 0 00:00:50

Then, I copy the datafile(s) to the target location and Datapump import the metadata.


C:\>copy C:\oracle\oradata\np12\DEMO.DBF C:\oracle\oradata\db122\DEMO.DBF
        1 file(s) copied.

C:\>impdp transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp

Import: Release 12.2.0.1.0 - Production on Tue Apr 18 14:17:27 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA transport_datafiles=C:\oracle\oradata\db122\DEMO.DBF directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Apr 18 14:17:46 2017 elapsed 0 00:00:15

And voila, there is my tablespace in the target database…


C:\>sql122

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 18 14:19:08 2017

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

Last Successful login time: Tue Apr 18 2017 14:14:19 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



SQL> select * from dba_tablespaces
  2  where tablespace_name = 'DEMO'
  3  @pr
==============================
TABLESPACE_NAME               : DEMO
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 1048576
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  : 0
MIN_EXTLEN                    : 1048576
STATUS                        : READ ONLY
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : UNIFORM
PLUGGED_IN                    : YES
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

“Gee thanks, Connor” I can hear you muttering. A demonstration of the flippin’ obvious!

But there is one thing that is not apparent from the export or import logs. Let’s take a squizz at the database alert log for the target, that is, the database we imported the tablespace into.


DW00 started with pid=69, OS id=632, wid=1, job SYS.SYS_IMPORT_TRANSPORTABLE_01
2017-04-18T14:17:34.208631+08:00
Plug in tablespace DEMO with datafile
  'C:\oracle\oradata\db122\DEMO.DBF'
2017-04-18T14:17:46.199645+08:00
ALTER TABLESPACE "DEMO" READ WRITE
Completed: ALTER TABLESPACE "DEMO" READ WRITE
2017-04-18T14:17:46.665512+08:00
ALTER TABLESPACE "DEMO" READ ONLY
Completed: ALTER TABLESPACE "DEMO" READ ONLY

That is a change in 12c. Whilst our imported tablespace ends up as read only as it has always done, during the import process, there was a small window where the tablespace was READ WRITE. This is needed to make some metadata corrections to the tablespace on the way in.

So if you do intend to share tablespaces between databases, that is, share a single copy of the file, make sure take some precautions. On my Windows laptop, standard Windows file locking prohibited me from causing any damage to my source datafile, but on other platforms you might to set those files to read only at the OS level just in case. Of course, you’ll then see a warning during the Datapump import saying that the momentary change to read/write could not be done, but that is not a critical problem.  The transport will still complete.

12c Release 2, set feedback enhancement in SQL PLus

There’s a nice little touch to the “set feedback” command in SQL PLus in 12.2. There is a new “only” clause in the SET FEEDBACK command, so you can run queries but the resulting rows are not shown in the display. They are still queried, fetched and “returned”, but just rendered to the screen. Here’s an example


$ sqlplus hr/hr

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 14 22:59:15 2017

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

Last Successful login time: Sat Mar 11 2017 01:59:20 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

So that’s normal behaviour. Let’s now use the new ONLY option.


SQL> set feedback only 
SQL> select * from regions;

4 rows selected.

So why would you want that ? Well, sometimes you just want to run the query so that you can use a subsequent DBMS_XPLAN.DISPLAY_CURSOR call to see the true execution plan. Or perhaps, you just to want to examine some options with regard to the fetch performance. For example, here’s a demo of fetching from a large table called EMP


SQL> show arraysize
arraysize 10

SQL> set timing on
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:26.27

So that took 26 seconds, with an arraysize of 10. Let’s see if we can do better than that – we’ll bump up the arraysize to 200


SQL> set arraysize 200
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.65

Wow, thats pretty cool. Six times faster just by tweaking the batch size of the fetch. Surely then we can just keep bumping it up.


SQL> set arraysize 5000
SQL> select * from emp;

1753088 rows selected.

Elapsed: 00:00:04.43

Apparently not. There is a “sweet spot” for arraysize, and diminishing returns on performance when you go higher and higher (at the cost of consuming memory and resources on your client machine to drag all those rows down at once). But this post isn’t about arraysize, it’s merely a conduit for the nice cool feature SET FEEDBACK ONLY which lets us now test out such things without having to see all the rows presented back.

12c Release 2 – Transparent Data Encryption online !

It doesn’t take a rocket scientist to know that even if you have incredibly stringent controls on user authentication, user authorisation etc, that wont save you if your data on disk is not encrypted.  All you need is an errant tape, a missing disk, a misplaced flash stick…and kersplat, someone has a copy of your datafiles from your Oracle database.

Data at rest should be encrypted, but that often meant taking applications offline to do so.

I’ve put my Speed Racer hat on Smile and here’s a video on a new 12c Release 2 feature covered in 60 seconds !

Oracle Database 12c Release 2 is here !

image

Since OpenWorld 2016 when we first saw some of the cool features in Oracle Database 12c Release 2, many IT professionals out there have been exploring the release via our various cloud offerings, but if your organization has not yet embraced the cloud, then March 2017 is a great month for you !  Because you can now download the latest and greatest release of our database from the usual downloads page, and run it on your own servers in your own data centre.

Of course, there’s a difference between downloading and installing the software, and being up to speed with all of the great features that have come in 12c and 12c Release 2 so keep an eye on my blog, and on my YouTube channel.

Over the coming weeks I’ll be talking about lots of the new features in 12.2 in a short easy to digest videos to help you appreciate all of the goodness that is 12c Release 2 Smile

To whet your appetite, how cool is this simple enhancement to SQL Plus – command history !



SQL> history
  1  select * from dba_users where username = 'SYSTEM';
  2  select count(*)
     from dba_tables
     where table_Name like 'A%';
  3  select * from tab;
  4  show sga
  5  set timing off
  6  select * from dba_tablespaces;

SQL> history 4 run

Total System Global Area 1048576000 bytes
Fixed Size                  8795840 bytes
Variable Size             251660608 bytes
Database Buffers          784334848 bytes
Redo Buffers                3784704 bytes

Extending Flashback Data Archive in 12c

Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.

Create a tablespace to hold your history, and create a flashback archive using that space allocation.



SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB11\ARCH_SPACE.DBF' 
  3  size 100M;

Tablespace created.



SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created.

Then add your tables to the archive. Some tables will be created automatically to support being able to record all of the changes to that table for as long as the archive duration specifies


SQL> ALTER TABLE EMP FLASHBACK ARCHIVE LONGTERM;

Table altered.

SQL> select table_name
  2  from   user_tables
  3  /

TABLE_NAME
---------------------------
SYS_FBA_HIST_71036
SYS_FBA_TCRV_71036
SYS_FBA_DDL_COLMAP_71036
EMP

And then voila! We can track changes to our table over time, well beyond the typical undo_retention setting for the database.


SQL> select empno, ename, job, sal, comm,
  2      nvl(VERSIONS_STARTTIME,LAST_MOD) TS
  3     ,nvl(VERSIONS_OPERATION,'I') op
  4  from EMP
  5  versions between timestamp
  6    timestamp '2014-02-11 20:12:00' and
  7    systimestamp
  8  order by empno;


     EMPNO ENAME      JOB              SAL       COMM TS           O
---------- ---------- --------- ---------- ---------- ------------ -
      7369 SMITH      CLERK            806            08.10.51 PM  I
      7369 SMITH      SALES           8060       1000 08.12.10 PM  U
      7499 ALLEN      SALESMAN        1606  300000000 08.10.51 PM  I
      7521 WARD       SALESMAN        1256  500000000 08.10.51 PM  I
      7566 JONES      MANAGER         2981            08.10.51 PM  I
      ...
      7900 JAMES      CLERK            956            08.10.51 PM  I
      7902 FORD       ANALYST         3006            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.10.51 PM  I
      7934 MILLER     CLERK           1306            08.12.10 PM  D

But sometimes we want to know more about the history. We want to know who did the change, what machine were they on, etc etc. Auditors tend to be a little snippy about those kind of things Smile

  • You: “I can confirm that the SALARY field was updated at 9:01am on January 25th”
  • Auditor: “And who did that change?”
  • You: “Er….um…..someone”

But it might not be practical to augment all of your tables with WHO, WHEN, WHERE etc columns to capture that information. That might involve a lot of application changes and testing.

If you’re on 12c, you might be in luck. Flashback Data Archive can capture additional information, assuming your application is taking advantage of the myriad of instrumentation facilities offered by the database.

We simply turn on context tracking, which can capture information on a per transaction basis


SQL> begin
  2    dbms_flashback_archive.set_context_level(
  3        level=> 'ALL');
  4  end;

PL/SQL procedure successfully completed.

Now when we perform changes to the table, we can look at the transaction ID (XID) for that change.


SQL> update EMP
  2  set    sal = sal*10
  3  where  empno = 7499;

1 row updated.

SQL> commit;


SQL> select XID from SYS_FBA_HIST_510592;

XID
----------------
09000B00C7080000

And using that XID, we can pick up the context information about that transaction


SQL> set serverout on
SQL> begin
  2    dbms_output.put_line(
  3      dbms_flashback_archive.get_sys_context (
  4         '09000B00C7080000', 'USERENV', 'SESSION_USER'));
  5
  6    dbms_output.put_line(
  7      dbms_flashback_archive.get_sys_context (
  8         '09000B00C7080000', 'USERENV', 'HOST'));
  9
 10    dbms_output.put_line(
 11      dbms_flashback_archive.get_sys_context (
 12         '09000B00C7080000', 'USERENV', 'MODULE'));
 13  end;
 14  /
SCOTT
WORKGROUP\XPS
SQL*Plus

How much more ? Well, quite a lot actually !


SQL> desc SYS.SYS_FBA_CONTEXT_AUD

 Name                      Null?    Type
 ------------------------- -------- ------------------
 XID                                RAW(8)
 ACTION                             VARCHAR2(256)
 AUTHENTICATED_IDENTITY             VARCHAR2(256)
 CLIENT_IDENTIFIER                  VARCHAR2(256)
 CLIENT_INFO                        VARCHAR2(256)
 CURRENT_EDITION_NAME               VARCHAR2(256)
 CURRENT_SCHEMA                     VARCHAR2(256)
 CURRENT_USER                       VARCHAR2(256)
 DATABASE_ROLE                      VARCHAR2(256)
 DB_NAME                            VARCHAR2(256)
 GLOBAL_UID                         VARCHAR2(256)
 HOST                               VARCHAR2(256)
 IDENTIFICATION_TYPE                VARCHAR2(256)
 INSTANCE_NAME                      VARCHAR2(256)
 IP_ADDRESS                         VARCHAR2(256)
 MODULE                             VARCHAR2(256)
 OS_USER                            VARCHAR2(256)
 SERVER_HOST                        VARCHAR2(256)
 SERVICE_NAME                       VARCHAR2(256)
 SESSION_EDITION_NAME               VARCHAR2(256)
 SESSION_USER                       VARCHAR2(256)
 SESSION_USERID                     VARCHAR2(256)
 SESSIONID                          VARCHAR2(256)
 TERMINAL                           VARCHAR2(256)
 SPARE                              VARCHAR2(256)

And since we can use our flashback query syntax to pick up all of the transaction ID’s


SQL> select empno, ename, job, sal, comm,
  2      VERSIONS_XID
  3  from EMP
  4  versions between timestamp
  5    timestamp '2014-02-11 20:12:00' and
  6    systimestamp
  7  order by empno;

     EMPNO ENAME      JOB              SAL       COMM VERSIONS_XID
---------- ---------- --------- ---------- ---------- ----------------
      7369 SMITH      CLERK            806
      7369 SMITH      SALES           8060       1000 09001C00E04A0000
      7499 ALLEN      CLERK          16060  300000000 0A000A0024080000
      7499 ALLEN      SALESMAN       16060  300000000 09000B00C7080000
      7499 ALLEN      SALESMAN        1606  300000000
      7521 WARD       SALESMAN        1256  500000000

we can also take advantage of that new dictionary view SYS.SYS_FBA_CONTEXT_AUD to get all the context information for each transaction.


SQL> select ...
  2  from
  3  (  select
  4         empno, ename, job,
  5         sal, comm, VERSIONS_XID
  6     from EMP
  7     versions between timestamp
  8        timestamp '2014-02-11 20:12:00' and
  9        systimestamp
 10  ) e, SYS.SYS_FBA_CONTEXT_AUD  a
 11  where e.VERSIONS_XID = a.XID;

Flashback Data Archive is pretty nifty in 12c.

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01011

A 12.2 treat for the festive season

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.

Unless you’re on 12.2.

Here’s a demo where we can take an existing table and

  • convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
  • take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
  • take the other existing index, and globally partition it.

Oh…and of course, we will do the whole thing online without service disruption

Oh…and of course, we will do it with a single SQL command.

Ab…So….Lute….Ly awesome !



SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 22 09:53:37 2016

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

Last Successful login time: Mon Dec 19 2016 13:38:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table T as
  2  select d.*
  3  from dba_Objects d,
  4       ( select 1 from dual
  5         connect by level <= 20 )   6  where d.object_id is not null; Table created. SQL> create index IX on t ( object_id );

Index created.

SQL> create index IX2 on t ( created, object_name );

Index created.

SQL> alter table T modify
  2  partition by range (object_id) interval (10000)
  3  (
  4    partition p1 values less than (20000)
  5  ) online
  6  update indexes
  7  ( ix  local,
  8    ix2 global partition by range (created)
  9    (
 10     partition ix2_p1 values less than (date '2016-08-01'),
 11     partition ix2_p2 values less than (maxvalue)
 12   )
 13  );

Table altered.

SQL> select partition_name
  2  from   user_tab_partitions
  3  where  table_name = 'T';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P1
SYS_P1145
SYS_P1146
SYS_P1147
SYS_P1148
SYS_P1149
SYS_P1150

7 rows selected.

SQL> select index_name, partition_name
  2  from   user_ind_partitions
  3  where  index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             SYS_P1145
IX                             SYS_P1146
IX                             SYS_P1147
IX                             SYS_P1148
IX                             SYS_P1149
IX                             SYS_P1150
IX2                            IX2_P1
IX2                            IX2_P2

9 rows selected.

SQL>