Category Archives: storage

Recover HCC Compressed Tables to non Exadata storage

Posted on by
7

Is this for me? (always helpful for busy people)

As many of you know by now, Exadata comes with an exclusive way to compress information, based on columns, and with a different storage unit called “Compression Unit”, that responds by the name of Hybrid Columnar Compression (HCC). The only storage in the world that can read this compressed information is Exadata Storage Server. So if you have your main production database inside an Exadata box, and you want to duplicate that database to non-Exadata storage (for instance SAN), then this article is for you.

Stuff you need to know first:

- As of this day Golden Gate development teams haven’t been able to capture information from compressed tables (HCC or other Oracle compression algorithm) but they are working on it

- If you use standby database (Data Guard) either in active or on mount mode you need to do these steps first, before you start syncronizing

- HCC is a very good compression technique. I’ve seen hundreds of examples and very few of them don’t have a 10 to 20 average compression ratio. So you need to have 10 to 20 times the space in your non-Exadata storage before you consider using the following steps. The only way to compensate the need for that amount of space is to “explode” the HCC tables to OLTP compressed tables. I’ll show you that too.

The Steps

These steps were done inside an Exadata X2-2 Full Machine on a server pool with 4 nodes, but can be reproduced on any other Exadata V2/X2 Machine.

The following example will:

1. Create a tablespace called “knod”

2. Create a table “lisboa” inside the tablespace, with an HCC compression level of 2 (Query High). The table will be loaded during creation with the view “DBA_EXTENTS” to create some volume (28674 rows).

3. Backup the tablespace “knod” with RMAN to the FRA that resides on the ASM diskgroup called “RECO” (inside Exadata).

4. Restore the tablespace from Exadata to a non-Exadata storage, using RMAN.

5. Query the table and get the ORA-64307 Error.

6. Try to move the table back to a tablespace inside Exadata (called “tbs_with_hcc”) and see that it can’t read it either, throwing the 64307 error again.

7. Try to DML on the table and watch that only the INSERT statement would work, because you can add data, you just can’t read the one that’s there.

8. To put you out of your misery the table will be decompressed (it could be recompressed with OLTP compression too in this stage)

9. And now you can query or DML on top of the table that everything is working normally (the table was decompressed from it’s HCC format).

10. Finally we count the bytes it takes up (3 MB), OLTP compress it and count them again (0.625) to see that the table was 3 times smaller.

Main Conclusion: You don’t have to “explode” your HCC tables and then OLTP compressed them, you can go *directly* from HCC on Exadata to OLTP compression on non-Exadata storage.

The Script

SQL> create tablespace knod datafile '+DATA_DM01' size 1g;

Tablespace created.

SQL> create table lisboa
  2  tablespace knod
  3  compress for query high
  4  as
  5  select * from dba_extents
  6  ;

Table created.

Elapsed: 00:00:12.00
SQL> select count(*) from lisboa;

  COUNT(*)
----------
     28674

RMAN> backup tablespace knod;

Starting backup at 23-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/orcl/datafile/knod.3800.746570399
channel ORA_DISK_1: starting piece 1 at 23-MAR-11
channel ORA_DISK_1: finished piece 1 at 23-MAR-11
piece handle=+RECO/orcl/backupset/2011_03_23/nnndf0_tag20110323t204617_0.366.746570777 tag=TAG20110323T204617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAR-11

---

run {
sql 'alter tablespace "KNOD" offline immediate';
set newname for datafile '+DATA/orcl/datafile/knod.3799.746573393' to '/home/lmc/rec/knod.dbf';
restore tablespace 'KNOD';
switch datafile all;
recover tablespace 'KNOD';
sql 'alter tablespace "KNOD" online';
}

---

SQL> desc lisboa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select count(*) from lisboa;
select count(*) from lisboa
*
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> alter table lisboa move tablespace tbs_with_hcc;
alter table lisboa move tablespace tbs_with_hcc
            *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> insert into lisboa select * from dba_extents where rownum < 2;

1 row created.

SQL> commit;

SQL> update lisboa
  2  set owner = owner;
update lisboa
       *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

SQL> delete lisboa where owner = 'KUS';
delete lisboa where owner = 'KUS'
       *
ERROR at line 1:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

------------------- BUT !!!!!!!!!!!!! ----------------------
If you alter table move 'it with NOCOMPRESS things will be different:

SQL> alter table lisboa move nocompress;

Table altered.

SQL> select count(*) from lisboa;

  COUNT(*)
----------
     28675

-- Now I can even do all the DML I can:

