OracleNotes

Author: Biju

  • Oracle Tidbits – May 2016

    “Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in May 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use…

    #oratidbit 101: In a CDB, the UNDO_MANAGEMENT parameter must be AUTO. There is one active undo tablespace for a single-instance CDB. Cannot have separate UNDO tablespace for PDBs.
    #oratidbit To create a service for a PDB using the SRVCTL utility, use the add service command and specify the PDB in the -pdb parameter. If you do not specify a PDB in the -pdb parameter when you create a service, then the service is associated with the root.
    #oratidbit PDB management with Oracle EM Express is available starting with Oracle Database 12c Release 1 (12.1.0.2).
    #oratidbit There is a default temporary tablespace for the entire CDB. You optionally can create additional temporary tablespaces for use by individual PDBs.
    #oratidbit Specify STORAGE clause in CREATE PLUGGABLE DATABASE to specify the amount of storage that can be used by all tablespaces that belong to the PDB. Default is UNLIMITED.
    #oratidbit Cluster Verification Utility (CVU) is a command-line tool that you can use for preinstallation and postinstallation checks of your cluster environment. OUI runs CVU after the Oracle Clusterware installation to verify the environment.
    #oratidbit Oracle Cluster Registry (OCR) maintains cluster configuration information and configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases.
    #oratidbit For a fixed-size undo tablespace, Oracle Database automatically tunes the system to give the undo tablespace the best possible undo retention. For an automatically extensible undo tablespace, Oracle Database retains undo data longer than the longest query duration and the low threshold of undo retention specified by the UNDO_RETENTION parameter.
    #oratidbit Query V$UNDOSTAT.TUNED_UNDORETENTION to determine the amount of time for which undo is retained for the current undo tablespace.
    #oratidbit To use Oracle Flashback Transaction Query feature, supplemental logging must be enabled in the database – ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    #oratidbit When upgrading your database, DBUA expects that both the source (pre-12c) Oracle home and the destination (new 12c) Oracle home are owned by the same user.
    #oratidbit Starting with Oracle Database 12c, Oracle XML DB is a mandatory component of Oracle Database. You cannot uninstall Oracle XML DB, and there is no option to exclude it when you create an Oracle database.
    #oratidbit Starting with Oracle Database 11g Release 2 (11.2), setting JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run.
    #oratidbit A Global Data Services (GDS) configuration and its global services are created and managed using the GDSCTL command-line interface. GDS is a highly effective solution for automatic workload management across a set of replicated databases.
    #oratidbit Oracle NoSQL Database is available in Community Edition (CE) and Enterprise Edition (EE). Only EE has integration with Oracle Stack such as Oracle Database External Table Integration, Oracle Big Data SQL integration, and so on.
    #oratidbit You can model data in Oracle NoSQL Database by using Tables, JSON schemas or a raw key-value interface. Tables are the easiest way to model data and they provide the highest level of abstraction.
    #oratidbit Setting database initialization parameter STATISTICS_LEVEL to BASIC disables many Oracle Database features, including ADDM. Setting parameter CONTROL_MANAGEMENT_PACK_ACCESS to NONE also disables ADDM.
    #oratidbit ADDM analysis of I/O performance partially depends on DBIO_EXPECTED, which is the average time it takes to read a single database block in microseconds – default is 10 milliseconds.

  • EBS R12.1.3 Post-upgrade Invalid Objects

    It is common to have many new invalid objects after an EBS upgrade, and many DBAs take it as the new norm. I think a little bit of research can help to clean out the invalid objects – in fact most of the custom objects that are invalid are pretty much unused by the application and can be dropped right away (take a backup of the code).

    We can drop many seeded objects also. Here is a list of invalid seeded objects we found after an EBS upgrade, and related MOS notes identifying them as not needed for application functionality in EBS 12.1.

    • Drop ARP_DDREMIT_FMT_PKG – Refer to Doc ID 1534108.1
    • Drop AP_CCE_BANK_UPGRADE – Refer to BUG 9679899
    • Drop CSE_PROJ_TRANSFER_PKG – Refer to Doc ID 1098989.1
    • Drop CZ_IMP_SINGLE_DEBUG – Refer to Doc ID 398525.1
    • Drop EGO_DEMO_PUB – Refer to Doc ID 2079240.1
    • Drop FA_JOURNALS_PKG – Refer to Doc ID 1078410.1
    • Drop GL_BALANCE_FIX – Refer to Doc ID 1540767.1
    • Drop OE_CREDIT_CARD_MIGRATE_UTIL – Refer to Doc ID 603741.1
    • Drop OKS_CCENCRYPTION_PKG – Refer to Doc ID 413213.1
    • Drop PA_AP_XFER_PKG – Refer to Doc ID 1674338.1
    • Drop ZPB_BUILD_METADATA – Refer to Doc ID 1951646.1
    • Drop view GMF_END_PRD_HLTH_CHK_MAIN_V – Refer to Doc ID 1954028.1
    • Follow the instructions in “APPS.MSC_DEMANTRA_PKG PACKAGE WILL NOT COMPILE (Doc ID 1067492.1” to make MSC_DEMANTRA_PKG valid
    • Package body AP_PAYMENT_EVENT_WF_PKG do not have package definition. Follow instructions in “PACKAGE APPEWFPB.PLS MISSING AFTER THE UPGRADE TO 12.1.1 (Doc ID 1180533.1)”
    • Follow the instructions in ” Unable to Compile WMS_EPC_PVT get ‘MGD_IDCOMPONENT_VARRAY’ Must be Declared Error (Doc ID 1070991.1″ to make WMS_EPC_PVT valid.

    There may be more depending on the patches applied and modules enabled in your database. Do a search with the invalid object name on MOS. Most likely you will find a note.

    Hope this helps…

    B