Saturday, 13 April 2013

Enabling audit trail for oracle e-business suite applications in R12

Define audit group
Security>Audittrail>Groups



Note: When you first create the audit group the group status will be 'Enable Requested' this will automatically be changed to 'Enabled' once thAuditTrail Update Tables concurrent request is run

Define columns to be audited
Security > AuditTrail>Tables


Enable auditing for schema owners

Security > Audittrail>install


Enable ‘AuditTrail Activate’ profile option


Submit the AuditTrail Update Tables concurrent request

If Request fails with   “Fatal error in fdasql, quitting Fatal error in fdacv, quitting Fatal error in fdaupc, quitting “ check this out Fatal error in fdasql



Fatal error in fdasql - AuditTrail Update Tables program error on R12


While running AuditTrail Update Tables concurrent request on e-business suite R12(12.1.3) as part of audittrail enable process the request completed with Fatal error in fdasql, quitting.... Fatal error in fdacv, quitting.... Fatal error in fdaupc, quitting error.
  


To fix the issue Check if you have enabled auditing for  APPLSYS,APPS and the application schema owner you want to audit, if not enable the auditing for the schema you wish to audit.




And also check the logfile to see if its failing at View AP_SYSTEM_PARAMETERS_ALL_AC1, If so change the group status for the Audit Group AP_SYSTEM_PARAMETERS_ALL to disable – purge table

 



Now submit the AuditTrail Update Tables program again

Thursday, 11 April 2013

Creating Domain failed while installing oracle forms 11g2 11.1.2.1.0 on windows xp


Configuration Action Creating domain has failed please check logs for details




Few lines from install log file

oracle.as.provisioning.exception.ASProvWorkflowException: Error Executing workflow.
at oracle.as.provisioning.engine.WorkFlowExecutor._createDomain(WorkFlowExecutor.java:686)
........
.......
oracle.as.install.engine.modules.configuration.boot.ConfigurationExtension.kickstart(ConfigurationExtension.java:81)
at oracle.as.install.engine.modules.configuration.ConfigurationModule.run(ConfigurationModule.java:86)
at java.lang.Thread.run(Thread.java:662)


Few from orainstall.out as well


Creating Weblogic Domain.
oracle.as.provisioning.exception.ASProvWorkflowException
at oracle.as.provisioning.weblogic.ASDomain._createDomain(ASDomain.java:2630)
at oracle.as.provisioning.weblogic.ASDomain.createDomain(ASDomain.java:2476)
.......................................
........................................ 
at oracle.as.provisioning.engine.WorkFlowExecutor._createDomain(WorkFlowExecutor.java:686)
at oracle.as.provisioning.engine.WorkFlowExecutor.executeWLSWorkFlow(WorkFlowExecutor.java:391)
at oracle.as.provisioning.engine.Config.executeConfigWorkflow_WLS(Config.java:866)
... 13 more


I had two reasons which were stopping domain creation the Node Manager service and environment variables with space in it.


Check for "Server Node Manager" service, if it is running stop it


I had a service with name "Oracle Enterprise Pack for Eclipse NodeManager (C_Oracle_Middleware_wlserver_10.3)"

see forms installation guide section 2.2.4 Stopping Node Manager Before Installing Oracle Forms and Reports (Windows Only)

http://docs.oracle.com/cd/E38115_01/doc.111210/e23960/install.htm#CHDBFJBE


Check the user environment variables

Check the user environment  variables(temp/tmp) if they are pointing to directories with spaces, change those to point to directory path without space

my computer > properties > Advanced > Environment Variables > User Variables for

change to
temp=c:\temp
tmp=c:\tmp

make sure given folders exist

if you are hitting same error again check path variable has any folders with space, if so remove it temporarily and continue installation


Oracle forms 11g2 (11.1.2.1.0) on windows XP can be installed in the following way


Install weblogic server

Download Weblogic server from
http://www.oracle.com/technetwork/middleware/weblogic/downloads/wls-main-097127.html

WLS 10.3.5 , WSL 10.3.6 are supported with  forms 11g2 (as of this writing)

See certification matrix here
http://www.oracle.com/technetwork/developer-tools/forms/oracle-forms-11gr2certmatrix-519680.xls

Then install  oracle forms 11g2 (11.1.2.1.0)

http://docs.oracle.com/cd/E38115_01/doc.111210/e23960/install.htm




Saturday, 6 April 2013

Restoring autoconfig configurations (How does autoconfig restore.sh script work)



Each time autoconfig is run it creates a folder under [inst_top]/admin/out/. The name of folder is the the date and time of autoconfig run in the mmddhhmi format, This contains resote.sh script with many other files including  context_file.

On running autoconfig it automatically creates a folder with different files for that autoconfig run. After autoconfig run If you find something wrong( like application is not accessible because of last modification) stop the application tier services and go to the relevant autoconfig folder and run restore.sh script to restore previous autoconfig configuration.

The restore.sh will not actually restore the context_file but this will restore all the settings prior to this autoconfig run.(if you had a faulty configuration before, of course restore.sh will restore only those configurations)

