RDBPROD: INTEGRATE... ALTER FILES can cause loss of metadata

(Doc ID 1072469.1)

Last updated on AUGUST 03, 2016

Applies to:

Oracle CDD/Repository - Version 7.0 and later
Oracle Rdb Server on OpenVMS - Version 7.0 and later
HP OpenVMS VAX
HP OpenVMS Itanium
HP OpenVMS Alpha

Symptoms

Re-integrating an Rdb database using definitions from CDD (INTEGRATE with ALTER FILES) may result in the loss of some definitions in the database and possible data loss. Metadata, such as tables, indexes, and views, defined in the database while attached by filename rather than pathname could disappear, as well as the data in such a table. Errors during the integrate can also cause loss of metadata.

Note: Oracle strongly recommends a full RMU/BACKUP of the database be made prior to any metadata changes, including INTEGRATE with ALTER FILES.

Changes

Examples:

1. Tables and views
2. Indexes
3. Indexes used in storage maps

1. Tables and views.

Define a new table and a view on a table that is fully integrated in the CDD and the database while attached by filename rather than pathname. After defining the view, perform an INTEGRATE... ALTER FILES. The view no longer exists in the database.


$! define fields and record in the cdd. all definitions go to cdd$default.
$!
$ mcr cdo
CDO> define field V1 datatype text 5.
CDO> define field V2 datatype signed longword.
CDO> define record R1.
cont> V1.
cont> V2.
cont> end record.
CDO> Exit
$!
$! create the database by pathname
$!
$ mcr sql$
SQL> create database pathname cdd$default.DB1 filename DB1;
SQL> disconnect all
SQL> --
SQL> -- create table while attached by pathname
SQL> --
SQL> attach 'pathname DB1';
SQL> create table from R1;
SQL> commit;
SQL> --
SQL> -- attach by filename and define a new table and a view
SQL> --
SQL> disconnect all;
SQL> attach 'filename DB1';
SQL> create view VW1 (V3, V4) as select
cont> vV1, V2 from R1 where V2=1;
SQL> commit;
SQL> create table T1 (FLD1 char(5));
SQL> commit;
SQL> show table
User tables in database with filename DB1
R1
T1
VW1           A view.
SQL> Exit
$!
$! integrate with alter files - the table and view have disappeared
$!
$ mcr sql$
SQL> integrate database pathname DB1 alter files;
%CDD-I-INT_DELETE, object T1 will be deleted -- data may be lost
%CDD-I-INT_DELETE, object SQL$5CHR will be deleted -- data may be lost
SQL> commit;

SQL> disconnect all;
SQL> attach 'filename DB1';
SQL> show table
User tables in database with filename DB1
R1
SQL>



2. Indexes.

Define an index while attached by filename. Attach by pathname and define another index. Perform an INTEGRATE... ALTER FILES. The index that was defined while attached by pathname is present in the database, but the other index is gone.


$ mcr sql$
SQL> attach 'filename DB1';
SQL> show table
User tables in database with filename DB1
R1
SQL> create index I1 on R1(V2) type is sorted;
SQL> commit;
SQL> --
SQL> -- now disconnect and connect by pathname
SQL> --
SQL> disconnect all;
SQL> attach 'pathname DB1';
SQL> create index I2 on R1(V1) type is sorted;
SQL> show index
User indexes in database with pathname SYS$COMMON:[CDDPLUS]DB1;1
I1
I2
SQL> commit;
SQL> exit

$ mcr sql$
SQL> -- index I1 was created while attached by filename
SQL> -- index I2 was created while attached by pathname
SQL> --
SQL> integrate database pathname DB1 alter files;
%CDD-I-BLRSYNINFO, unsupported entity - marked Incomplete
SQL> commit;
SQL> show index
User indexes in database with pathname SYS$COMMON:[CDDPLUS]DB1;1
I2
SQL> -- index I1 was deleted by the integrate
SQL> Exit
$



3. Indexes used in storage maps.

Attach by filename and define an index that will then be used in a storage map for a placement via index. (Placement by a sorted index is not generally useful but will be used for the purposes of this example.)  An error results from the INTEGRATE... ALTER FILES. In this case, the index could not be deleted as it was in the previous example because it is used by a storage map, resulting in the integrate error.

$ mcr sql$
SQL> create database filename 'DBMF.RDB' pathname 'DB2'
cont> create storage area RDB$SYSTEM filename 'DBMF_SYS.RDA'
cont> create storage area DAT filename 'DBMF_DAT.RDA'
cont> create storage area IND filename 'DBMF_IND.RDA';
SQL> Exit
$

$ mcr sql$
SQL> attach 'pathname DB2';
SQL> create table from R1;
SQL> commit;
SQL> disconnect all;

SQL> attach 'filename dbmf';
SQL> create index I1 on R1(V1) type is sorted store in IND;
SQL> create storage map M1 for R1 store in DAT placement via index I1;
SQL> commit;
SQL> show indexes
User indexes in database with filename dbmf
I1
SQL> show storage maps
User Storage Maps in database with filename dbmf
M1
SQL> disconnect all;

SQL> integrate database pathname DB2 alter files;
%CDD-I-BLRSYNINFO, unsupported entity - marked Incomplete
%CDD-I-INT_ERROR, error during integrate caused by
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-INDINMAP, index "I1" is used in storage map "M1"
%CDD-I-INT_CONT, integrate continuing with next object
SQL> --
SQL> show indexes
User indexes in database with filename dbmf
I1
SQL> show storage maps
User Storage Maps in database with filename dbmf
M1
SQL>

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms