Remote DBA Plans
Remote DBA Service

Read-Only Tablespace

Oracle Tips by Burleson Consulting

Making a Tablespace Read-Only

One of the new features with later versions of Oracle7 and all versions of Oracle8 and oracle9i is the read-only tablespace. A read-only tablespace, as its name implies, allows read-only access to its information. This is beneficial in several ways:

* Since we are only reading data, no redo or rollback is required for read-only tablespaces.

* Read-only tablespaces only need to be backed up once after they are made read-only, then you can remove them from the backup plan.

* Read-only tablespaces can be left offline until just before they are needed (in Oracle8i or Oracle9i)--for example, on removable media such as CD-ROM or PD CD-ROM--and then brought online only when needed. Finally, read-only tablespaces do not participate in checkpoints.

Normally, you would have two related tablespaces, a data and an index tablespace, that you would want to make read-only. I would suggest dropping and rebuilding the indexes prior to making the index tablespace read-only, as well as correcting any space management problems such as chaining or excessive fragmentation on the data tablespace.

Of course, read-only tablespaces cannot be updated unless they are made readable again through the use of the ALTER TABLESPACE command.

The general procedure for making a tablespace read-only is:

1. Create the tablespace as a normal, permanent tablespace.

2. Populate the tablespace as you would a normal tablespace.

3. Once all data has been added to the tables in the tablespace, alter the tablespace to read-only.

4. Back up the tablespace using normal system backups.

Once a tablespace has been made read-only, you can transfer it to a media such as CD-ROM and then do a rename command on its datafile(s) using the ALTER DATABASE command to relocate them to the CD-ROM, as demonstrated here:

SQL> alter tablespace graphics_data read only;

Tablespace altered.

SQL> alter tablespace graphics_data offline;
Tablespace altered.

... Use operating system commands to copy file to new location ...

SQL> alter database rename file 'D:\ORANT8\DATABASE\ORTEST1_GRAPHICS_DATA01.DBF' to
2* 'H:\ORACLE5\ORTEST1\DATA\ORTEST1_GRAPHICS_DATA01.DBF'
SQL> /

Database altered.

... Here, just to be sure, I rename the actual file I copied from to: graphics_data01.old using the operating system rename command.

SQL> alter tablespace graphics_data online;

Tablespace altered.

In this example, notice that I renamed the file to a location on the h: drive (this happened to be a Panasonic PD CD-ROM drive). In the next example, I will show how to use the new initialization parameter READ_ONLY_OPEN_DELAYED.

Use of READ_ONLY_OPEN_DELAYED with Read-Only Tablespaces

In this section I introduce the new initialization parameter that deals specifically with read-only tablespaces: READ_ONLY_OPEN_DELAYED. This parameter tells Oracle to allow startup of the database even if some or all of the read-only tablespaces are offline or unavailable (for example, someone was using the CD-ROM player to listen to Bruce Springsteen and forgot to slip the CD with your read-only tablespace back in before startup).

At this juncture, I ask you to take a few things on faith because it is rather hard to show in print actual activities. To test this, I first issued the following commands to demonstrate that the read-only tablespace GRAPHICS_DATA was online and had active data (albeit read-only data):

SQL> select table_name from Remote DBA_tables where tablespace_name='GRAPHICS_DATA';

TABLE_NAME
—————————————————
GRAPHICS_TABLE                                                                  INTERNAL_GRAPHICS                                                               BASIC_LOB_TABLE

3 rows selected.

SQL> desc graphics_Remote DBA.internal_graphics
Name                            Null?    Type
—————————————————————————————————————————————————————————— GRAPHIC_ID                               NUMBER
GRAPHIC_DESC                             VARCHAR2(30)
GRAPHIC_BLOB                             BLOB
GRAPHIC_TYPE                             VARCHAR2(10)

SQL> select graphic_id,graphic_desc from graphics_Remote DBA.internal_graphics where rownum<10;

GRAPHIC_ID GRAPHIC_DESC
——————————————————————————————————————————————————————————
         1 April book of days woodcut
         2 August book of days woodcut
         3 Benzene Molecule Graphic
         4 c20conto.gif
         5 cover11b.gif
         6 December book of days woodcut
         7 February book of days woodcut
         8 harris-c.gif
         9 HIV Virus Image

9 rows selected.

I then added the initialization parameter READ_ONLY_OPEN_DELAYED=TRUE (it defaults to FALSE) to the initialization parameter file and shut down the database. Once the database was shut down I opened the PD CD-ROM CD drawer with the PD CD-ROM disk that holds the GRAPHICS_DATA tablespace data file. I then restarted the database.

No error was generated even with the PD CD-ROM drawer open, thus making the GRAPHICS_DATA data file unavailable. I then issued the following commands to see what could be expected for attempting to access the off-line tablespace:

SQL> select graphic_id,graphic_desc from graphics_Remote DBA.internal_graphics where rownum<10;
select graphic_id,graphic_desc from graphics_Remote DBA.internal_graphics
*
ERROR at line 1:
ORA-01157: cannot identify data file 4 - file not found
ORA-01110: data file 4: 'H:\ORACLE5\ORTEST1\DATA\ORTEST1_GRAPHICS_DATA01.DBF'

