Friday, 14 June 2019

DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

Customer RecommendedDatapump Export (expdp) Raises Warnings Like "DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" (Doc ID 1638799.1)


APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS
Expdp on Oracle Standard Edition / Standard Edition 1 produces below warnings

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

CAUSE
The issue was explained in
Bug 17718899 - EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES "OLAP NOT ENABLED"
closed with status 'Not a Bug' with below comment:

OLAP is not available in Standard Edition One (SE1) or Standard Edition (SE) as per the Database Licensing Information document,
it is only available in Enterprise Edition (EE).

This message is intentionally output for DATAPUMP and coded as such in 11.2.0.4 and higher Standard Edition One (SE1) or Standard Edition (SE).

From the Datapump perspective these are warnings and it is safe to ignore them.



NOTE:
The same symptoms can be encountered with the Oracle Enterprise Edition when OLAP is not installed.


SOLUTION
The OLAP related warnings can be safely ignored for DataPump usage.


REFERENCES
BUG:17718899 - EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES "OLAP NOT ENABLED"

ORA-39142: incompatible version number 5.1 in dump file

ORA-39142: incompatible version number 5.1 in dump file


PROBLEM:

Trying to export schema/tables from higher Database version 12.2.0.1.0 and import to version 12.1.0.1.0, causing following error message.

ERROR:

Import: Release 12.1.0.2.0 - Production on Thu Jun 13 17:03:18 2019

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/u01/expimp/schemas_name.dmp"


SOLUTION:

Please set parameter Version=12.1 in export command and import the data without issue.

Source Database(12.2.0.1):

expdp system/****@prod schemas=schemas_name Version=12.1 directory=EXPDP dumpfile=schemas_name.dmp log=schemas_name.log

expdp system/****@PROD directory=EXPDP tables=table_name Version=12.1dumpfile=schemas_name.dmp log=schemas_name.log

Target Database(12.1.0.1):

impdp system/****@prod  directory=EXPDP schemas=schemas_name dumpfile=schemas_name.dmp logfile=schemas_name.log

impdp system/****@PROD directory=EXPDP tables=table_name dumpfile=schemas_name.dmp log=schemas_name.log


Thursday, 28 March 2019

apps-fnd-01564 oracle error 1653 in summit others


CAUSE:

Typically occurs when writing a new record to oracle database, but there is not enough space to write it. The data file in oracle tablespace called APPS_TS_TX_DATA should be set to AUTOEXTEND. If it is not, Oracle will not auto-increase the size of the datafile, and will raise the above error.






SOLUTION:

If all the reports end in COMPLETE – NORMAL, means its work.

If not than follow below steps.

 Run a Concurrent Manager Recovery

1. Please stop Concurrent managers by adcmctl.sh.

2. If not stopped, please abort managers by adcmctl.sh.

adcmctl.sh abort apps/****

3. Please kill if a process remains.

4. relink all (ADADMIN)

5. Please retry Concurrent Manager Recovery.

Navigate:
Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery.


OR



SOLUTION:

WARNING: This procedure should be performed by your ORACLE Database Administrator. 
The SYSTEM user should have required privileges to perform this task. 

Enable Autoextend on the APPS_TS_TX_DATA tablespace by executing the following Oracle PL/SQL command:

SQL > ALTER DATABASE DATAFILE '/u01/ORACLE/ORADATA/APPS_TS_TX_DATA.ORA' AUTOEXTEND ON NEXT 3072K



Sunday, 24 March 2019

ORA-19588: archived log RECID 1003 STAMP 2001986 is no longer valid


Error in RMAN backup log

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d1 channel at 03/04/2018 23:16:26
ORA-19588: archived log RECID 1003 STAMP 2001986 is no longer valid

Recovery Manager complete.



SOLUTION:

This is not an error but a warning. The reason for this is archive logfile which this process is trying to take backup,
is already been backed up by another process and file is deleted

This error is mostly because of 2 reasons.

1. Either 2 backups like one full backup and one archive log backup are running in parallel.
And if the archivelog backup job tried to take backup of the archives, which were backed up and deleted by the full back
job running at the same time, then this warning will come.

So never schedule two backups at the same time

2. Another reason might be because of few invalid or deleted records in control file.

To clean up invalid records, crosscheck the archive log:



RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> DELETE EXPIRED ARCHIVELOG ALL;


It is recommended to use delete input option while backing up archivelog.


RMAN> backup archivelog until time 'sysdate -1' delete input;


If we want to take backup of all archivelogs, then:
——————————————————————

RMAN> backup archivelog all delete input ;

Friday, 8 March 2019

Autoconfig Fails While Running Scripts "afdbprf.sh" and "adcrobj.sh" with 12.1 database

Upgraded database 11.1.0 for 12.1 database having AutoConfig Error

While implement and run AutoConfig form my newly upgraded database 11.1.0, getting the following errors

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
         


  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u01/oracle/sonu/db/tech_st/11.1.0/appsutil/install/sonu_visr12
      afdbprf.sh              INSTE8_PRF         1


  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u01/oracle/sonu/db/tech_st/11.1.0/appsutil/install/sonu_visr12
      adcrobj.sh              INSTE8_APPLY       1




AutoConfig is exiting with status 2

After checking privileges on $ORACLE_HOME/oracore/zoneinfo, timezone.dat and timezlrg.dat are not present ...

Cause
The multiple timezlrg.* and timezone.* are present so a choice of timezone versions are available.

Solution
To resolve the problem, create links to one of the timezone_nn.dat files. Unless you have a requirement for a specific version, choose the latest ...

[dba@sonu bin]$ cd $ORACLE_HOME/oracore/zoneinfo

[dba@sonu zoneinfo]$ ln -s timezone_18.dat timezone.dat
[dba@sonu zoneinfo]$ ln -s timezone_18.dat timezlrg.dat

After the above steps, again initiated the script, and completed successfully.


[dba@sonu bin]$ perl adconfig.pl

Enter the full file path to the Context file: /u01/db/tech_st/12.1.0.2/appsutil/sonu.xml


Enter the APPS user password:

Using Context file          : /u01/db/tech_st/12.1.0.2/appsutil/sonu.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 db112
Updating rdbms type in Context file to 32 bits
Configuring templates from ORACLE_HOME ...

AutoConfig completed successfully.

Wednesday, 6 March 2019

ORA-01804: failure to initialize timezone information

ERROR:-
ORA-01804: failure to initialize timezone information


SP2-0152: ORACLE may not be functioning properly

Cause: -

The timezone information file was not properly read.

Solution:-

1. Set the ORA_TZFILE environment variable:
a. Set environment variable ORA_TZFILE:


$ export ORA_TZFILE=/home/oracle/app/oracle/product/12.1.0/oracore/zoneinfo/timezlrg_18.dat


[ora@sujeet ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 6 10:27:42 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Monday, 18 February 2019

NID-00135: There are number active threads.


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD      MOUNTED

[oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES

DBNEWID: Release 11.1.0.7.0 - Production on Thu Apr 26 18:04:18 2018

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database ACSEBSP (DBID=2199446376)

Connected to server version 11.1.0

NID-00135: There are 1 active threads


Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Solution:-

Action: Ensure that all threads are closed before retrying the operation. Start and open the database to perform crash recovery, then shut down with the NORMAL or IMMEDIATE options to close it cleanly. Finally, try running the utility again.


SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.


[oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES

I hope issue resolved.

Wednesday, 30 January 2019

REP-0004: Warning: Unable to open user preference file.

Enter Password:
REP-0004: Warning: Unable to open user preference file.

=======================================================================
Cause:

Your local (customized) Oracle Reports preference file could not be opened.
This is just a warning, so the product will continue to run even if this occurs.
The possible causes of this error include the following:

Action:

You can take the following actions to rectify this error:

Case 1: Make sure the file prefs.ora is located in your "home" directory.

Case 2: Make sure that you have the privileges necessary to access the file. If you don't, change your privileges accordingly.

Case 3: Consult your operating system documentation or contact your system administrator.

Solution :-


Check whether u have prefs.ora file in your application user's home

[applmgr@sujeet scripts]$ locate prefs.ora
/u01/apps/tech_st/10.1.2/inventory/Templates/tools/admin/prefs.ora
/u01/apps/tech_st/10.1.2/tools/admin/prefs.ora