Thursday, 9 February 2017

ORA-01882

Reports Error ORA-01882: Timezone Region Not Found (Doc ID 390954.1)

SYMPTOMS

In some of the reports, the following error is displayed:

An error has occurred!
Error rendering element. Exception: ORA-01882: timezone region not found
ORA-06512: at "SYSMAN.MGMT_GLOBAL", line 7
ORA-06512: at "SYSMAN.MGMT_VIEW_UTIL", line 189

+ emoms.log files contain below errors :

2006-08-15 18:17:49,484 [DeliveryThread-SNMP3] ERROR jdk.NLSUtil getFormattedDateWithTZ.407 -
Invalid region name format: GMT+08:00
2006-08-15 18:17:49,500 [DeliveryThread-SNMP3] ERROR jdk.NLSUtil getFormattedDateWithTZ.407 -
Invalid region name format: GMT+08:00
2006-08-15 18:17:49,531 [DeliveryThread-SNMP3] ERROR jdk.NLSUtil getFormattedDateWithTZ.407 -
Invalid region name format: GMT+08:00


CAUSE

Time zone issue on the target box.

Created Report specifying the Target time zone which worked.


SOLUTION

Please follow the below Steps :

Select that Report for which errors are being encountered -> Click on Create Like
On the General Tab -> Under Time Period
Click on Set Time Period
Select the Agent (target) Time zone
Click Preview or OK
then access the newly created Report.

ORA-00959: tablespace 'TABSPACE1' does not exist

I specifically exported a large partitioned table. The tablespace it was in is TABSPACE1, which also contains many more large tables

When I import into another instance, which does not have the tablespace TABSPACE1, I get the error message

IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TABSPACE1' does not exist
Import terminated successfully with warnings.

This is fair enough, but is there a way of forcing the import into another tablespace ??

Solution:-

If you specify ignore=y in your import the table will get created in the schema's default tablespace.
 Just make sure that the default tablespace is the one that you want to use.
Otherwise create the table the way you want in the proper tablespace then import with ignore=y.

ORA-01917

Error 'ORA-01917: user or role 'PSQA' does not exist' when importing Controller Oracle database.
ErrorI am trying to import schema into Oracle database-12c, using a command similar to the following:
imp system/password@databasename file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log
fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME
Receives below error message.
1.       IMP-00017: following statement failed with ORACLE error 1917:
 "GRANT SELECT ON "ABC_APL_PROFILE_MST" TO "PSQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PSQA' does not exist.

Cause
The environment from where the original backup (export / .DMP) file was created is using the (optional) Oracle feature which creates additional grants on objects in the Controller schema.

Solution
Delete the schema, then re-create a blank schema. Finally, import the user (into the new schema) but this time use the optional switch:
grants=no.


Modify your import script, to something similar to:
imp system/password@databasename file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME grants=no

Issue resolved.



Monday, 6 February 2017

ORA-04031: unable to allocate 4096 bytes of shared memory

When i am going to import schema facing below issue on 12c Database.

ERROR:-

EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","oracle/i18n/util/LocaleMapper","JOXLE^aff8222c",":SGAClass")
EXP-00000: Export terminated unsuccessfully.

Solution:-

SQL> ALTER SYSTEM SET SGA_MAX_SIZE=512M SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SGA_TARGET=512M SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='512M' scope=spfile;

System altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;

SQL> startup;

Retest issue.


Thursday, 12 January 2017

APP-FND-00222

APP-FND-00222 When Attempting To Run Any Request (Doc ID 412987.1)

APPLIES TO:

Oracle Application Object Library - Version 11.5.10.2 to 11.5.10.2 [Release 11.5]
Information in this document applies to any platform.

SYMPTOMS

On 11.5.10.2 in Production:
When attempting to run any request,
the following error occurs:

APP-FND-00222: Encountered an error while getting the ORACLE user account for your concurrent request

The issue can be reproduced at will with the following steps:
1. Submit any request
2. Face the above error

The issue has the following business impact:
Due to this issue, users cannot submit any request.

CHANGES

Recent applying of ATG RUP3 and associated patches

CAUSE

Although there is no customization done but there is some issue in the CUSTOM.pll
This is verified as follows:

1. Log into Apps and Turn OFF "Custom Code" in the Help->Diagnostics menu.
2. After turning off the custom code, it was possible to launch new requests and the
    requests went through successfully.

SOLUTION

1. Confirm that there is no CUSTOM.plx exists in $AU_TOP/resource.  If any exists then delete it.
2. Take a backup of the current  CUSTOM.pll which exists in $AU_TOP/resource.
3. Replace the current  CUSTOM.pll with a one from a working instance.
4. Regenerate the CUSTOM.pll and retest the issue.

Monday, 9 January 2017

Unable to find an Output Post Processor service to post-process request

ORACLE EBSR12.2.4


SYMPTOMS

You are attempting to run the Subledger Period Close Exception Report (XLAPEXRPT) and the report shows xml data only.
It is not properly formatted.

Associated logfile shows the following:


---------------------- 1) PUBLISH ----------------
Unable to find an output post processor service to post-process request 45999573

Check that the output post processor service is running

This can happen with other reports besides the one listed above.

CAUSE

The OPP processor did not close down properly and needed to be restarted.

SOLUTION

Bounce OPP processor.

Terminate then restart the OPP Output Post Processor.  The following steps were taken from Note 352370.1:

Responsibility: System Administrator
Navigation: Concurrent > Manager > Administer
Query for Name = Output Post Processor
Click button Restart
Click button Refresh, the "Actual" and "Target" values should show "1"


or 

Output Post Processor is Down with Actual Process is 0 And Target Process is 1


Go to System Admin>From OAM Dashboard >>CM status> Output Post Processor> select Abort option and clock on GO.
After Abort again select start.

check Output Post Processor actual value & Target Value.

Actual Process is 1 And Target Process is 1.

Issue resolved.

or 

- FNDSVC should exist under FND_TOP/bin
- Bring down all application services and relink the FNDSVC through adadmin or using the below command:

adrelink.sh force=y ranlib=y "FND FNDSVC"

- Restart all applications services and retest the issue.

or 

Output Post Processor is Down with Actual Process is 0 And Target Process is 1 

1. Shutdown Concurrent Managers

2. To ensure concurrent manager down; check there is no FNDLIBR process running.

ps -ef | grep FNDLIBR

3. Run adadmin to relink FNDSVC executable.

a. Invoke adadmin from command prompt
b. Choose option 2 (2. Maintain Applications Files menu)
c. Choose option 1 (1. Relink Applications programs )
d. Then type FND When prompted; ( Enter list of products to link (all for all products) [all] : FND
 )1
e. Ensure adrelink is exiting with status 0

4. Start Concurrent Managers


5. Check the Output Post Processor




Saturday, 7 January 2017

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


Symptoms:

All concurrent requests that run REPORTS are printing the following warning in the request log.
“REP-0004: Warning: Unable to open user preference file”
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:
CAUSE 1: The file was not found under the specified name in the specified location
CAUSE 2: You lacked the necessary privileges to open the file.

SOLUTION [ID 1120529.1] :

To resolve the warning, copy the prefs.ora file from your Reports Builder $ORACLE_HOME/tools/admin/ directory into the Applications $HOME directory.

Example:

Copy of the file prefs.ora in the directory /u01/prod/apps/tech_st/10.1.2/tools/admin to the directory  /home/applmgr/

Note: Bounce apache service.

After doing above steps my issue resolved.

Tuesday, 3 January 2017

Cold clone Oracle 12c Database steps on Linux

Environment detailed

OS:- Linux7.2 / 64 bits.
Oracle Database:- 12.1.0

                                        Cold clone PROD to UAT environment


What is Database Cloning?
Cloning is a process that can be used to create a copy of the existing Oracle database. Sometimes DBA’s need to clone databases to test backup, development environments and Relocating an database to another machine, and  recovery strategies or export a table that was dropped from the production database and import it back into the production database.
Cold backup Database Cloning is simplest method to clone the database . The disadvantage with this approach is that database will be down during cold backup.
The high level steps for How to clone the database using Cold backup Database Cloning
1) Take the cold backup of the database to be cloned
2) Copy all the backup files to the destination server
3) Create the init.ora ,then startup nomount then create the control file on the new location
4) alter database open resetlogs to bring the new clone online


PROD_SID= Airtel (Source) Host_name:-sujeet
UAT_SID= Idea (Target) Host_name:- Sonu
Steps:-1
Login with Source Instance(PROD).
[oracle@sujeet ~]$ sqlplus / as sysdba
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;
INSTANCE_NAME    STATUS
---------------- ------------
Airtel            OPEN

SQL> create pfile from spfile;
File created.

SQL> select name, value From v$parameter Where name='user_dump_dest';
NAME                                                                                    VALUE
--------------------------------------------------------------------------------
user_dump_dest                             /Airtel/product/12.1.0/dbhome_1/rdbms/log

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_system_cmrsn65x_.dbf
/Airtel/oradata/airtel/Airtel/datafile/tfqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_sysaux_cmrslrt4_.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_undotbs1_cmrsopm4_.dbf
/Airteloradata/airtel/Airtel/datafile/rcqa01.dbf
/Airtel/oradata/airtel/Airtel/datafile/o1_mf_users_cmrsomgs_.dbf
/Airtel/oradata/airtel/Airtel/datafile/icdata01.dbf
/Airtel/oradata/airtel/Airtel/datafile/icindx01.dbf

8 rows selected.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_3_cmrspswy_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_3_cmrspt1y_.log
/Airtel/oradata/airtel/Airtel/onlinelog/o1_mf_2_cmrsprnj_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_2_cmrsprs3_.log
/Airtel/oradata/acsqa/Airtel/onlinelog/o1_mf_1_cmrspqcg_.log
/Airtel/fast_recovery_area/Airtel/onlinelog/o1_mf_1_cmrspqjb_.log

6 rows selected.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /Airtel/oradata/airtel/Airtel                                                                                                            /controlfile/o1_mf_cmrspnsv.ctl

Create the script that will re-create the control file:

SQL> alter database backup controlfile to trace;

Database altered.


Steps:-2
Shut down PROD server.
SQL> shutdown immediate
SQL> !

Steps:-3

Copy source PFILE to Target instance.

Go to Pfile location on PROD:-
[oracle@sujeet dbs]$ cd /Airtel/product/12.1.0/dbhome_1/dbs
[oracle@sujeet dbs]$ cp initacsqa.ora /Idea/product/12.1.0/dbhome_1/dbs/

Rename PFILE on Target instance
[oracle@sonu dbs]$ mv initAirtel.ora initIdea.ora
[oracle@sonu dbs]$ ll
-rw-r--r-- 1 oracle oinstall 1002 Dec 29 01:50 initIdea.ora

[oracle@sonu dbs]$ chmod 777 initIdea.ora

Edit Pfile on Target server:-


Idea.__data_transfer_cache_size=0
Idea.__db_cache_size=8388608
Idea.__java_pool_size=16777216
Idea.__large_pool_size=33554432
Idea.__oracle_base='Idea'#ORACLE_BASE set from environment
Idea.__pga_aggregate_target=603979776
Idea.__sga_target=989855744
Idea.__shared_io_pool_size=0
Idea.__shared_pool_size=230686720
Idea.__streams_pool_size=0
*.audit_file_dest='/Idea/admin/idea/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/Idea/oradata/idea/IDEA/controlfile/o1_mf_cmrspnsv_.ctl','/Idea/fast_recovery_area/IDEA/controlfile/o1_mf_cmrspnxf_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/Idea/oradata/idea'
*.db_domain='sonu.net'
*.db_name='Idea'
*.db_recovery_file_dest='/Idea/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/Idea'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=IdeaXDB)'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'


Steps:-4
copy new trace file from PROD to UAT server.
[oracle@sujeet ~]$ cd  /Airtel/product/12.1.0/dbhome_1/rdbms/log
[oracle@sujeet log]$ cp Airtel_ora_4547.trc /Idea/product/12.1.0/dbhome_1/rdbms/log

Rename trace file with .sql format.
[oracle@sonu ]$ cd /Idea/product/12.1.0/dbhome_1/rdbms/log
[oracle@sonu log]$ mv Airtel_ora_4547.trc  Idea_c1.sql

Edit control file created scripts.

[oracle@sonu log]$ vi Idea_c1.sql
CREATE CONTROLFILE SET DATABASE "IDEA" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_1_cmrspqcg_.log' SIZE 50M,
GROUP 2'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_2_cmrsprnj_.log' SIZE 50M,
GROUP 3'/Idea/oradata/Idea/IDEA/onlinelog/o1_mf_3_cmrspswy_.log' SIZE 50M,
GROUP 4'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_1_cmrspqjb_.log' SIZE 50M,
GROUP 5'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_2_cmrsprs3_.log' SIZE 50M,
GROUP 6'/Idea/fast_recovery_area/IDEA/onlinelog/o1_mf_3_cmrspt1y_.log' SIZE 50M
DATAFILE
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_system_cmrsn65x_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/tfqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_sysaux_cmrslrt4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_undotbs1_cmrsopm4_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/rcqa01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/o1_mf_users_cmrsomgs_.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icdata01.dbf',
'/Idea/oradata/Idea/IDEA/datafile/icindx01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;

:wq <SAVE this file as is it>

Steps:-5
Copy Datafile, Logfile & ORACLE_HOME PROD to UAT Server.
Copy Datafile:-
[oracle@sujeet datafile]$cd /Airtel/oradata/airtel/Airtel/datafile
 [oracle@sujeet datafile]$ cp * /Idea/oradata/idea/IDEA/datafile

 Copy Logfile:-
[oracle@sujeet  onlinelog]$ cd /Airtel/fast_recovery_area/AIRTEL/onlinelog
 [oracle@sujeet onlinelog]$ cp * /Idea/fast_recovery_area/IDEA/onlinelog
[oracle@sujeet ~]$ cd /Airtel/oradata/airtel/AIRTEL/onlinelog
 [oracle@sujeet onlinelog]$ cp * /Idea/oradata/idea/IDEA/onlinelog

Copy ORACLE_HOME:-
[oracle@sujeet ~]$ cd $ORACLE_HOME
[oracle@sujeet dbhome_1 ~]$ cp -r * /Idea/product/12.1.0/dbhome_1

Steps:-6
Set .Bash_Profile on UAT Server.

Steps:-7
[oracle@sonu ~]$ . .bash_profile
[oracle@sonu ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/Idea/product/12.1.0/dbhome_1/dbs/initIdea.ora';
ORACLE instance started.
Total System Global Area  251658240 bytes
Fixed Size                  2923096 bytes
Variable Size             192939432 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes

Steps:-8
Run the re-created control file script on IDEA clone database:
SQL> @Idea_c1.sql
Control file created.
Database altered.

Steps:-9
SQL> select INSTANCE_NAME,STATUS from v$INSTANCE;

INSTANCE_NAME    STATUS
---------------- ------------
Idea          OPEN

SQL> create spfile from pfile;

File created.

Check New clone UAT server files location.

SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> show parameter control_files;

*************************************END******************************************

Monday, 26 December 2016

Oracle Application in DMZ (Demilitarized Zone)

DMZs For Civilians

In the IT industry, a demilitarized zone is a single or multi-segment perimeter network that demarks the portion of the corporate network that lies between the intranet and outside networks.  Corporate DMZ borders are enforced by firewalls and other dedicated networking devices.  






DMZs for the E-Business Suite

AutoConfig supports the use of DMZs with the E-Business Suite Release 11i, and an increasing number of our customers have either already implemented them or are planning to do so.  This is a common configuration:




In the configuration above, there are two different E-Business Suite application servers, each with its own unique domain name and setup.  External users access the E-Business Suite via the external "acme.company.com" address, and internal users access it via the "staff.acme.com" address.

Different Responsibilities for Internal and External Servers

It's possible (and recommended) to restrict the general set of Applications Responsibilities based on the application server that you're using.  

For example, there should be no reason to allow external users to modify your company's Chart of Accounts, so that responsibility can't be used if the end-user is logging in from outside the corporate intranet.

Possible Weak Points

There are two possible weaknesses with the first configuration shown above:
  1. If your external firewall is compromised, your external application server is also compromised, exposing an attack on your E-Business Suite database.
  2. There's nothing to prevent your internal users from attacking your internal application server, also exposing an attack on your E-Business Suite database.
Reverse Proxies and DMZs

If you're concerned about your external firewall being hacked, one possible countermeasure is to use layered DMZs and put a reverse proxy in the first DMZ.  




The reverse proxy has restricted capabilities and and the authority only to speak with the external application server.  It's possible to use the following as reverse proxies with the E-Business Suite:
  • Oracle Web Cache
  • Oracle HTTP Server
  • Other third-party reverse proxy servers, including Apache and Microsoft Proxy Server
An Inside Job

I'm a big fan of heist and con artist movies.  According to Hollywood, you can't pull off a big job without someone on the inside.  

It seems a lot of IT security analysts are fans, too, since they regularly publish surveys that suggest that the majority of security breaches are the result of employees with their hand in the till.  If we're to learn anything from movies, it's this:  trust nobody, not even your internal end-users.

That's why the second configuration above shows the E-Business Suite database server protected by its own firewall.  Even if your internal application server is compromised by an industrious but disgruntled fellow employee, your database is still protected.

Scratching the Surface

There are a number of other interesting DMZ-related architectural options for the E-Business Suite.  If you'd like to get more details, the following document is recommended reading: