Wednesday, February 25, 2015

DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled After Upgrading to 11.2.0.4 Standard Edition

"DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" message could be seen while using export data dump and import data pump after upgrading a 11.2.0.3 standard edition database to 11.2.0.4 standard edition.
expdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/testupg/dpdump/sct.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 28 13:00:10 2015 elapsed 0 00:00:09
The main reason for this is the OLAP components are not enabled for SE but could get installed if a database template is used (eg. online transaction processing template) and becomes invalid after the upgrade. A similar problem was observed earlier with regard to dbms_cube_exp.schema_info_imp_beg error.
MOS note 1638799.1 these messages are intentionally output for 11.2.0.4 SE1 and SE and could be safely ignored. But if it's desired not to have the message output then the package could be deleted with (take a full database backup before deleting)
delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';
commit;
@?/rdbms/admin/utlrp
This is mentioned in other MOS notes (1921158.1,726637.1,1675617.1) though not directly related to this warning message. After the delete the expdp and impdp doesn't result in any warning messages
expdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Export: Release 11.2.0.4.0 - Production on Wed Jan 28 13:02:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/testupg/dpdump/sct.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 28 13:03:01 2015 elapsed 0 00:00:06

impdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Import: Release 11.2.0.4.0 - Production on Wed Jan 28 13:05:31 2015

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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.562 KB      14 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Wed Jan 28 13:05:33 2015 elapsed 0 00:00:02
At the same time it would also be useful in clearing all OLAP related invalid objects. Invalid objects could be identified with
select owner, object_name, object_type from dba_objects where status <> 'VALID' order by owner, object_type;
Since OLAP components are not installed in a SE home running the component removal scripts will fail
SQL> @?/olap/admin/catnoamd.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoamd.sql"
SQL> @?/olap/admin/olapidrp.plb
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/olapidrp.plb"
SQL> @?/olap/admin/catnoxoq.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoxoq.sql"
SQL> @?/olap/admin/catnoaps.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoaps.sql"
SQL> @?/olap/admin/cwm2drop.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/cwm2drop.sql"
It is possible to copy in these files from a EE home and run to remove the OLAP components or manually drop the packages/synonyms and finally drop the OLAP user (refer 1593666.1 and 1900113.1 before dropping OLAP user).

Useful metalink notes
Datapump Export (expdp) Raises Warnings Like "DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" [ID 1638799.1]
12.1 Export Gives EXP-8 ORA-29280 EXP-85 ORA-06512 "SYS.UTL_FILE" "SYS.DBMS_AW_EXP" [ID 1921158.1]
Traditional Export (EXP) and DataPump (expdp) Fail With ORA-4063: Package Body SYS.DBMS_AW_EXP Has Errors [ID 726637.1]
DBMS_AW_EXP: Ignoring APPS.ODPCODE During Schema Data Pump Export [ID 1675617.1]
How To Find Out If OLAP Is Being Used [ID 739032.1]
How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1]
How To Remove The OLAP Option In 10g And 11g [ID 332351.1]
CATNOAMD.SQL DOES NOT DROP OLAPSYS USER IN 11.2.0.4 [ID 1900113.1]
Invalid OLAPSYS Objects After Upgrading TO 12C [ID 1593666.1]
Removing Oracle OLAP from the Database does not Remove All OLAP Objects [ID 1060023.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
OWB Repository Upgrade To 11.2.0.4 Does Not Upgrade The Locations [ID 1637271.1]
Upgrading Oracle Warehouse Builder 11.2 - How To Upgrade From OWB 11.2.0.x To OWB 11.2.0.y [ID 1225254.1]

Sunday, February 22, 2015

SP2-0310: unable to open file catmmig.sql

Patch 19518079 was needed to reflect the fact that DB was upgraded from 12.1.0.1 to 12.1.0.2.However this patch is now included in the PSU 12.1.0.2.2. This result in an error when the post patch installation steps are carried out.
Patch 19518079 rollback (pdb CDB$ROOT): WITH ERRORS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19518079/18024100/19518079_rollback_ENT12C_CDBROOT_2015Feb06_17_47_56.log (errors)
    Error at line 27: SP2-0310: unable to open file "/opt/app/oracle/product/12.1.0/dbhome_2/sqlpatch/19518079/18024100/rollback_files/rdbms/admin/catmmig.sql"
Patch 19877336 apply (pdb CDB$ROOT): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19877336/18313828/19877336_apply_ENT12C_CDBROOT_2015Feb06_17_47_56.log (no errors)
Patch 19769480 apply (pdb CDB$ROOT): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19769480/18350083/19769480_apply_ENT12C_CDBROOT_2015Feb06_17_49_33.log (no errors)
Patch 19518079 rollback (pdb PDB$SEED): WITH ERRORS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19518079/18024100/19518079_rollback_ENT12C_PDBSEED_2015Feb06_17_49_40.log (errors)
    Error at line 57: SP2-0310: unable to open file "/opt/app/oracle/product/12.1.0/dbhome_2/sqlpatch/19518079/18024100/rollback_files/rdbms/admin/catmmig.sql"
Patch 19877336 apply (pdb PDB$SEED): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19877336/18313828/19877336_apply_ENT12C_PDBSEED_2015Feb06_17_49_40.log (no errors)
Patch 19769480 apply (pdb PDB$SEED): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19769480/18350083/19769480_apply_ENT12C_PDBSEED_2015Feb06_17_51_28.log (no errors)
Patch 19877336 apply (pdb PDB12C): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19877336/18313828/19877336_apply_ENT12C_PDB12C_2015Feb06_17_51_33.log (no errors)
Patch 19769480 apply (pdb PDB12C): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19769480/18350083/19769480_apply_ENT12C_PDB12C_2015Feb06_17_53_00.log (no errors)
Patch 19877336 apply (pdb PDB12CDI): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19877336/18313828/19877336_apply_ENT12C_PDB12CDI_2015Feb06_17_51_33.log (no errors)
Patch 19769480 apply (pdb PDB12CDI): SUCCESS
  logfile: /opt/app/oracle/cfgtoollogs/sqlpatch/19769480/18350083/19769480_apply_ENT12C_PDB12CDI_2015Feb06_17_53_00.log (no errors)
Rolling back of the patch fails with unable to open file catmmig.sql.



The log files shows
IGNORABLE ERRORS: NONE

INSTALL_FILE
--------------------------------------------------------------------------------
?/sqlpatch/19518079/18024100/rollback_files/rdbms/admin/catmmig.sql

SP2-0310: unable to open file "/opt/app/oracle/product/12.1.0/dbhome_2/sqlpatch/19518079/18024100/rollback_files/rdbms/admin/catmmig.sql"

PL/SQL procedure successfully completed.
The metalink notes listed at the end of the post gives a workaround for this which involves manually adding an entry to the registry$history to mention the upgrade.

Useful metalink notes
Oracle Quarterly Database Patch 12.1.0.2.4 Known Issues [ID 1942931.1]
Oracle Quarterly Database Patch 12.1.0.2.1 Known Issues [ID 1930503.1]
Bug 20421900 - catmmig.sql is missing from the 12.1.0.2.2/3/4 Engineered Systems / DB In-Memory Bundle Patch (DBBP) [ID 20421900.8]

Friday, February 20, 2015

Transportable Tablespaces With Segment Dependencies

Transportable tablespaces allow moving of tablespaces between databases. The transportable tablespace must contain all the segments within itself. If there are segment dependencies between tablespace this would require all the dependent tablespaces being moved. This post shows a steps in transporting three tablespaces which have segment dependencies between them.
1. Create three tablespaces in this case one for storing table data, one for indexes and one for lob segments.
create tablespace tabletbs datafile '+data(datafile)' size 10m;
create tablespace indextbs datafile '+data(datafile)' size 10m;
create tablespace lobstbs datafile '+data(datafile)' size 10m;
2. Grant the user quotas on the tablespaces
alter user asanga quota unlimited on tabletbs quota unlimited on indextbs quota unlimited on lobstbs;
3. Create the segments such that all tablespaces are used.
create table abc (a number, b varchar2(100)) tablespace tabletbs;

create table def (b number, c blob) tablespace tabletbs lob(c)
     STORE AS c_lob_seg (
        TABLESPACE lobstbs
        CHUNK 32K
        CACHE
        STORAGE (MAXEXTENTS UNLIMITED)
        INDEX c_lob_idx (
            TABLESPACE lobstbs
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    );  

create index aidx on abc(a) tablespace indextbs;
create index caidx on def(b)  tablespace indextbs;
4. Populate the tables
SQL> begin
      for i in 1 .. 1000
      loop
      insert into abc values(i,'abc'||i);
      end loop;
      commit;
      end;
      /

SQL> begin
      for i in 1000 .. 2001
      loop
      insert into def values(i,'def'||i);
      end loop;
      commit;
      end;
      /
5. If the full transportable tablespace set is not specified this will be mentioned transport set violation view. Below output shows the transport set violation when only the tabletbs is selected for transport.
SQL>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tabletbs',true);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------
ORA-39905: Table ASANGA.C_LOB_SEG in tablespace lobstbs points to LOB segment ASANGA.DEF in tablespace tabletbs.
When all the depended tablespaces are selected no errors are shown
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tabletbs,indextbs,lobstbs',TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
no rows selected
6. Change the tablespace to read only mode.
SQL> ALTER TABLESPACE tabletbs READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE indextbs READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE lobstbs READ ONLY;
Tablespace altered.
7. Export the tablespace metadata.
expdp  system/rac11g2db directory=exec_dir transport_tablespace=y tablespaces=tabletbs,indextbs,lobstbs dumpfile=tbs.dmp logfile=tbs.log

Export: Release 11.2.0.3.0 - Production on Wed Feb 18 11:39:22 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=tabletbs,indextbs,lobstbs"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=exec_dir tablespaces=tabletbs,indextbs,lobstbs dumpfile=tbs.dmp logfile=tbs.log reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/df/tbs.dmp
******************************************************************************
Datafiles required for transportable tablespace indextbs:
  +DATA/rac11g2/datafile/indextbs.269.871989879
Datafiles required for transportable tablespace lobstbs:
  +DATA/rac11g2/datafile/lobstbs.268.871989887
Datafiles required for transportable tablespace tabletbs:
  +DATA/rac11g2/datafile/tabletbs.270.871989869
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:40:38
8. Copy the data files belonging to the tablespaces being transported to remote host. Data files in ASM could be copied to local host with asmcmd's cp command and then copied remotely or use ftp service provided with XML DB.
 asmcmd cp +DATA/rac11g2/datafile/tabletbs.270.871989869 /home/oracle/tabletbs.270.871989869
copying +DATA/rac11g2/datafile/tabletbs.270.871989869 -> /home/oracle/tabletbs.270.871989869


9. Import the tablespace metadata in the remote host. Reflect the new location of the data files copied over in the transport_datafiles parameter.
impdp system/ent11g2db directory=exec_dir dumpfile=tbs.dmp logfile=imp.log 
transport_datafiles='/home/oracle/trntbs/indextbs.269.871989879','/home/oracle/trntbs/lobstbs.268.871989887','/home/oracle/trntbs/tabletbs.270.871989869'

Import: Release 11.2.0.3.0 - Production on Wed Feb 18 10:55:10 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=exec_dir dumpfile=tbs.dmp logfile=imp.log transport_datafiles=/home/oracle/trntbs/indextbs.269.871989879,/home/oracle/trntbs/lobstbs.268.871989887,/home/oracle/trntbs/tabletbs.270.871989869
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:55:13
10. After the import the tablespaces will be plugged in but in read only mode.
SQL> SELECT tablespace_name, plugged_in, status FROM   dba_tablespaces;

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
tabletbs                       YES READ ONLY
indextbs                       YES READ ONLY
lobstbs                        YES READ ONLY
Change tablespace mode to read write.
SQL> alter tablespace lobstbs read write;
Tablespace altered.

SQL> alter tablespace indextbs read write;
Tablespace altered.

SQL> alter tablespace tabletbs read write;
Tablespace altered.

SQL> SELECT tablespace_name, plugged_in, status FROM   dba_tablespaces;

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
tabletbs                       YES ONLINE
indextbs                       YES ONLINE
lobstbs                        YES ONLINE
11. The transported tablespaces are not ready for use.