6 Replies Latest reply on Feb 23, 2018 10:56 AM by Jibu-Oracle

    EBS R12.1.3 Nologging options

    Beauty_and_dBest

      EBS R12.1.3

      11gR2

      OL6

       

      Does Oracle EBS ERP system allows this dangerous option of nologging?

       

      I selected all objects with nologging options and I got many

       

      SQL> select count(*) from dba_tables where logging='NO';

       

        COUNT(*)

      ----------

            1789

       

       

      SQL>  select count(*) from dba_indexes where logging='NO';

       

        COUNT(*)

      ----------

            5043

       

       

      Maybe EBS allowed this to enable nologging options in some database objects to improve performance?

      But during backup/restore procedure we encountered error.

       

      We have restored full backup and applied daily archived logs to the last minute.

      But when we run gather schema stats, we encountered error below:

       

      +---------------------------------------------------------------------------+

      Application Object Library: Version : 12.1

       

      Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

       

      FNDGSCST: Gather Schema Statistics

      +---------------------------------------------------------------------------+

       

      Current system time is 22-FEB-2018 16:25:42

       

      +---------------------------------------------------------------------------+

       

      **Starts**22-FEB-2018 16:25:42

      **Ends**22-FEB-2018 16:28:15

      ORA-0000: normal, successful completion

      +---------------------------------------------------------------------------+

      Start of log messages from FND_FILE

      +---------------------------------------------------------------------------+

      In GATHER_SCHEMA_STATS , schema_name= MSC percent= 50 degree = 4 internal_flag= NOBACKUP

      Error #1: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_BOM_COMPONENTS***ORA-01578: ORACLE data block corrupted (file # 20, block # 192723)

      ORA-01110: data file 20: '/oracle/ASCP/db/apps_st/data/a_txn_data05.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #2: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_DEMANDS***ORA-01578: ORACLE data block corrupted (file # 20, block # 205443)

      ORA-01110: data file 20: '/oracle/ASCP/db/apps_st/data/a_txn_data05.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #3: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_ITEM_CATEGORIES***ORA-01578: ORACLE data block corrupted (file # 20, block # 203123)

      ORA-01110: data file 20: '/oracle/ASCP/db/apps_st/data/a_txn_data05.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #4: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_REGION_LOCATIONS***ORA-01578: ORACLE data block corrupted (file # 403, block # 259325)

      ORA-01110: data file 403: '/oracle/ASCP/db/apps_st/data/a_txn_ind02.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #5: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_ROUTING_OPERATIONS***ORA-01578: ORACLE data block corrupted (file # 405, block # 194237)

      ORA-01110: data file 405: '/oracle/ASCP/db/apps_st/data/a_txn_ind04.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #6: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_SUPPLIES***ORA-01578: ORACLE data block corrupted (file # 404, block # 214829)

      ORA-01110: data file 404: '/oracle/ASCP/db/apps_st/data/a_txn_ind03.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      Error #7: ERROR: While GATHER_TABLE_STATS:

      object_name=MSC.MSC_SYSTEM_ITEMS***ORA-12801: error signaled in parallel query server P002

      ORA-01578: ORACLE data block corrupted (file # 20, block # 210755)

      ORA-01110: data file 20: '/oracle/ASCP/db/apps_st/data/a_txn_data05.dbf'

      ORA-26040: Data block was loaded using the NOLOGGING option***

      +---------------------------------------------------------------------------+

      End of log messages from FND_FILE

      +---------------------------------------------------------------------------+

       

       

      +---------------------------------------------------------------------------+

      No completion options were requested.

       

      Output file size:

      0

       

      +---------------------------------------------------------------------------+

      Exceptions posted by this request:

      Concurrent Request for "Gather Schema Statistics" has completed with error.

       

      +---------------------------------------------------------------------------+

      Concurrent request completed

      Current system time is 22-FEB-2018 16:28:16

       

      +---------------------------------------------------------------------------+

       

       

      Please help how to handle this issue.

       

      Kind regards,

      jc

        

        • 1. Re: EBS R12.1.3 Nologging options
          Jibu-Oracle

          Hi jc,

           

          A generic query for nologging count on dba_tables or indexes will not be a good option to reach a conclusion.

          For certain programs the NOLOGGING option (as default) is used for performance gain on tables that have a large amount of transactions

           

          You may refer the following Note for detailed understanding,

          The Gains and Pains of Nologging Operations (Doc ID 290161.1)

           

          Hope it helps

           

          For the reported errors,  I would request to assistance from RDBMS team as block corruption is reported.

           

          Thanks

          Jibu

          • 2. Re: EBS R12.1.3 Nologging options
            Beauty_and_dBest

            The question is, why is our EBS database is in nologging options/mode? Is it recommended?

            I think this is the cause why some archivelogs are bypassed and made the backup/restore corrupted on some indexes or tables.

             

             

            select force_logging from v$database;

             

            FORCE_LOGGING

            -------------

            NO

            • 3. Re: EBS R12.1.3 Nologging options
              Kanda-Oracle

              I will check and revert meanwhile,

              Nologging in the E-Business Suite for Release 11i (Doc ID 216211.1)

               

              Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime (Doc ID 1581549.1)

               

              • 4. Re: EBS R12.1.3 Nologging options
                Jibu-Oracle

                Please check the value for following to check if your database has archive mode enabled

                SQL>archive log list

                 

                This feature can be enabled at TWO levels:

                - Database level(CREATE DATABASE ,ALTER DATABASE ..FORCE LOGGING)
                - Tablespace level(CREATE TABLESPACE,ALTER TABLESPACE ..FORCE LOGGING)


                Force_logging column in V$database view shows value yes if database is in a force logging mode.

                If the force logging is enabled at a database level,all the operations happening in the database are logged.
                This causes performance problems in case of heavy logging. In such cases,identify the important tablespaces and
                enable the force logging at the tablespace level.

                force_logging column in dba_tablespace view shows value yes if tablespace is in a force logging mode.

                Force logging option is by default there for undo tablespace so we need not enable the same.

                 

                Hope you can check the details to get more clarity for your case

                • 5. Re: EBS R12.1.3 Nologging options
                  Beauty_and_dBest

                  Thanks Kanda and Jibu,

                   

                  As long as you do not enable FORCE_LOGGING=YES  you are putting your PROD database in danger.

                   

                  My question is, if you choose performance FORCE_LOGGING=NO  over a consistent backup how do you handle this kind of issue?

                  ORA-26040: Data block was loaded using the NOLOGGING option***

                   

                   

                  Kind regards,

                  • 6. Re: EBS R12.1.3 Nologging options
                    Jibu-Oracle

                    As updated earlier, it is important to see output of following in production database

                    SQL>archive log list

                    Also need to if force_logging is enabled in tablespace level, which will be know querying dba_tablespace view.

                    Certain specialized tasks where logging is not required, such as manipulating data for data warehousing applications, or maintaining summary data for business intelligence queries.

                    From the output it shows certain data blocks as corrupted which required detailed review for necessary actions.