adstats.sql Fails With ORA-20011 and ORA-600

Performing database upgrade from 11.2.0.4 to 12.1.0.2 with EBS version R12.1.3. When I'm executing post DB upgrade step adstats.sql, its failed with following ORA internal error.

declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
[1350], [3], [23], [60], [AMERICAN], [0], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 36906
ORA-06512: at line 3
ORA-06512: at line 33
 

Looking at V$NLS_VALID_VALUES and it shows as,

SQL> select count(*) from V$NLS_VALID_VALUES;
select count(*) from V$NLS_VALID_VALUES
                                      *
ERROR at line 1:
ORA-00600: internal error code, arguments: [1350], [3], [23], [60], [AMERICAN],
[0], [], [], [], [], [], []

SQL>
 

Follow below steps to fix this issue:

1) Source the database environment file and check PERL5LIB variable is set correctly. If you are using RAC, then check all the nodes of a RAC environment.

Example:
 
 $ export 
 PERL5LIB=/u01/app/12102/perl/lib/5.10.0:/u01/app/12102/perl/lib/site_perl/5.10.0:/u01/app/12102/appsutil/perl
 

2) Run "perl $ORACLE_HOME/nls/data/old/cr9idata.pl" to create 9idata directory and set ORA_NLS10 parameter.

Example:
  
  $ export ORA_NLS10=/u01/app/12102/nls/data/9idata
 

3) Restart the database

4) Now run adstats.sql and also check at V$NLS_VALID_VALUES. If still issue exists please check with oracle support team.

That's it, hope this article will help you :).

Thanks,
Chowdari

Recreating the /appsutil/scripts/ directory in EBS

When I have executed $AD_TOP/bin/admkappsutil.pl to generate appsutil.zip file, I didn't find the scripts directory under $ORACLE_HOME/appsutil.
To fix this issue, need to run 'perl $ORACLE_HOME/appsutil/bin/adbldxml.pl' utility, this will create a new context file on the database tier.
The new context file acts as a central repository for database configuration information. The adbldxml Utility is only supported on the Database Tier, not on the Applications Tier.

With using newly generated DB context file need to run $ORACLE_HOME/appsutil/bin/adconfig.sh on DB tier.  Now the scripts directory will be created under  $ORACLE_HOME/appsutil directory.

Follow below detailed steps:

1. On the Application Tier (as the APPLMGR user)

2. Log in to the APPL_TOP environment (source the environment file)

3. Create appsutil.zip file

4. perl /bin/admkappsutil.pl

5. This will create appsutil.zip in $APPL_TOP/admin/out .

6. Copy or FTP the appsutil.zip file to the DB Tier RDBMS ORACLE_HOME

7. Go to database ORACLE_HOME and unzip the appsutil directory .

       
unzip -o appsutil.zip
       
 

8. Generate Database Context File and follow the steps below:

source DB env 
cd /appsutil/bin perl adbldxml.pl

-bash-3.2$ perl adbldxml.pl

Starting context file generation for db tier..
Using JVM from /u01/R12EBS/12.1.0.2/appsutil/jre/bin/java to execute java programs..
APPS Password:

The log file for this adbldxml session is located at:
/u01/R12EBS/12.1.0.2/appsutil/log/adbldxml_09300206.log

Could not Connect to the Database with the above parameters, Please answer the Questions below


Enter Hostname of Database server: HYDLAB02

Enter Port of Database server: 1524

Enter SID of Database server: UPG1220

Enter Database Service Name: UPG1220

The context file has been created at:
/u01/R12EBS/12.1.0.2/appsutil/UPG1220_HYDLAB02.xml
-bash-3.2$       
 

9. Run  adconfig.sh on DB tier. After running adconfig.sh, the scripts directory will be created under appsutil directory.

