DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, October 24, 2016

Solved " ORA-16057: server not in Data Guard configuration " , in oracle dataguard environment



We faced the error ORA-16057 while building a standby database.archives were not shipping to the DR site

Now in the production database,

Select dest_id,error from v$archive_dest;

   DEST_ID ERROR
---------- --------------------------------------------------------------------------
         1
         2
         3
         4  ORA-16057: server not in Data Guard configuration
         5
         6
         7
         8
         9
        10
        11



In our case ,this error is due to wrong configuration in "log_archive_config" parameter in the DR side .
Once we the fixed the parameter "log_archive_config"

sql > alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,standby)' scope=both;

Now defer and enable the "log_archive_dest_state_4" paramter in production DB.and the logs started shipping to DR without any issues


sql > alter system set log_archive_dest_state_4=DEFER;

sql > alter system set log_archive_dest_state_4=ENABLE;


Now checking again for the error,

Select dest_id,error from v$archive_dest;

   DEST_ID ERROR
---------- -------------------------------------------------------------------------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11


Hope this helps.. 

Saturday, October 22, 2016

EBS R12.2 Cloning Steps


NOTE: The following Clone approach is applicable for the APPLICATION version from R12.2.1 To R12.2.4
ENVIRONMENT:-
Source:-
BASE_PATH=/s01/oracle/R122
RUN_FILE_SYSTEM=/s01/oracle/R122/fs1
PATCH_FILE_SYSTEM=/s01/oracle/R122/fs2
Non-Editioned File System=/s01/oracle/R122/fs_ne

Target:-
BASE_PATH=/t01/oracle/R122
RUN_FILE_SYSTEM=/t01/oracle/R122/fs1
PATCH_FILE_SYSTEM=/t01/oracle/R122/fs2
Non-Editioned File System=/t01/oracle/R122/fs_ne

STEP1: Prepare Source Node for cloning
Run adpreclone.pl from RUN File System
cd /s01/oracle/R122/fs1/inst/apps/SI_source_appl/admin/scripts
[applsource@source_appl scripts]$ perl adpreclone.pl appsTier

STEP2: Copy the Source to Target
Choose only EBSapps from RUN File System for copy
[applsource@source_appl fs1]$ scp -rf EBSapps appltarget@<target-ip-address>:<target-server-path>
Prepare Target Server
mkdir /t01/oracle/R122/fs1
mkdir /t01/oracle/R122/fs2
mkdir /t01/oracle/R122/fs_ne

STEP3: After finishing Copy Configure Target File System
Go to Clone directory
cd /t01/oracle/R122/fs1/EBSapps/clone/bin
[appltarget@target_appl bin$ perl adcfgclone.pl appsTier

STEP4:Start application and run adpreclone on TARGET RUN File System
Run the environment of Target RUN File System application tier
cd $ADMIN_SCRIPTS_HOME
sh adstrtal.sh apps/<apps-password>
[appltarget@target_appl scripts$ perl adpreclone.pl appsTier

STEP5:Shutdown application on TARGET RUN File System
cd $ADMIN_SCRIPTS_HOME
sh adstpall.sh apps/<apps-password>

STEP6:Copy Target RUN File System to Target Patch File System
cp -rf /t01/oracle/R122/fs1/* /t01/oracle/R122/fs2/
UNSET the application Enviroment.Previously we have executed env.
Before we run adcfgclone.pl dont run any env's, Directly go to /u01/oracle/R122/fs2/EBSapps/comn/clone/bin/ and execute adcfgclone.pl


STEP7:Once Finished copying START CONFIGURING TARGET PATCH FILESYSTEM
[appltarget@target_appl bin$ perl adcfgclone.pl appsTier

STEP8:Once we are done with everything. Start the application Tier using adstral.sh
This time set the env to RUN File System
. /t01/oracle/R122/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME
adstral.sh apps/<apps-password>


Thanks & Regards
Venkatesh RK

Friday, October 21, 2016

AWR Snapshots are not generated Automatically

Scenario:
------------

Oracle Database 11.2.0.4. My AWR Snapshots were not being generated even though it was full enabled.

Basic Checks done:
------------------------

1. Checked that I had enough free space on SYSAUX tablespace. I was using only 5GB of the total 20GB.
2. System Parameter statistics_level was TYPICAL.
3. DBMS_STATS.GET_STATS_HISTORY_RETENTION was to 30 days.
4. The snapshot Interval was 60 minutes and Retention was 8 days.

Query Check the AWR intervals:
=========================
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from
dba_hist_wr_control;

5. Everything was OK,So finally checked the process responsible for AWR collection ie) MMON

Now MMON was not running.

Now it is a critical system and CRP testing is going, So i cannot bring down the system but i need to spawn the MMON process.

Here is the workaround :
-------------------------------

Just enable and disable the restricted sessions.

SQL> alter system enable restricted session;

System altered.

SQL> alter system disable restricted session;

System altered.

Now check the MMON.

[oradoyen@saravanadb trace]$ ps -ef|grep mmon
oradoy  9732     1  3 18:46 ?        00:00:00 ora_mmon_ORADOY
oradoy  9977 50953  0 18:47 pts/8    00:00:00 grep mmon
oradoy 28182     1  0 Oct10 ?        00:11:40 ora_mmon_DEV

Now AWR snapshots started generating.

Note :
Actually this is a bug in 11.2.0.4 and it is fixed by a patch 19565533.

Note id : 2023652.1 AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned

Monday, October 17, 2016

Tempfiles in Standby database

In a active standby database we had a problem due to temp tablespace.

So we increased the temp tablespace by adding a 10G tempfile in the primary database.

SQL> select name from v$tempfile;

NAME
--------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf

SQL> alter tablespace temp add tempfile '/u02/oracle/oradata/TEST/temp02.dbf' size 10g;

Tablespace altered. ( IN PRODUCTION )

SQL> select name from v$tempfile;

NAME
---------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf
/u02/oracle/oradata/TEST/temp02.dbf

But the case is that in the standby database the new tempfile is not created.even the recovery is still active in standby database.
We switched a few archive logs in production database and waited till it applies on the standby database but still the tempfile is not created in the standby environment.


SQL> select name from v$tempfile;

NAME
-----------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf


On searching we found the following DOC id : 834174.1 ,
and we manually have to create the tempfiles in the standby database.

Now in standby database

sql > alter tablespace temp add tempfile '/u02/oracle/oradata/TEST/temp02.dbf' size 10g;

and check

SQL> select name from v$tempfile;

NAME
---------------------------------------------
/u02/oracle/oradata/TEST/temp01.dbf
/u02/oracle/oradata/TEST/temp02.dbf

solved ORA-01187 ORA-01110 in standby database


We got the below error on a active standby databse

ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 73: '/u02/oracle/oradata/test/temp_01.dbf'

so the sloutions is as below

Drop the tempfile from the standby database:

SQL> alter database tempfile '/u02/oracle/oradata/test/temp_01.dbf' drop;

Create a new tempfile

alter tablespace TEMP add tempfile ‘/u02/oracle/oradata/test/temp_01.dbf’ SIZE 5G REUSE;


and when i tried the query again i got the same error but for another file so I dropped and recreated all the tempfiles of the Temporary tablespace temp


after recreating the tempfiles again i checked the



SQL> select file_name ,status from dbA_temp_files;
FILE_NAME                                          STATUS
---------------------------------------------      ---------------------
/u02/oracle/oradata/test/temp_01.dbf               ONLINE
/u02/oracle/oradata/test/temp_02.dbf      ONLINE
/u02/oracle/oradata/test/temp_03.dbf               ONLINE
/u02/oracle/oradata/test/temp_04.dbf               ONLINE
/u02/oracle/oradata/test/temp_05.dbf               ONLINE

Solved ORA-00304: requested INSTANCE_NUMBER is busy




On starting upan instance  in 3 node RAC Database,and got the below error

SQL> startup mount ;


ORA-00304: requested INSTANCE_NUMBER is busy

SQL>EXIT




on checking we found the background sessions are still hanging at the OS level.

Solution is to Kill the hanging sessions at the os level and restart the instance.

ps -ef | grep -i TEST AND

KILL -9 PID1 PID2 PID3


after killing the hanging ssession , wee are able to startup the instance without any issues


Thursday, October 13, 2016


Log files differences in Oracle EBS Release 12.1.3 and Oracle EBS Release 12.2.4


The Log files locations in Oracle EBS Release 12.1.3 are given below:

1.Instance startup and configuration Log files are located for INST_TOP in Oracle Release 12.1.3 are below:

$INST_TOP/logs/appl/admin/log
Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp](Apache log files)
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J, oa*, opmn.log)
$INST_TOP/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

2. Log files related to cloning in R12.1.3 are as below:

 Preclone log files in source instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
Application Tier –
$INST_TOP/apps/$CONTEXT_NAME/admin/log/(StageAppsTier_MMDDHHMM.log)

Clone log files in target instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log
Apps Tier – $INST_TOP/admin/log/ApplyAppsTier_.log

3. Patching related log files in R12.1.3 are as below:

i) Application Tier adpatch log – $APPL_TOP/admin/$SID/log/
ii) Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
iii) Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
iv) Database Tier opatch log – $ORACLE_HOME/.patch_storage


4. Autoconfig related log files in R12.1.3 are as below:

a) Database Tier Autoconfig log :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/adconfig.log
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MMDDHHMM/NetServiceHandler.log


b) Application Tier Autoconfig log :
$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

5.Autoconfig context file location in R12.1.3 :
$INST_TOP/apps/$CONTEXT_NAME/appl/admin/$CONTEXT_NAME.xml


6)R12.1.3 Installation Logs in R12.1.3 are as below:

 Database Tier Installation
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTechStack_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/installdbf.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log
Application Tier Installation
$INST_TOP/logs/.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppsTechStack.log
$INST_TOP/logs/ora/10.1.2/install/make_.log
$INST_TOP/logs/ora/10.1.3/install/make_.log
$INST_TOP/admin/log/ApplyAppsTechStack.log
$INST_TOP/admin/log/ohclone.log
$APPL_TOP/admin/$CONTEXT_NAME/log/installAppl.log
$APPL_TOP/admin/$CONTEXT_NAME/log/ApplyAppltop_.log
$APPL_TOP/admin/$CONTEXT_NAME/log//adconfig.log
$APPL_TOP/admin/$CONTEXT_NAME/log//NetServiceHandler.log
Inventory Registration:
$Global Inventory/logs/cloneActions.log
$Global Inventory/logs/oraInstall.log
$Global Inventory/logs/silentInstall.log

7) Log files related with relink,Network,OUT inventory logs for R12.1.3 are as below:
 1) Database Tier
1.1) Relink Log files :
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME /MMDDHHMM/ make_$MMDDHHMM.log
1.2) Alert Log Files :
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log
1.3) Network Logs :
$ORACLE_HOME/network/admin/$SID.log
1.4) OUI Logs :
OUI Inventory Logs :
$ORACLE_HOME/admin/oui/$CONTEXT_NAME/oraInventory/logs
2) Application Tier
$ORACLE_HOME/j2ee/DevSuite/log
$ORACLE_HOME/opmn/logs
$ORACLE_HOME/network/logs
Tech Stack Patch 10.1.3 (Web/HTTP Server)
$IAS_ORACLE_HOME/j2ee/forms/logs
$IAS_ORACLE_HOME/j2ee/oafm/logs
$IAS_ORACLE_HOME/j2ee/oacore/logs
$IAS_ORACLE_HOME/opmn/logs
$IAS_ORACLE_HOME/network/log
$INST_TOP/logs/ora/10.1.2
$INST_TOP/logs/ora/10.1.3
$INST_TOP/logs/appl/conc/log
$INST_TOP/logs/appl/admin/log


In EBS R12.2.4 the log files locations are as below:

1)Log files file Online patching (adop) in EBS R12.2.4 are in below location:

The adop log files are located on the non-editioned file system (fs_ne), under:

$NE_BASE/EBSapps/log/adop/<adop_session_id>/<phase>_<date>_<time>/<context_name>/log

This log directory will contain patch logs,patch worker logs.

adop(phase=fs_clone) Online pathcing filesystem cloning process related log files are found under:

$INST_TOP/admin/log


2)Log files for Autoconfig process in Oracle EBS R12.2.4 are below:

On Applicaion Tier: $INST_TOP/admin/log/<MMDDhhmm>
On Database Tier: $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>

3)Log files for start/stop of services from $ADMIN_SCRIPTS_HOME

In below directory we will find log files related to start/stop process of oacore, forms, apache, opmn,
weblogic admin server/node manager:

$LOG_HOME/appl/admin/log


4)Log/Out files for Concurrent programs/managers in Oracle R12.2.4 are in below location:

Log/Out files for Oracle Release 12.2 are stored in Non-Editioned filesystem(NE).

Log files: $APPLCSF/$APPLLOG (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/log)
Out files: $APPLCSF/$APPLOUT (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/out)


5)Log files for OPMN and OHS processes in Oracle R12.2.4 are in below location:

Below directory contains log files related OPMN process(opmn.log),
OPMN Debug logs(debug.log), HTTP Transaction logs (access.log),security settings related logs.

$IAS_ORACLE_HOME/instances/<ohs_instance>/diagnostics/logs


6)Log file for Weblogic Node Manager in Oracle R12.2.4 are in below location:

Log file is generated by Node Manager and contains data for all domains that
are controlled by Node Manager on a given physical machine.

$FMW_HOME/wlserver_10.3/common/nodemanager/nmHome1/nodemanager.log


7)Log file for Weblogic  in Oracle R12.2.4 for Oracle Management Service are below

Initial settings for AdminServer and Domain level information is written in this log file

$EBS_DOMAIN_HOME/sysman/log


8)Log files for server processes initiated through Weblogic in Oracle R12.2.4 are in below location:
Stdout and stderr messages generated by the forms, oafm and oacore services are located
at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.
How to Change Weblogic user password in Oracle EBS Applications 12.2.5

We can change the 'weblogic' user password in EBS Release 12.2.5 using txkUpdateEBSDomain.pl perl script.
Before changing the 'weblogic'  user password we need to shutdown all the Middle Tier applications using 'adstpall.sh' script.

Below are the steps involved.

Step 1:Source the run filesystem  in the Oracle EBS Applications 12.2 or higher.

/doyensys/testebs/upgrade
. EBSapps.env run

Step 2:Change the password using txkUpdateEBSDomain.pl  perl script.

applmgr@host1:/doyensys/testebs/upgrade$ perl /doyensys/testebs/upgrade/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkUpdateEBSDomain.pl -action=updateAdminPassword

Program: txkUpdateEBSDomain.pl started at Wed Mar 23 13:51:40 2016

AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: yes

Enter the full path of Applications Context File [DEFAULT - /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:

Executing: /doyensys/testebs/upgrade/fs1/FMW_Home/webtier/perl/bin/perl /doyensys/testebs/upgrade/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl  ebs-get-serverstatus -contextfile=/doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml -servername=AdminServer -promptmsg=hide -logfile=/doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/rgf/TXK/txkUpdateEBSDomain_Wed_Mar_23_13_51_40_2016/EBSProvisioner.log
ERROR : Admin Server is not RUNNING, cannot proceed further.
Exiting..applmgr@host1:/doyensys/testebs/upgrade$ cd $ADMIN_SCRIPTS_HOME
applmgr@host1:/doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/admin/scripts$ ./adadminsrvctl.sh start

You are running adadminsrvctl.sh version 120.10.12020000.10

Enter the WebLogic Admin password:
Enter the APPS Schema password:
Starting WLS Admin Server...


Domain updated successfully
Restarting AdminServer with new Admin Password.

You are running adadminsrvctl.sh version 120.10.12020000.10

Stopping WLS Admin Server...
Refer /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /doyensys/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...


You are running adnodemgrctl.sh version 120.11.12020000.12


NodeManager log is located at /doyensys/testebs/upgrade/fs1/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1

adnodemgrctl.sh: exiting with status 0

adnodemgrctl.sh: check the logfile /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adnodemgrctl.txt for more information ...


You are running adadminsrvctl.sh version 120.10.12020000.10

Starting WLS Admin Server...
Refer /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /doyensys/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /doyensys/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...


*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.
********************************************

Step 3: Start the EBS applications using 'adstral.sh'
$ADMIN_SCRIPTS_HOME
$./adstral.sh

Step 4:Login to admin console for Oracle EBS environment and verify the 'weblogic' user password.

Verify by login to admin console using the 'weblogic' user and make sure all the managed servers are up & running.



Wednesday, October 12, 2016

"ORA_01033:ORACLE initialization or shutdown in progress" .

Issue:
The issue is in racprd database while connecting to service name racprdha1  is  showing ORACLE initialization or shutdown in progress.

Workaround:

The problem is with the scan listener in RAC1. We have checked the scan listener 
“LISTENER_SCAN3” which points to DR instance racdr.

[oracle@RAC1 ~]$ lsnrctl service LISTENER_SCAN3
Service "racprdha1" has 3 instance(s).
Instance "racdr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2140 refused:0 state:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=racdbdr01)(PORT=1521)) 
Instance "racprd1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(address=(protocol=tcp)(host=racdb1)(port=1540)))
Instance "racprd2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(address=(protocol=tcp)(host=racdb2)(port=1540)))

We have shutdown the racdr instance and connect the service name racprdha1 in racprd database.The racha1 service name is able to connect the racprd database.

Solution:
After that,we have checked and changed the remote_listener in DR instance.

The remote listener in racdr instance points the rac1s scan ip.Due to this problem,”ORACLE initialization or shutdown in progress” because of racdr instance is in mount stage.

In DR Instance,

SQL> show parameter remote_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string rac1s:1540 [scan ip of production server]
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0

SQL> alter system set remote_listener='';

System altered.

SQL> show parameter remote

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string 
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
result_cache_remote_expiration integer 0

Friday, October 7, 2016

ORA-20100: Temporary file creation for FND_FILE failed

Scenario:
-----------

While running a report in Payables or any of the program errored out and the log file shows the following error in 12.2.4

ORA-20100: Temporary file creation for FND_FILE failed

Error in Log file :
----------------------

Enter Password:
MSG-00101: Error occured in BATCH_APPROVAL()
MSG-00102: Error Code : -20100
MSG-00103: Error Message : ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with write permissions on the database server node and is listed under utl_file_dir para
ORA-06512: at "APPS.FND_FILE", line 319
ORA-06512: at "APPS.FND_FILE", line 364
ORA-06512: at "APPS.FND_FILE", line 421
ORA-06512: at "APPS.AP_APPROVAL_PKG", line 10672
MSG-00000: When Others:User-Defined Exception
REP-1419: 'beforereport': PL/SQL program aborted.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-1419: MSG-00101: Error occured in BATCH_APPROVAL()
MSG-00102: Error Code : -20100
MSG-00103: Error Message : ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.
Action: Check that the directory is a valid directory with write permissions on the database server node and is listed under utl_file_dir para
ORA-06512: at "APPS.FND_FILE", lin

Solution:
-----------

1. Check the APPLPTMP for ex. /usr/tmp/SARA
2. check whether that exsists in utl_file_directory.
Note : Better to check with gv$ view like below
select name,value FROM gv$parameter2 WHERE name='utl_file_dir' and value like '%SAR%';

If it is not same which is in APPLPTMP. then

a) Change the APPLPTMP location to any one location which is there in UTL file in env file and XML and bounce the concurrent
or
b) Add the same value which is in APPLPTMP to the UTL file location and bounce the db.

Thursday, October 6, 2016

The Data Guard status of xxxx is Error ORA-03135: connection lost contact from Primary database

We have got a error from OEM "The Data Guard status of xxxx is Error ORA-03135: connection lost contact."After that, we have resolve the error using below steps,

Step:1

Check the command in primary database side

SQL> select status,error from v$archive_dest where dest_id=2;

STATUS    ERROR
--------- -----------------------------------------------------------------
ERROR     ORA-12541: TNS:no listener

Step:2

Start the listener in standby side

lsnrctl status
lsnrctl start listener_dbname

Step:3

Mount the standby database and enable MRP Process

Startup mount

alter database recover managed standby database disconnect from session;

Step:4

After login to primary database side,defer and enable the standby destination  log_archive_dest

SQL>Show parameter log_archive_dest_state;

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Step 5:

Check the status of standby side from primary database

SQL> select status,error from v$archive_dest where dest_id=2;

STATUS    ERROR
--------- -----------------------------------------------------------------
VALID

Step:6

Check both primary and standby database sync now.....

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


   Thread Last Sequence Received Last Sequence Applied Difference
--------- ---------------------- --------------------- ----------
        1                  51031                 51031          0
        2                  49482                 49482          0



How to Check or Validate The RMAN Backups


Step 1: The below command just gives the report of backups that are used to do the  restore and recover :


RMAN> run
{
set until time "to_date('2016-24-10:9:00:00','yyyy-dd-mm:hh24:mi:ss')";
restore database preview;
}



Step 2: Check the backup pieces
The below command will read the backup pieces/Copies which has datafiles and if finds any error it will report at the RMAN prompt.

 run
{
allocate channel c1 type disk;
set until time "to_date('2016-02-10:9:00:00','yyyy-dd-mm:hh24:mi:ss')";
restore database validate;

Tuesday, October 4, 2016

Script to analyze all tables for the specified schema





SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql
SELECT 'ANALYZE TABLE "' || table_name || '" COMPUTE STATISTICS;'
FROM   all_tables
WHERE  owner = Upper('&1')
ORDER BY 1;
SPOOL OFF
-- Comment out following line to prevent immediate run
@temp.sql
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON

Displays information on all database sessions with the username column displayed as a hierarchy if locks are present.


SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A30
COLUMN osuser FORMAT A10
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT level,
       LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT 1
               FROM   v$session
               WHERE  blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
SET PAGESIZE 14

Displays threshold information for tablespace




SET VERIFY OFF

DECLARE
  l_warning  VARCHAR2(2) := '&1';
  l_critical VARCHAR2(2) := '&2';
BEGIN
    DBMS_SERVER_ALERT.SET_THRESHOLD(
      metrics_id              => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
      warning_operator        => DBMS_SERVER_ALERT.OPERATOR_GE,
      warning_value           => l_warning,
      critical_operator       => DBMS_SERVER_ALERT.OPERATOR_GE,
      critical_value          => l_critical,
      observation_period      => 1,
      consecutive_occurrences => 1,
      instance_name           => NULL,
      object_type             => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
      object_name             => NULL);
END;
/

SET VERIFY ON

Difference between v$sql v$sqltext v$sqltext_with_newlines

Difference between v$sql v$sqltext v$sqltext_with_newlines

v$sql : if you have multiple copies of the query:

select * from x

if user A and B are executing (in shared pool ) above query then we will have 2 rows in v$sql

v$sqlarea:- aggregate of v$sql. It selects out DISTINCT sql.select * from x
will appear there.

v$sqltext:- for full text

v$sqltext_with_newlines: - without the white space replacement and tab.

Installing various Database Components

col COMP_ID for a20
col VERSION for a15
col STATUS for a10
col COMP_NAME for a40
select COMP_ID, COMP_NAME, VERSION, STATUS from DBA_REGISTRY order by STATUS ;

 @?/rdbms/admin/utlrp

col OWNER for a30
col OBJECT_NAME for a30
select owner, object_name, object_type from dba_objects where status='INVALID' order by object_name;


connect / as sysdba
@?/ord/im/admin/iminst.sql;
connect / as SYSDBA
revoke AQ_ADMINISTRATOR_ROLE from ODM;


connect / as SYSDBA
@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
@?/rdbms/admin/catexf.sql;


connect / as SYSDBA
@?/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP;
@?/rdbms/admin/catxdbj.sql;
@?/rdbms/admin/catrul.sql;


connect / as SYSDBA
@?/ctx/admin/catctx change_on_install SYSAUX TEMP NOLOCK;
connect "CTXSYS"/"change_on_install"
@?/ctx/admin/defaults/dr0defin.sql "AMERICAN";

@?/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP;
@?/rdbms/admin/catxdbj.sql;
@?/rdbms/admin/catrul.sql;

@?/rdbms/admin/catproc.sql;                                                                      
@?/rdbms/admin/catoctk.sql;                                                                    
@?/rdbms/admin/owminst.plb;
@?/rdbms/admin/catldap.sql;

connect / as SYSDBA
@?/olap/admin/olap.sql SYSAUX TEMP;

ORA-00309: log belongs to wrong database

Message:
ORA-00309: log belongs to wrong database
Cause:
The system cannot access the archived redo log because it belongs to another database.
Action:
Specify the correct redo log file, then retry the operation.

sqlplus / as sysdba

show parameter config

dg_broker_config_file1               string                            /u01/oracle/product/11.2.0.4/db_1/dbs/dr1TEST_TESTHOST.dat
dg_broker_config_file2               string                            /u01/oracle/product/11.2.0.4/db_1/dbs/dr2TEST_TESTHOST.dat
log_archive_config                   string                            DG_CONFIG=(PRIMARY_PRODHOST,TEST_TESTHOST)


Check PRIMARY_PRODHOST and TEST_TESTHOST are correct Primary and Standby Hosts

ORA-17502: ksfdcre:4 Failed to create file “FILE NAME” / ORA-15173: entry ‘string’ does not exist in directory ‘string’

RMAN> restore spfile from '+FRA/SRPRIMT/AUTOBACKUP/2016_07_10/s_788290484.315.788290487';
Starting restore at 10-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/SRPRIMT/AUTOBACKUP/2016_07_10/s_788290484.315.788290487
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/10/2016 17:57:17
ORA-19870: error while restoring backup piece +FRA/srprimt/autobackup/2016_07_10/s_788290484.315.788290487
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATA/srprimt/spfilesrprimt.ora
ORA-15173: entry 'srprimt' does not exist in directory '/'
SQL> alter diskgroup DATA add directory '+DATA/srprimt';
Diskgroup altered.
RMAN> restore spfile from '+FRA/SRPRIMT/AUTOBACKUP/2016_07_10/s_788290484.315.788290487';
Starting restore at 10-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/SRPRIMT/AUTOBACKUP/2016_07_10/s_788290484.315.788290487
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 10-JUL-16
RMAN> exit

Converting into RAC instance

Converting into RAC instance

Add separate Undo Tablespaces

First, Create 2 UNDO tablespaces for the 2 instances.PLINKTST1 > create undo tablespace UNDOTBS1 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M; 
Tablespace created.
 

PLINKTST1 > create undo tablespace UNDOTBS2 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
 
Tablespace created.


Add new REDO thread and enable it
 
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)


PLINKTST1 > alter database add logfile thread 2 group 10 '+PWTEST_DATA' size 100M;
Database altered.

PLINKTST1 > alter database add logfile thread 2 group 11 '+PWTEST_DATA' size 100M;
Database altered.


PLINKTST1 > alter database enable public thread 2;

Database altered.


RAC Specific parameters

Add all RAC specific parameters to the pfile (I simply uncommented the undo_tablespaces and the cluster_data parms as I already included those). If the RAC specific were not included earlier in the pfile, add those now. Here are the main ones -- *.cluster_database=true 
*.cluster_database_instances=2

PLINKTST1.local_listener='LISTENER_PLINKTST1'PLINKTST2.local_listener='LISTENER_PLINKTST2'
PLINKTST1.undo_tablespace='UNDOTBS1' 
PLINKTST2.undo_tablespace='UNDOTBS2'

PLINKTST1.instance_number=1
PLINKTST2.instance_number=2

PLINKTST1.thread=1
PLINKTST2.thread=2
Start the additional Listeners
We added a TNS entry for the LOCAL_LISTENER earlier. Lets start a new listener at that port (1522 in this case). 
Update the following files::
 


listener.ora
----------------

LISTENER_PLINKTST_RPTDB01-PWPRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
)
)

SID_LIST_LISTENER_PLINKTST_DB01-PWPRD =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /d01/app/pwprd/product/10.2.0/pwprddb)
(PROGRAM = extproc)
)

Start the above listener. Also, add the TNS entry for the new database.tnsnames.ora 
--------------------
PLINKTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb02-pwprd)(PORT = 1522))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLINKTST)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Shared pfile
Move the pfile to a shared location in ASM; restart instance 1; create all the appropriate directories on node2 and start the instance 2.
If you get the following error, make sure you've created the additional thread and enabled it (see the section "Add new REDO thread and enable it") --

PLINKTST2 > startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
ORA-01620: no public threads are available for mounting
..
..
..
From node 1:
PLINKTST1 > alter database enable public thread 2;

Database altered.

..

The database should come up ok now.
PLINKTST2 > startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
Database mounted.
Database opened.
PLINKTST2 > 


This completes create a RAC database using SQL*Plus. Next we should register all the resources with CRS.


Registering with CRS
$ srvctl add database -d PLINKTST -o /d01/app/pwprd/product/10.2.0/pwprddb
$ srvctl add instance -d PLINKTST -i PLINKTST1 -n rptdb01-pwprd
$ srvctl add instance -d PLINKTST -i PLINKTST2 -n rptdb02-pwprd
$ srvctl add service -d PLINKTST -s PLINKTSTSVC -r "PLINKTST1,PLINKTST2"


Check for all the services just registered.
$ . oraenv
ORACLE_SID = [PLINKTST1] ? CRS

$ crs_stat |grep PLINK
NAME=ora.PLINKTST.PLINKTST1.inst
NAME=ora.PLINKTST.PLINKTST2.inst
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST1.srv
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST2.srv
NAME=ora.PLINKTST.PLINKTSTSVC.cs
NAME=ora.PLINKTST.db

All the new resources have been registered with CRS.