After verifying that the datafile was in fact not available I simply reloaded the PD CD-ROM cartridge and reissued the command, all with the database on-line and active:

SQL> r
1* select graphic_id,graphic_desc from graphics_Remote DBA.internal_graphics where rownum<10

As you can see, the database performs as if nothing were wrong. This means a database can continue to be used even if something has happened to make your read-only tablespaces temporarily unavailable.

TIP : Be very careful using READ_ONLY_OPEN_DELAYED=TRUE. If the tablespace that is read-only is not accessed between startups, or if a database crash occurs, the tablespace may be made invalid and become non-recoverable. Leave the tablespace in this mode (non-accessed) for as short a time as possible.

Using Transportable Tablespaces

A feature added in Oracle8i was the ability to move a tablespace and its datafiles from one database to another. A transportable set of tablespace is one that is self-contained, for example, a set of data and index tablespaces, wherein all internal references between tables and indexes are resolved within that set of tablespaces. However, before you get too excited about this capability, you must be aware of these restrictions and limitations:

* The source and target database must be on the same platform (not the same machine, but the same platform "i.e., Sun, version 2.7").

* The source and destination database must have the same blocksize.

* The source and destination database must have the same character set (there is an undocumented event to allow this, but it is not supported.)

* There is no way to change the owner of the tablespace objects, so the owner(s) must have users set up in both databases.

* The tablespaces to be moved cannot contain bitmap indexes; these must be dropped before the move and rebuilt afterward.

* There is no way to rename the tablespace in transit; it must not already exist in the target database.

* Tablespaces containing nested tables and varrays cannot be transported.

* Both source and target must have compatibility at least set to 8.1, with the source being the same or earlier version than the target database. The actual versions do not matter as long as the compatibility setpoints are from same or earlier version on the source to the same or later version on the target database.

To check if the set of tablespaces is self-contained, Oracle has provided a stored package called DBMS_TTS that contains another package called TRANSPORT_SET_CHECK. This procedure is fed the names of the tablespaces in your transport set and verifies that the set is self-contained. For example, to check where AP_DATA and AP_INDEX tablespaces are a self-contained set, the command would be:

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AP_DATA,AP_INDEX', TRUE);

The TRUE entry in the TRANSPORT_SET_CHECK call corresponds to whether or not you wish to verify for constraints. Constraints have to be internally consistent, as do tables, indexes, and clusters. If there are violations in the set of tablespaces that prohibit there being a self-contained set, they will be written in human-readable form into the TRANSPORT_SET_VIOLATIONS table.

Once all of the tablespaces in your transport set are verified to be a self-contained set, you set them all to be read-only using the ALTER TABLESPACE command. Once all are set to read-only, a special type of export is performed that creates the data dictionary information for use in the target database. For our example tablespaces, the command to create the data dictionary export, including all triggers, constraints, and grants, would be:

Exp transport_tablespace=y tablespaces=ap_data,ap_index triggers=y constraints=y grants=y file-tts.dmp

Obviously, if you set grants, triggers, or constraints to n (no), then they are ignored and not exported. The default setting for the grants, triggers, and constraints options is y (yes).

The next step is to copy the tablespace datafiles and the export file to the target database platform. After the tablespace datafiles have been copied, the original tablespaces can be altered back to READ WRITE mode if desired.

The final step, which consists of these steps, is to “plug in” the tablespaces to the target Oracle8i database:

1. Put the target tablespace datafiles into the OFA structure of the target database so the database can find them.

2. Plug in the tablespaces by adding their metadata via an import into the target database from the export file created previously:

Imp transport_tablespace=y datafiles= (d:\oracle2\ap_db\data\ap_data01.dbf,e:\oracle3\ap_db\data\ap_index.dbf) file=tss_dmp tablespaces=(ap_data,ap_index) owners=(ap_Remote DBA);

The datafiles parameter must be specified to tell the target database where to find the transported tablespace datafiles; the tablespace’s and owner’s parameters are optional. Once the import completes, verify the import log for errors; then you can use the ALTER TABLESPACE command to make the tablespaces READ WRITE again (if desired, they are still in READ ONLY after the import completes). As with other export and import operations, a long list of parameters can be placed into a parameter file and referenced with the PARFILE parameter for ease of use.

Some things to watch for are:

* That rowids may no longer be unique after a transportable set of tablespaces are plugged in, as they are not regenerated.

* REF values are not checked when consistency is verified, so there may be dangling REF values if the REF targets are not self-contained within the transported set of tablespaces.

* BFILE values will be invalid unless the external files they refer to are also moved with the tablespace.

* Triggers, if exported, are exported without validity checks so they may generate an error on import if they are not self-contained in the transport set.

* Snapshot and replication data are not transported.

As long as you operate within these guidelines, transportable tablespaces will make it easier and faster than ever before to move data between databases, because it is much faster to do the data dictionary export and copy the files than to do any data extraction and reloading. Another advantage is that you carry the indexes with the transport set, so they do not have to be rebuilt (however, beware of duplicate rowids causing erroneous results).

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.