-bash-3.2$ pwd
/u01/R12EBS/12.1.0.2/appsutil/bin
-bash-3.2$
-bash-3.2$
-bash-3.2$ ./adconfig.sh
Enter the full path to the Context file: /u01/R12EBS/12.1.0.2/appsutil/UPG1220_HYDLAB02.xml
Enter the APPS user password:
The log file for this session is located at: /u01/R12EBS/12.1.0.2/appsutil/log/UPG1220_HYDLAB02/09300209/adconfig.log

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.
        Using ORACLE_HOME location : /u01/R12EBS/12.1.0.2
        Classpath                   : :/u01/R12EBS/12.1.0.2/jdbc/lib/ojdbc6.jar:/u01/R12EBS/12.1.0.2/appsutil/java/xmlparserv2.jar:/u01/R12EBS/12.1.0.2/appsutil/java:/u01/R12EBS/12.1.0.2/jlib/netcfg.jar:/u01/R12EBS/12.1.0.2/jlib/ldapjclnt12.jar

        Using Context file          : /u01/R12EBS/12.1.0.2/appsutil/UPG1220_HYDLAB02.xml

Context Value Management will now update the Context file

        Updating Context file...COMPLETED

        Attempting upload of Context file and templates to database...COMPLETED

Updating rdbms version in Context file to db121
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.
-bash-3.2$
-bash-3.2$
-bash-3.2$ cd ../scripts/
-bash-3.2$ ls -lrt
total 4
drwxr-xr-x 2 oracle dba 4096 Sep 30 02:09 UPG1220_HYDLAB02
-bash-3.2$ cd UPG1220_HYDLAB02
-bash-3.2$ ls -rlt
total 72
-rw------- 1 oracle dba  1257 Sep 30 02:09 adstrtdb.sql
-rw------- 1 oracle dba   811 Sep 30 02:09 adstopdb.sql
-rwx------ 1 oracle dba 27246 Sep 30 02:09 adpreclone.pl
-rwx------ 1 oracle dba  3670 Sep 30 02:09 adlsnodes.sh
-rwx------ 1 oracle dba  7347 Sep 30 02:09 adexecsql.pl
-rwx------ 1 oracle dba  6724 Sep 30 02:09 addlnctl.sh
-rwx------ 1 oracle dba  2775 Sep 30 02:09 addbctl.sh
-rwx------ 1 oracle dba  5541 Sep 30 02:09 adchknls.pl
-rwx------ 1 oracle dba  1483 Sep 30 02:09 adautocfg.sh
-bash-3.2$
       
 

That's it, hope this article will help you. :)

Reorganising tablespaces using EXP/IMP

We have many approaches to perform reorganising tablespaces to claim unused space in datafiles. Here in this article, will show you reorganising tablespaces with using EXP/IMP method.

There are number of scenarios that can lead to unused space in datafiles.
The most common I see are - One or more tables/indexes are truncated/deleted huge records from tables, during that time leaving empty areas in the datafiles that previously accommodated them.

Please remember, take backup before you perform reorganising a tablespace because its a big structural change.

Follow below DEMO for reorganising tablespaces using EXP/IMP method:

Step 1: Create new tablespace 'reorg_ts'  and and user 'reorg_user'.  Also create two dummy tables and insert some bunch of records to that tables. After that gather the tables statistics.

Step 2:  Now we can see both table segments are made up of multiple extents, each extent being made up of multiple blocks.

Step 3: Connect to reorg_user and truncate the REORG_T1 table.

Step 4: Even after truncated the REORG_T1 table, there is no difference on REORG_TS tablespace size and also tried to resize the datafile.

Step 5: Here we can see REORG_TS tablespace with lots of free space. For tablespaces with autoextend enabled, the script calculates the maximum sizes the datafiles can grow to, but the "FREE_MB" column is based on the current file size, so use that figure for tablespaces with datafiles set to autoextend. If we switch off autoextend for the relevant datafile, the output will look more clear.

Step 6: Now start with Export/Import process:

• Create directory object for the export and import to work
• Export the REORG_USER schema
• Drop the REORG_USER
• Resize the datafile to 5MB
• Import the REORG_USER schema

Now we can see the size of the datafile has been reduced from 58M to 29M.

Thats it. Hope this article will help you :)

Thanks,

Chowdari          

Find Tablespace Freespace in ORACLE 11g

Below query is useful to displays a list of tablespaces and their used/full status.