After restore.sh is run start up the services(don't run autoconfig), access the application, check the modifications and correct the modifications causing trouble in application. To take effect of new modifications(this must be done using autoconfig editor from OAM) shutdown application services and run autoconfig.

The restore.sh will take you back to the previous configuration (ie the configuration you had when you run autoconfig).



Lets do a test

Change Active Web Port from its value current value 8001to 'bbb'

Save the change when you are asked to change the  dependent parameter say no and click OK on next page. On saving, it will tell you to run autoconfig for new changes to take effect.
Stop the application services and run autoconfig.
Running autoconfig  completes with errors,  lets start the application services and try to access the page.



The page is not appearing because of the wrong port value.

Lets restore the previous autoconfig configuration. Go to the [inst_dopt]/admin/out/ [mmddhhss] folder and run restore.sh. Don't run autoconfig after running restore.sh



Now access the application and go to autoconfig page, click on show history button, click on show differences from current configuration on history page

You can see the current and previous values here, use the restore previous value radio button to restore the correct value


Press ok button
Run autoconfig


Now access the application and go to autoconfig page, click on show history button, click on show differences from current configuration on history page and see.


Friday, 5 April 2013

Recovering database past controlfile SCN

Scenario

Restore the database and recover up to the last available archived log sequence.Auto Controlfile backup is generated before sometime disaster happened (lost all controlfiles, datafiles and redologfiles).After the autobackup of controlfile generation there had been few archive logs generated but not backed up. 

Steps
Restore the controlfile
Restore the datafiles
Catalogue the archived logs manually of which controlfile is not aware of
recover the database
open the database with resetlogs
Connect to rman and set dbid

RMAN> restore controlfile from '/u02/bkp/TESTCLONE/bkpcontrl_c-1057140170-20130324-01'

Starting restore at 25-MAR-2013 14:32:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u04/oracle/TESTclonedata/cntrl01.dbf
output file name=/u04/oracle/TESTclonedata/cntrl02.dbf
output file name=/u04/oracle/TESTclonedata/cntrl03.dbf
Finished restore at 25-MAR-2013 14:32:11

Let's do a listing of archived log files

RMAN> alter database mount
database mounted
released channel: ORA_DISK_1

RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
101     1    1       A 24-MAR-2013 12:05:46
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_1_810907546.dbf
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf


This lists only two archive logs (these were the only available archive logs  at the time of controlfile autobackup happened), whereas we have three more archive logs generated after this control file backup was generated.

Let’s see in sqlplus

SQL> select sequence#,first_change#,next_change# from v$log order by 1;

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
         0             0            0
         0             0            0
         0             0            0
         0             0            0
         0             0            0
         1     425593057    425626797
         2     425626797    425628778
         3     425628778   2.8147E+14

The controlfile says sequence# 3 is the current one

SQL> select archived,first_change#,next_change#,sequence# from v$archived_log order by first_time


ARC FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--- ------------- ------------ ----------
YES     425593057    425626797          1
YES     425626797    425628778          2

Now let’s try to recover to a specific scn

RMAN>  recover database until scn 425628778 preview ;

Starting recover at 25-MAR-2013 10:56:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=375 device type=DISK
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:56:27

Lets try to recover to a later scn

RMAN>  recover database until scn 425646710 preview ;

Starting recover at 25-MAR-2013 10:57:10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:57:10

Recovering even to a later point is using same archive log file(the controlfile does not know about the other archive log files)


But  We have three more archive logs at the archive location, lets catalog the archive log information to the restored controlfile
RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf' ;

cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf RECID=103 STAMP=811006615

RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/
TESTclone_arc_1_4_810907546.dbf ' ;
cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_4_810907546.dbf RECID=104 STAMP=811008283

RMAN> cataloged archived log
rchived log file name='/u04/arch/TESTCLONE/
TESTclone_arc_1_5_810907546.dbf ' ;
cataloged archived log
archived log file name=/u04/arch/TESTCLONE/TESTclone_arc_1_5_810907546.dbf RECID=105 STAMP=811008400

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------

102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

103     1    3       A 24-MAR-2013 12:22:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf

104     1    4       A 24-MAR-2013 14:55:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_4_810907546.dbf

105     1    5       A 24-MAR-2013 15:10:34
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_5_810907546.dbf

Lets see in sqlplus
SQL> select archived,first_change#,next_change#,sequence# from v$archived_log order by first_time ;

ARC FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--- ------------- ------------ ----------
YES     425593057    425626797          1
YES     425626797    425628778          2
YES     425628778    425646710          3
YES     425646710    425648285          4
YES     425648285    425650656          5

Now controlfile knows about the other archive log files .  v$archived_log shows the newly added archive log files details.

 Lets see what happens when we run same recover commands (with different scn) we ran before


RMAN> recover database until scn 425628778 preview ;

Starting recover at 25-MAR-2013 10:56:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=358 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=375 device type=DISK
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 10:56:27


RMAN> recover database until scn 425646710 preview ;

Starting recover at 25-MAR-2013 11:14:48
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name TESTCLONE
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
102     1    2       A 24-MAR-2013 12:13:20
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_2_810907546.dbf

103     1    3       A 24-MAR-2013 12:22:50
        Name: /u04/arch/TESTCLONE/TESTclone_arc_1_3_810907546.dbf

Media recovery start SCN is 425626797
Recovery must be done beyond SCN 425628742 to clear datafile fuzziness
Finished recover at 25-MAR-2013 11:14:48

Unlike previous run this time the newly added archived logs are selected for applying.



RMAN> recover database until scn 425650656
Starting recover at 25-MAR-2013 16:37:45
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_2_810907546.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_3_810907546.dbf
archived log for thread 1 with sequence 4 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_4_810907546.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u04/arch/ERPCLONE/erpclone_arc_1_5_810907546.dbf
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_2_810907546.dbf thread=1 sequence=2
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_3_810907546.dbf thread=1 sequence=3
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_4_810907546.dbf thread=1 sequence=4
archived log file name=/u04/arch/ERPCLONE/erpclone_arc_1_5_810907546.dbf thread=1 sequence=5
media recovery complete, elapsed time: 00:00:14
Finished recover at 25-MAR-2013 16:38:01

RMAN>  Alter database open with resetlogs;


Tested on 11g(11.2.0.3)