SQL> r
  1* update lisboa set owner = 'USK' where owner = 'SYSMAN'

1951 rows updated.

SQL> commit;

Commit complete.

SQL> delete lisboa where owner = 'USK';

1951 rows deleted.

SQL> commit;

Commit complete.

-- ****************************************************************
SQL> select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LISBOA';
SUM(BYTES)/1024/1024
--------------------
                   3

SQL> alter table lisboa move compress for oltp;

Table altered.

SQL>  select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LISBOA';

SUM(BYTES)/1024/1024
--------------------
                .625

-- **************************************************************

—

Feel free to bash or inquire.

LMC

Exadata Storage Foundation

Posted on by
Reply

Exadata is software. It’s the secret sauce in the Database Machine solution, but actually aside from all the fancy stuff that it does (HCC, Storage Indexes, or Smart Scan), it has to present the physical disks back to the ASM layer, so the party can begin upstairs at the database level.

So how it all happens?

First Exadata picks each physical disk and turns it into an entity called Cell Disk. This entity is a “kind-of” formatting. After you have created your Cell Disk you can chop it into smaller entities called Grid Disks. The Grid Disks themselves are still raw, unformatted slots of space in a bigger physical disk, but a Grid Disk always maps into one and only one physical disk.

The Grid Disks are always created with an ASM intention behind. Just because these areas in each and every physical or cell disk, will map directly into a ASM Disk, and in order to enable redundancy you have to make sure you are using all physical disks from all cells. It doesn’t make any sense create an ASM Disk Group based only on Grid Disks from a single cell. It’s possible to do so, but it’s senseless.

In a quarter-rack, the default configuration from Oracle very own installation services creates by default three ASM Disk Groups: DATA, RECO and SYSTEM. Each of these ASM Disk Groups, have all it’s Grid Disks created across all cell disks from all cells. The last time I’ve explained this to a customer I did two drawing: one having all disks divided into smaller units called grid disks (with the respective default sizes in gigs on the side) and other with the physical disk numbering on a given cell:


Hope to have shed some light over this, and to the storage guys: please stop crying about not having RAID and low-level copies. You don’t need them for database!!

LMC.

New Physical Database Limits of 11.2

Posted on by
Reply

It has been a while since I’ve gone through the documentation part of Oracle Database Server that talks about limits! I think the last one was when the rowid format changed from Oracle7 to Oracle8.

This post is not just about publishing those in another website, it’s commenting them. If you want to read the original text click here, if you want to read my remarks on the limitations, keep scrolling down, they are in bold (but don’t expect rocket science though!)

Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Database Block Size Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment 2 blocks (just because one is for data other is for segment header)
Database Blocks Maximum per datafile Platform dependent; typically 222 – 1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022 (Here the bigfile tablespaces actually can index much more space with just one datafile. It's because the rowid lacks the datafile encoding hence saving more space to index more blocks)
Database files Maximum per database 65533 – May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents Maximum per dictionary managed tablespace 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents Maximum per locally managed (uniform) tablespace 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS Maximum Unlimited (you should put a brake here not letting the segment have more than 100,000 extents on huge objects; 10,000 on big ones and less than 1,000 on average sized ones: typically huge is terabyte sized, big is hundreds of gigs and average sized less than 10 gigs)
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit (always change the default setting specially in a clustered environment)
Redo Log Files Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 4 MB (always start with at least 100MB on OLTP and 500MB on DWH shops)
Redo Log File Size Maximum Size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces Number of blocks A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces Number of blocks A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file Maximum size Dependent on the operating system.An external table can be composed of multiple files.

Slice and Dice bigfile tablespaces with RMAN

Posted on by
3

Back in 2003 when Oracle 10g was released and bigfile tablespaces were first introduced, not many databases were in the terabyte or petabyte weight-class. The very feature of having your data warehouse downsized in the number of files through the use of bigfile tablespaces, was not considered very useful unless you would use Automatic Storage Management (ASM).

But those who believed that having a tablespace terabyte sized, in a single datafile, crashed their belief in the moment of having to do a backup. Why? Because in Oracle 10g you couldn’t do the backup in parallel of a single datafile. Better said: you couldn’t use the RMAN multi-channelling feature because a single datafile had to go through the same pipe in the whole backup process.

It’s typical in the technology arena to create new features that create new needs, that will be solved and made easier, through the introduction of, yet, new features. So in that same line of thought, Oracle 11g introduced the capability of doing a RMAN multi-channelling, hence parallel, backup of a single datafile, by slicing and dicing it into smaller chunks.

How can we do that? It’s simple: add the following clause to the RMAN command “BACKUP”:

SECTION SIZE <size in bytes>

To give you some examples for the see-it-to-believe-it folks why not give some URLs from the hard-working bloggers (not lazy like me!):

http://jhdba.wordpress.com/2010/04/06/reducing-rman-backup-time-for-unevenly-sized-tablespaces/

Oracle Base Web-Site

Interesting article on Oracle Magazine

If you want to Google it, try: “Multi-section backups”. You’ll speed up the time it takes to backup your huge datafiles in your even bigger database.

Hope it helped some one out there.

LMC.

Deferred Segment Creation: segmentless tables (11gR2 new feature) and dumb questions

Posted on by
6

Much have been said about segmentless tables but most people have been asking me how does that work, as if that would be a complicated feature.

From 11g Release 2 all tables created without rows, do not create any segments (by default). If you want to change this behaviour, tweak the spfile parameter “deferred_segment_creation” to FALSE. Anyway the people installing Peoplesoft, SAP, Siebel and other thousand-table data models, really do thank for this new feature.

How does it work?

If the INITIAL extent of a table is 15 MegaBytes in size, but the table is empty, the table won’t ask for that 15 MB of space. As soon as the first row of data is inserted (even a few bytes), the Oracle space engine will run the storage space settings of the table, before inserting that line, making the table actually owner of that space. You can create a 100GB table onto a 100MB tablespace as long as the table is empty, because the space will only be reclaimed by the table when it receives the first row.

Take a look at an example I’ve prepared to show the basic behaviour:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table hr.xpto (n number, n2 varchar2(90))
  2  storage (initial 15m) tablespace example;
Table created.
SQL> r
  1* select blocks, bytes/1024/1024, segment_name, segment_type from dba_segments where segment_name = 'XPTO'
no rows selected
SQL> save expto

Created file expto.sql

Why aren’t there any blocks for the table? Watch this parameter:

SQL> show parameter deferred
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

If I don’t have 15BM in the tablespace EXAMPLE the INSERT will fail since that assessment would only be made now:

SQL> insert into hr.xpto values (1234,'Some String');
insert into hr.xpto values (1234,'Some String')
 *
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace EXAMPLE

As soon as I give more space to the tablespace, the insertion goes smoothly and the space is reclaimed:

SQL> insert into hr.xpto values (1234,'Some String');
1 row created.
SQL> @expto
    BLOCKS BYTES/1024/1024 SEGMENT_NAME         SEGMENT_TYPE
---------- --------------- -------------------- ------------------
      1920              15 XPTO                 TABLE

I love dumb questions!

Here’s the 1.000.000 Euro question, which is simultaniously the dumbest:

“Will Oracle turn the table into segmentless again when I delete all rows?”

I love this kind of line of thought that you can only find in very honest and dumbest people! But they’re people, and they have sentiments,

so you should respect their lines of thought and don’t reject them at start.

I learn a lot from the dumbest questions people make, and so I stimulate people to ALWAYS ASK.

So let me prepare an answer to that question in 2 sentences:

“No. Oracle won’t remove all segments if you clear the table.”
“Yes, if you want to downsize the table segment-wise, you can do it. Yes you can!”

Want to see it?

SQL> truncate table hr.xpto;
Table truncated.
SQL> @expto
    BLOCKS BYTES/1024/1024 SEGMENT_NAME         SEGMENT_TYPE
---------- --------------- -------------------- ------------------
      1920              15 XPTO                 TABLE

Truncating the table didn’t shronk the table. It still has the 15MB.

Now let’s try to shrink it in two methods: 1) Explicitly deallocating all the extents 2) Shrink clause

1) Deallocate:

SQL> alter table hr.xpto deallocate unused keep 0;
Table altered.
SQL> @expto
    BLOCKS BYTES/1024/1024 SEGMENT_NAME         SEGMENT_TYPE
---------- --------------- -------------------- ------------------
        24           .1875 XPTO                 TABLE

When were left with 24 blocks, which is one segment of 192KB.

2) Shrink clause:

We have to enable row movement first:

SQL> alter table hr.xpto enable row movement;
Table altered.
SQL> alter table hr.xpto shrink space;
Table altered.
SQL> @expto
    BLOCKS BYTES/1024/1024 SEGMENT_NAME         SEGMENT_TYPE
---------- --------------- -------------------- ------------------
        24           .1875 XPTO                 TABLE

Exactly the same.

But still, it ain’t possible to transform a table that once had a segment into a segmentless object.

LMC.