COL tspace form a25 Heading "Tablespace"
COL tot_ts_size form 99999999999999 Heading "Size (Mb)"
COL free_ts_size form 99999999999999 Heading "Free (Mb)"
COL ts_pct form 9999 Heading "% Free"
COL ts_pct1 form 9999 Heading "% Used"
BREAK on report
COMPUTE sum of free_ts_size on report
COMPUTE sum of tot_ts_size on report
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, df.BYTES / (1024 * 1024) tot_ts_size,
         SUM (fs.BYTES) / (1024 * 1024) free_ts_size,
         NVL (ROUND (SUM (fs.BYTES) * 100 / df.BYTES), 1) ts_pct,
         ROUND ((df.BYTES - SUM (fs.BYTES)) * 100 / df.BYTES) ts_pct1
    FROM dba_free_space fs,
         (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.BYTES
UNION ALL
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, fs.BYTES / (1024 * 1024) tot_ts_size,
         SUM (df.bytes_free) / (1024 * 1024) free_ts_size,
         NVL (ROUND ((SUM (fs.BYTES) - df.bytes_used) * 100 / fs.BYTES),
              1
             ) ts_pct,
         ROUND ((SUM (fs.BYTES) - df.bytes_free) * 100 / fs.BYTES) ts_pct1
    FROM dba_temp_files fs,
         (SELECT   tablespace_name, bytes_free, bytes_used
              FROM v$temp_space_header
          GROUP BY tablespace_name, bytes_free, bytes_used) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.BYTES, df.bytes_free, df.bytes_used
ORDER BY 4 DESC
/

Thanks,
Chowdari

To get lsinventory with SQL statement in 12.1.0.2

In Oracle Database 12c there's DBMS_QOPATCH package available which is very useful to query plenty of information about patches from within the database.

The DBMS_QOPATCH package provides a PLSQL/SQL interface to view the installed database patches. The interface provides all the patch information available as part of the OPATCH LSINVENTORY -XML command. The package accesses the OUI patch inventory in real time to provide patch and meta-information.

A client-server database connection won't be able to run OPatch easily and hence the DBMS_QOPATCH API is required.

Find below scripts to get lsinventory with SQL.

1)  Query to find which patches have been applied:

      SQL> set serverout on

      SQL> exec dbms_qopatch.get_sqlpatch_status;    
2)  Query to find lsinventory from SQL:

      SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
                select x.*
                from a,
                xmltable('InventoryInstance/patches/*'
                passing a.patch_output
                columns
                patch_id number path 'patchID',
                patch_uid number path 'uniquePatchID',
                description varchar2(80) path 'patchDescription',
                applied_date varchar2(30) path 'appliedDate',
                sql_patch varchar2(8) path 'sqlPatch',
                rollbackable varchar2(8) path 'rollbackable') x;

3)  Query to find ORACLE_HOME and inventory:

      SQL> set pagesize 0
      SQL> set long 1000000
      SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Hope these scripts will help you.

Thanks,
Chowdari

Creating new CDB using DBCA in 12cR1

Oracle Database 12c allows single container database (CDB) to host multiple separate pluggable databases (PDB). This post covers creating a new CDB using DBCA.

Follow below steps to create new container database using DBCA:

Step 1: Set the base parameters and Invoke DBCA

 

Step 2: Database Operation - Select "Create Database". Click Next.

 Step 3: Creation Mode - Select "Advanced Mode". Click Next.

Step 4: Database Template - Select "General Purpose or Transaction Processing". Click Next.

Step 5: Database Identification - Enter Global Database Name: cdb2

SID: cdb2

Select "Create As Container Database"

Select "Create An Empty Container Database"

Click Next.

Step 6: Management Options - Deselect "Configure Enterprise Manager (EM) Database Express". Click Next.

Step 7: Database Credentials - Select "Use same Administrative password for all accounts" and Enter Password and Confirm password. Click Next.

Step 8: Network Configuration - Click Next.

Step 9: Storage Locations - Confirm Storage type is "File System". Select "Use Common Location for All Database Files". Click Next. 

Step 10: Database Options - Click Next. 

Step 11: Initialization Parameters - Select "Character Sets", Select "Use Unicode (AL32UTF8)". Click Next.

Step 12: Creation Options - Select "Create Database". Click Next.

Step 13: Pre Requisite Checks and Summary - Click Finish

Database creation is in progress

Database creation completed. Click close

Thats it. Hope this article will help you :-)

Thanks,

Chowdari

Re-Creating Appsutil directory for DB Tier in EBS R12

Lets assume, while performing DB activities If plan to change the database port, host, SID, or database name parameter on the database server, you must also update AutoConfig on the database tier and also de-register the current database server node. So such cases we need to Re-Create Appsutil directory for DB Tier.

Follow below steps to Re-Create Appsutil and sub directories for DB Tier:

On the Application Tier:

Step 1: Source the environment variables for Oracle Applications

     $ source $APPL_TOP/APPS.env

Step 2: Run AutoConfig on the APPL_TOP

     $ sh $ADMIN_SCRIPT_HOME/adautocfg.sh

Step 3: Execute admkappsutil.pl utility to create the file appsutil.zip

     $ perl $AD_TOP/bin/admkappsutil.pl

   admkappsutil.pl utility will create appsutil.zip in $INST_TOP/admin/out directory

On the Database Tier:

Step 1: Source the environment variables for RDBMS ORACLE_HOME

     $ source $ORACLE_HOME/.env

Step 2: Copy the created appsutil.zip file from $INST_TOP/admin/out to the <RDBMS ORACLE_HOME>

Step 3: Uncompress appsutil.zip under the <RDBMS ORACLE_HOME>  

     $ cd $ORACLE_HOME
     $ unzip -o appsutil.zip

Step 4: Create Contextfile

     $ cd $ORACLE_HOME/appsutil/bin
     $ perl adbldxml.pl appsuser=apps

Step 5: Create Scripts directory

     $ cd $ORACLE_HOME/appsutil/bin
     $ sh adconfig.sh contextfile=<contextfile path>

Step 6:  Run AutoConfig on the <RDBMS ORACLE_HOME>

     $ sh $ORACLE_HOME/appsutil/scripts/<context_name>/adautocfg.sh

Thats it. Hope this article will help you :)

Overview of Oracle Local Registry (OLR) in RAC 11gR2

In Oracle Clusterware 11g Release 2, each node in a cluster has a local registry for node specific resources, called an Oracle Local Registry (OLR), which is installed and configured when Oracle Clusterware installs OCR. Multiple processes on each node have simultaneous read and write access to the OLR particular to the node on which they reside, regardless of whether Oracle Clusterware is running or fully functional. The OLR provides various Oracle Clusterware processes with access to key configuration information even when Oracle Clusterware is not running on the node. One of its functions is to facilitate the Oracle Clusterware startup process in situations where the ASM stores the OCR and voting disks. During the startup process, the OLR is referenced to determine the exact location of the voting disks. This enables the node to join the cluster. After this initial phase, ASM is started. After ASM is started, processes that require the full OCR can start and the clusterware startup process completes.

By default, OLR is located at grid_home/cdata/hostname.olr.

The OLR contains the information about ORA_CRS_HOME, localhost version, active version, GPnP details, OCR latest backup time and location, information about OCR daily, weekly backup location
and node name etc.

You can manage the OLR by using the ocrcheck, ocrdump, and ocrconfig utilities with the -local option.

To see the location of the OLR, use the ocrcheck utility:
$ ocrcheck -config -local

You can check the status of the OLR as follows:
# ocrcheck -local

You can display the content of OLR to the text terminal that initiated the program using the OCRDUMP utility, as follows:
$ ocrdump -local -stdout

You can perform administrative tasks on OLR using the OCRCONFIG utility. To export OLR to a file:
# ocrconfig –local –export file_name

To import a specified file to OLR:
# ocrconfig –local –import file_name

To modify the OLR file on the local node:
# ocrconfig –local –repair olr file_name
Note: The olr keyword used with the -repair option is valid only when -local is used.

Thats it. Hope this will help you.

Thanks,
Chowdari