RMAN - Sample Backup Scripts 10g

RMAN - Sample Backup Scripts 10g [ID 397315.1]
Modified 08-JUN-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
Information in this document applies to any platform.
Goal

Audience: Novice RMAN users.

The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
Solution

RMAN - Sample Backup Scripts 10g


• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================

Making Whole Database Backups with RMAN

You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:

RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.

How to check if the RFA has been setup:

SQL> show parameter recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G

If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.

Note 305648.1 What is a Flash Recovery Area and how to configure it ?

If you wish to place your backup outside the FRA then following RMAN syntax may be used.

RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';

• Backing Up Individual Tablespaces with RMAN

RMAN allows individual tablespaces to be backed up with the database in open or mount stage.

RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;

• Backing Up Individual Datafiles and Datafile Copies with RMAN

The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.

RMAN> backup datafile 2;

RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';

RMAN> backup datafile 1,2,3,6,7,8;

RMAN> backup datafile '/oradata/system01.dbf';
• Backing Up the current controlfile & Spfile

The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.

It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.

If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.

Refer to the manual for more information regarding this feature.

RMAN> backup current controlfile;

RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';

RMAN> backup spfile;

• Backing Up Archivelogs

It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.

RMAN> backup archivelog all;

RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';

RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';
• Backing up the Whole database including archivelogs

Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.

RMAN> backup database plus archivelog delete input;

RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';

Useful Scripts For Finanical (and All Other) Applications Analysts

Useful Scripts For Finanical (and All Other) Applications Analysts [ID 887438.1]
		Modified 22-SEP-2010 Type FAQ Status PUBLISHED

In this Document
Purpose
Questions and Answers
1. How to find versions of files in packages?
2. How to check if a patch is applied?
3. How to find the patch set level for an application?
4. How to find instance name, host name, apps and RDBMS versions of the instance user is logged into?
5. How to find the latest version of a file on a given instance?
6. How to check the installation status and patch set level for a product?
7. How to backup a table before users use sql to update the apps tables?
8. How to find the table(s) name with the column name?
9. How to check for invalid objects in a particular module?
10. How to check for invalid objects in all the modules?
11. How to find the applications in the system that are either installed shared?
12. How to determine database character set?
13. How to check the indexes on a table?
14. How to check for custom triggers on seeded tables?
15. How to get the header file versions for an executable in Unix?
References

---

Applies to:

Oracle Report Manager - Version: 11.5.0 and later [Release: 11.5 and later ]
Oracle Project Costing - Version: 11.5.1 to 12.1.1 [Release: 11.5 to 12.1]
Oracle Applications Desktop Integrator - Version: 11.5.1 to 12.1 [Release: 11.5 to 12.1]
Oracle General Ledger - Version: 11.5 to 12.1 [Release: 11.5.10 to 12.1]
Oracle Payables - Version: 11.5.0 to 12.1.0 [Release: 11.5.10 to 12.0]
Information in this document applies to any platform.

Purpose

These scripts are meant to provide the most commonly requested information. Functional analysts with SQL and Unix acess should be able to run these scripts and provide the information to Oracle Support.

User need to log into SQL plus to run the SQL scripts.

Questions and Answers

1. How to find versions of files in packages?

select text from dba_source
where name like '%&PKG_NAME%'
and line = 2;

Example:

select text
from dba_source
where name = 'GLRX_JOURNAL_PKG'
and line = 2;

2. How to check if a patch is applied?

select * from ad_bugs where bug_number = &bug_number;

select * from ad_applied_patches where patch_name = &bug_number;

SELECT DISTINCT a.bug_number,e.patch_name,
c.end_date,b.applied_flag
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number LIKE '123456'
ORDER BY 1 DESC ;

3. How to find the patch set level for an application?

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like '%&short_name%';

Example:

select substr(aa.application_short_name,1,20) "Product", a.patch_level
"Patch Level" from fnd_product_installations a, fnd_application aa
where a.application_id = aa.application_id
and aa.application_short_name like '%AP%';

4. How to find instance name, host name, apps and RDBMS versions of the instance user is logged into?

select i.instance_name, i.host_name,
f.release_name release, i.version from v$instance i,
fnd_product_groups f where upper(substr(i.instance_name,1,4)) =
upper(substr(f.applications_system_name,1,4));

5. How to find the latest version of a file on a given instance?

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE '&file_name' AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id =
fi.file_id);

Example 1 - finding java class version of a file).

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE '%FrmSheet1VBA%' AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Example 2 - Finding version of .fmb file.

SELECT fi.file_id, filename, version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE '%GLXJEENT.fmb%' AND ve.file_id = fi.file_id AND version =
(SELECT MAX (version) FROM apps.ad_file_versions ven WHERE ven.file_id = fi.file_id);

Note: This works for .class, .fmb .pls, .o, and .sql files only. Doens't work
for .lpc,.lc files etc. If querying for a Form, please use GLXJEENT.fmb
as glxjeent.fmb will not work.

6. How to check the installation status and patch set level for a product?

Example 1

select patch_level, status from fnd_product_installations
where patch_level like '%FND%';

Example 2

select patch_level, status from fnd_product_installations
where patch_level like '%XDO%';

7. How to backup a table before users use sql to update the apps tables?

Example 1:

Create table ap_invoices_all_bkp as select * from ap_invoices_all;

Example 2:

Create table gl_interface_bkp as select * from gl_interface;

Note: SQL updates are not allowed unless directed to do so by Oracle Support or Development

8. How to find the table(s) name with the column name?

User knows the column_name but not sure what table(s) the column name is in.

Example:

select * from dba_tab_columns where column_name like '%SET_OF_BOOKS_ID%';

This will provide the names of all the tables that has column_name SET_OF_BOOKS_ID.

9. How to check for invalid objects in a particular module?

select OWNER, OBJECT_NAME, OBJECT_TYPE
from DBA_OBJECTS
where OBJECT_NAME like 'FND_%'
and STATUS = 'INVALID';

select OWNER, OBJECT_NAME, OBJECT_TYPE
from DBA_OBJECTS
where OBJECT_NAME like 'AP_%'
and STATUS = 'INVALID';

10. How to check for invalid objects in all the modules?

select owner, object_name, object_type from dba_objects
where status = 'INVALID'
order by object_name, object_type;

11. How to find the applications in the system that are either installed shared?

select fat.application_id, FAT.APPLICATION_NAME, fdi.status, fdi.patch_level FROM
FND_APPLICATION_TL FAT,
fnd_product_installations FDI
WHERE FDI.APPLICATION_ID = FAT.APPLICATION_ID
and fdi.status in ('I', 'S')

Note: Status 'I' meaning installed and status 'S' meaning shared.

12. How to determine database character set?

select value from nls_database_parameters
where parameter = 'NLS_CHARACTERSET';

The following scripts will provide NLS parameter and value for database, instance and session.

select * from nls_database_parameters;
select * from nls_instance_parameters;
select * from nls_session_parameters;

13. How to check the indexes on a table?

Example:

select index_owner owner,table_name tab, index_name ind,
column_name colu, column_position position
from DBA_IND_COLUMNS
where table_name = 'GL_CODE_COMBINATIONS';

14. How to check for custom triggers on seeded tables?

Example:
select trigger_name, owner
from dba_triggers
where table_name = 'GL_BALANCES';

15. How to get the header file versions for an executable in Unix?

Example 1

Log into UNIX.
cd $AP_TOP/bin
strings -a APXXTR |grep Header

Example 2

cd $RG_TOP/bin
Strings -a RGRARG |grep Header

The above will provide the versions of all the header files in those executables.

Scripts to Check the Setup of the General Ledger Calendar

Scripts to Check the Setup of the General Ledger Calendar [ID 76503.1]
		Modified 18-MAR-2011 Type TROUBLESHOOTING Status PUBLISHED

Applies to:

Oracle General Ledger - Version: 10.7.16.1 to 12.1 - Release: 10.7 to 12.1
Information in this document applies to any platform.
Checked for relevance on 15-Jul-2010
Executable:GLOOAP - Open Period
Form:GLXSTCAL.FMB - Calendar

Purpose

When investigating problems in this area the solution is often in the relevant White Paper which also provides a useful insight into the Topic. Please refer to the White Paper General Ledger Calendar Setup and Usage at Note:130539.1 .

There is also a GL Calendar Diagnostic now available, per Note 1225833.1.

Last Review Date

July 15, 2010

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Follow these steps to check the Calendar definitions and period statuses for Oracle Financial Applications that use the Calender defined by General Ledger.
This script will return the Calendar definitions, and the statuses of the periods for the different Oracle Financial Applications installed, for a specific Set of Books/Ledger.

1. Log into SQL*Plus as apps user:-
sql> /

2. At the SQL prompt type:

SQL> spool

Assuming you would like to save the output to the directory C:\TEMP and name the output file as
"calendar", type :

SQL> spool C:\TEMP\calendar.txt

3. Run the following queries to check the calendar:
(You can copy and paste the following query to Notepad, save it, then log into SQL*Plus from your
desktop by going to Start/Programs/Oracle for Windows/SQL Plus and log into your database and type
the following:

@\

Scripts To Perform Dynamic Hot/Online Backups

Scripts To Perform Dynamic Hot/Online Backups [ID 152111.1]
		Modified 08-MAR-2011 Type BULLETIN Status PUBLISHED

   PURPOSE -------  For this article basis database knowledge is necessary.  This article will explain how you can create online backups and it contains 2  scripts which can be used to create you hotbackup's.  In this article the terms  hotbackup  and online backup will be used.  The script in the note will create another script which can be used to take the hot backup.   The topics explained are: 1) check archive log mode 2) change archive log mode 3) script hotbackup.bat + explanation 4) script hotbackup.sql + explanation 5) Scheduling   How to create online backups. -----------------------------  --- 1 CHECK ARCHIVE LOG MODE ---   To create online backups you database has to be in archivelog mode.  To check if this is the case execute the next query in sqlplus connecting as system        select log_mode from v$database;  If the the result is NOARCHIVELOG this must be changed, see topic 2. If the  result is ARCHIVELOG go to topic 3.   --- 2 CHANGE ARCHIVE LOG MODE ---  To change you settings from noarchivelog mode to archive log mode read note: 69739.1 This note explains in detail how to change from noarchive log to archivelog and visa versa.   --- 3 SCRIPT HOTBACKUP.BAT ---  The script will start with setting 4 parameters. These parameters must be changed to the values you would like to use.     parameter SCRIPTS    => location of the hotbackup.bat and hotbackup.sql    parameter ORACLE_SID => SID name of your database    parameter TEMP_DIR   => location of a temporary directory (example c:\temp)    parameter DEST_DIR   => location where the copys of the datafiles, controlfile will be created  After this the script will check for old logfiles, datafiles and will create the destination directory if it doesn't exists.  The next part of deleting or renaming old datafiles can be changed. In this script the renaming of old datafiles isn't used. But if you want to save a hotbackup of the day before remove the REM and added the  rem the next part.  Now the hotbackup.sql script is called and the parameters are also passed. For more info about this script see topic 4.  When the hotbackup.sql is executed, the log files (2 in total) will be merged together. When done the program will exit.     Rem **************************************************************************************** Rem Rem  Script to create hot backup of database Rem Rem  09-07-2001 B. de Cock Buning Rem  created script for hotbackup. Rem  11-07-2001 B. de Cock Buning Rem  changed script for dynamic locations (add parameters) Rem Rem  Set SCRIPTS=d:\oracle\admin Set ORACLE_SID=prod Set TEMP_DIR=c:\temp Set DEST_DIR=E:\oracle\backup  Rem Rem Delete old hotbackup.log file Rem if exist %TEMP_DIR%\hotbackup.log del %TEMP_DIR%\hotbackup.log /q if exist %DEST_DIR%\%ORACLE_SID%\Log\backup.log del %DEST_DIR%\%ORACLE_SID%\Log\backup.log  Rem Rem Create backup location   Rem if not exist %DEST_DIR%\%ORACLE_SID% md %DEST_DIR%\%ORACLE_SID% >> %TEMP_DIR%\hotbackup.log   Rem Rem Go to the log directory Rem if not exist %DEST_DIR%\%ORACLE_SID%\Log md %DEST_DIR%\%ORACLE_SID%\Log >> %TEMP_DIR%\hotbackup.log cd %DEST_DIR%\%ORACLE_SID%\Log cd   Rem Rem Delete old log files Rem if exist *.log del *.log /q >> %TEMP_DIR%\hotbackup.log if exist *.lst del *.lst /q >> %TEMP_DIR%\hotbackup.log  Rem Rem Rename the old backup files Rem Create a copy of the current backup files  Rem Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl ren %DEST_DIR%\%ORACLE_SID%\*.ctl *.ctl.old >> %TEMP_DIR%\hotbackup.log Rem if exist %DEST_DIR%\%ORACLE_SID%\*.log ren %DEST_DIR%\%ORACLE_SID%\*.log *.log.old >> %TEMP_DIR%\hotbackup.log Rem if exist %DEST_DIR%\%ORACLE_SID%\*.dbf ren %DEST_DIR%\%ORACLE_SID%\*.dbf *.dbf.old >> %TEMP_DIR%\hotbackup.log  Rem Rem Delete the old backup files Rem Saves no copy of current backup Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl del %DEST_DIR%\%ORACLE_SID%\*.ctl /q >> %TEMP_DIR%\hotbackup.log if exist %DEST_DIR%\%ORACLE_SID%\*.log del %DEST_DIR%\%ORACLE_SID%\*.log /q >> %TEMP_DIR%\hotbackup.log if exist %DEST_DIR%\%ORACLE_SID%\*.dbf del %DEST_DIR%\%ORACLE_SID%\*.dbf /q >> %TEMP_DIR%\hotbackup.log   Rem Rem Start the hot backup script Rem D:\Oracle\Oracle817\Bin\sqlplus "sys/oracle@%ORACLE_SID% as sysdba" @%SCRIPTS%\hotbackup.sql %ORACLE_SID% %TEMP_DIR% %DEST_DIR%  type %TEMP_DIR%\hotbackup.log >> %DEST_DIR%\%ORACLE_SID%\Log\backup.log  exit  Rem ******************************************************************************************************************************   Rem end of script    --- 4 SCRIPT HOTBACKUP.SQL ---  This script will create a spool file wich will be save in your temporary location. The spool file will be used to finally execute the statement for the backup. The spool file is filled with the result of the queries.   NOTE: when you have more then 2 controlfiles or the names are different then mentioned in the script (remark 3) change the names to the names you use or add a controlfile. This because when recovery is needed you will not have any problem with the naming used and the names define in your parameter file of the parameter control_files.   remark HOTBACKUP.sql remark remark Make a hot backup of the database. remark remark Parameters: 1 - SID for the database remark             2 - Temp for temporary directory  remark             3 - Destination for locatie of the backup remark    remark  remark 1- for all tablespaces which are not ACTIVE  remark      make tablespaces active remark 2- for all loggroups remark      logswitch, so all changes/ inserts in redologfiles are archiveerd   remark 3- for every  controlfile remark      make backup of the  controlfile remark 4- backup of the controlfile to trace directory remark 5- for every tablespace remark      set tablespace in backup mode remark      copy datafile to destination directory remark      set tablespace in active mode remark 6- show status of  tablespaces  remark remark  09-07-2001 B. de Cock Buning  remark  Create script for dynamic hotbackup remark   set pagesize 0 set feedback off set linesize 132 set trimspool on set verify off  define dbsid=&1 define temp =&2 define destination=&3  spool &temp\backup_prod.sql   select 'Rem Start tijd:' || to_char( sysdate, 'dd-mm-yyyy hh24:mi' ) from dual; prompt Spool &destination\&dbsid\log\backup.log;  remark 1 select 'alter tablespace '||t.name||' end backup;' from sys.file$ f, v$backup v, sys.ts$ t where v.file# = f.file# and   f.ts#   = t.ts# and   v.status = 'ACTIVE';  remark 2 select 'alter system switch logfile;' from sys.v_$log;  remark 3 prompt alter database backup controlfile to '&destination\&dbsid\control01.ctl';; prompt alter database backup controlfile to '&destination\&dbsid\control02.ctl';;  remark 4 prompt alter database backup controlfile to trace;;  remark 5 select 'alter tablespace '||tablespace_name||' begin backup;' || chr(10) ||        'host copy '||file_name||' &destination\&dbsid\ >> &temp\hotbackup.log' || chr(10) ||        'alter tablespace '||tablespace_name||' end backup;' || chr(10) from dba_data_files;   remark 6 prompt select file#,status, to_char(time,'DD-MM-YYYY HH24:Mi') TIME from v$backup;;  prompt Spool off;; prompt exit;;  spool off  set feedback on set heading on set pagesize 40 set linesize 80       --- 5 SCHEDULE SCRIPT ---  To schedule the scripts for execution at night you needed to use a tool to schedule it. One easy to use tool is winat. This tool is included in the windows recourse kit.  When you install this tool, make sure the service schedule is running and also starts automaticly. To check this after the installation open your control panel with subtopic services and check the service schedule it must be up and running.   After all these steps you have a good hotbackup senario. Of course you have to test your script for errors. At the destination location there will be a subfolder with the name log containing a file called backup.log. This file will display the result of the backup. If there is any kind of error during the execution check this file and solve the problem

Oracle General Ledger

Oracle Diagnostic Scripts For General Ledger and Related Products [ID 1098034.1]
		Modified 01-APR-2011 Type HOWTO Status MODERATED

In this Document
Goal
Solution
References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle General Ledger - Version: 11.5 to 12 - Release: 11.5.10 to 12.0

Information in this document applies to any platform.

Goal

Assist My Oracle Supports users find the notes for running Oracle Diagnostic scripts for General Ledger and related products.

Solution

Script Purpose	For R11i	For R12.0.x to R12.0.5	For R12.0.6 and higher
Collects information on the definition of FSG reports.	Note 277038.1 General Ledger (GL) Financial Statement Generator Setup Test	Note 463515.1 R12 General Ledger Financial Statement Generator Setup Test	Note 732158.1 General Ledger Financial Statement Generator Setup Test
Collects and verifies the general setup of General Ledger.	Note 205198.1 General Ledger (GL) General Setup Test	Note 463514.1 R12 General Ledger General Setup Test	Note 732155.1 General Ledger General Setup Test
Displays detailed information for a given code combination.	Note 230518.1 General Ledger (GL) Code Combination Validation	Note 463520.1 R12 General Ledger Balances Data Collection Test	Note 732154.1 General Ledger Balances Data Collection Test
Identifies misclassified accounts.	Note 279061.1 General Ledger (GL) Misclassified Accounts Activity Test	Note 463517.1 R12 General Ledger Misclassified Accounts Activity Test	Note 732160.1 General Ledger Misclassified Accounts Activity Test
Checks for duplicate rows in the main General Ledger tables.	Note 221428.1 General Ledger (GL) Duplicate Rows Activity Test	Note 463516.1 R12 General Ledger Duplicate Rows Activity Test	Note 732157.1 Oracle General Ledger Duplicate Rows Setup Test
Gathers all relevant data for a journal batch.	Note 223712.1 General Ledger (GL) Journal Data Collection Test	Note 463518.1 R12 General Ledger Journal Data Collection Test	Note 732159.1 General Ledger Journal Data Collection Test
Checks if the period in Set of books/Ledger is ready to close.	Note 260031.1 General Ledger (GL) Period Closing Activity Test	Note 463519.1 R12 General Ledger Period Closing Activity Test	Note 732156.1 General Ledger Period Closing Activity Test
Setup test for Key Flexfield (accounting flexfield).	Note 262117.1 Oracle Application Object Library Flexfield Setup Test	Note 471684.1 R12.0.[3-4] Oracle Application Object Library Flexfield (KEY) Setup Test	Note 471684.1 R12.0.[3-4] Oracle Application Object Library Flexfield (KEY) Setup Test
Setup test for Descriptive Flexfield.	Note 262117.1 Oracle Application Object Library Flexfield Setup Test	Note.471685.1 R12.0.[3-4] Oracle Application Object Library Flexfield (DESCRIPTIVE) Setup Test	Note.471685.1 R12.0.[3-4] Oracle Application Object Library Flexfield (DESCRIPTIVE) Setup Test
RDA/ACT Test - Collects file system and database information.	Note 183274.1 11i Applications DBA RDA Data Collection Test	Note 420427.1All RDA Data Collection Test	Note 732091.1All RDA Data Collection Test
List of all the Diagnostic tests available for all modules.	Note.179661.1 E-Business Suite Diagnostics 11i Test Catalog	Note 741601.1 Oracle E-Business Suite Diagnostics Test Catalog for 12.0.6	Note 783319.1 Oracle E-Business Suite Diagnostics Test Catalog for 12.1.1
Diagnostic Tools FAQ and Troubleshooting Guide Release 11i and R12	Note.235307.1 E-Business Suite Diagnostic Tools FAQ and Troubleshooting Guide for Release 11i and R12	Note.235307.1 E-Business Suite Diagnostic Tools FAQ and Troubleshooting Guide for Release 11i and R12	Note.235307.1 E-Business Suite Diagnostic Tools FAQ and Troubleshooting Guide for Release 11i and R12
Subledger Accounting (XLA): GL Transfer Diagnostic Activity Diagnostic Test	Not Applicable to R11i.	Note 878891.1 Oracle GL Transfer Diagnostics Test	Note 878891.1 Oracle GL Transfer Diagnostics Test

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus [ID 201945.1]
		Modified 17-APR-2011 Type HOWTO Status PUBLISHED

Applies to:

Oracle Application Object Library
Information in this document applies to any platform.

Goal

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus

Solution

One can display Profile Options within eBusiness Suite Release 11i and Release 12
both through the "System Administrator" responsibility (forms based) and also
through the "Functional Administrator" responsibility (OA Framework page), but
there may be times when one wishes to quickly see all Profile Option values
outside of these screens.

One can use the SQL below to query all settings for a particular profile option
or profile options.

As this script is just performing a SELECT command, it is safe to run on any
eBusiness Suite instance and has been tested for 11.5.10 and 12.0.x environments

Instructions
------------
1. Connect to the eBusiness Suite database using APPS schema name

2. Execute the script listed below.

3. The script will prompt to enter a "profile_name". Enter the value for the profile option name to display. This value is case insensitive and is encased by % in the where clause.

For example, if entering the value 'fnd%debug' (do not enter the ' characters), then will see around 15 entries including:

FND: Debug Log Level
FND_DEBUG_RULE_THRESHOLD

4. If Oracle Support has asked to run this script, then one should enable "spool" in SQLPLus to capture the output before running the script.

NOTE - The REM commands are for documentation purposes only and can be ignored.

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name) like upper('%&user_profile_n
ame%'))and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;







Queries To Run To Gather Data For A Journal From Tables GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES - Release 11i

Queries To Run To Gather Data For A Journal From Tables GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES - Release 11i [ID 1111848.1]
		Modified 27-MAY-2010 Type HOWTO Status MODERATED

In this Document
Goal
Solution

---

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle General Ledger - Version: 11.5.9 to 11.5.10.2 - Release: 11.5 to 11.5
Information in this document applies to any platform.

Goal

Use these queries to gather data from GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES tables for a specific journal.

Solution

Upload the output of the following queries in a legible format(.xls file) so that all lines of data line up with their column headers.
Include the queries that you run so that we can see the parameters used.
Each tab of the spreadsheet file can have the results of one of the queries.

a.
Select *
from gl_je_batches
where set_of_books_id = &sob_id
and name = '&batch_name';

b.
Select *
from gl_je_headers
where je_batch_id = &je_batch_id -- returned in script a
and set_of_books_id = &sob_id;

c.
Select *
from gl_je_lines
where je_header_id = &je_header_id -- all returned in script b;

d.
SELECT kfv.concatenated_segments, gjl.*
FROM gl_je_lines gjl, gl_code_combinations_kfv kfv
WHERE gjl.code_combination_id = kfv.code_combination_id
AND gjl.je_header_id IN
(SELECT DISTINCT je_header_id
FROM gl_je_headers
WHERE je_batch_id = &JE_BATCH_ID);

Upload the queries and results, to the SR, in a formatted .xls file.


Hints:
-----
To find the set_of_books_id:
Help->Diagnostic->Examine.
Specify Block Name : HEADER and Field: SET_OF_BOOKS_ID


To find the batch id and header id:
Help->Diagnostic->Examine.
Specify Block Name : HEADER and Field:JE_BATCH_ID
Specify Block Name : HEADER and Field:JE_HEADER_ID

Related Products Oracle E-Business Suite > Financial Management > Financial Control and Reporting > Oracle General Ledger Keywords GL_JE_LINES; GL_JE_BATCHES; GL_JE_HEADERS

Back to top

Queries on GL_BALANCES [ID 14855.1]

Queries on GL_BALANCES [ID 14855.1]
		Modified 29-OCT-2010 Type HOWTO Status PUBLISHED

 Checked for relevance on 29-Oct-2010 Checked for relevance on 27-May-2009 Checked for relevance on 1-Oct-2007  The General Ledger entry reports can sometimes show errors like:      **CONFLICTS W/PER NET BAL**  which normally point to a discrepancy between an account's begin balance and  end balance - normally from the previous period.  To check out such an error  sometimes means querying on the gl_balances table. Here are some suggestions as to possible SIMPLE queries to be run.  If you do discover or suspect an imbalance, please log a service request, explaining the situation.   Note 101579.1 contains scripts that can help in identifying discrepancies. The Period Close Diagnostic can also be used to identify potential problems.   There is no end balance column in gl_balances - the following should highlight  it (this is the period end balance).  Select sum(begin_balance_dr + period_net_dr) - sum(begin_balance_cr +                                                period_net_cr) from gl_balances where period_name = and actual_flag = 'A' and currency_code = and set_of_books_id = and code_combination_id =  PLEASE NOTE:  When the general ledger entry reports work out the end balance  they work it out on a credit or debit basis - not a net of both.  That is, the  period activity total credits on gl_balances should match the journal line  credit total for the period (and CCID) number for number and visa versa with the debits. If it does not then the General Ledger Entry reports will still show  CONFLICT errors even though the account(s) actually have the correct net totals.  This is important when doing a correction on gl_balances or gl_je_lines.  To find period net activity (which forms part of the above)  Select sum(period_net_dr), sum(period_net_cr) from gl_balances where set_of_books_id = and actual_flag = 'A' and currency_code = and period_name =  At foreign currency sites you might want to include the translated_flag.  This will give the sum of every code combination.  However, if you just want to  check against one CCID then take out the sum and include the CCID in the where  clause.  This period net activity should match the amount of journals that have been put  through and for this you need to check mainly against gl_je_lines:  Select sum(j.entered_dr), sum(j.entered_cr) from gl_je_lines j, gl_je_headers h where j.je_header_id = h.je_header_id and j.period_name = and h.currency_code = and j.set_of_books_id = and j.status = 'P' and h.actual_flag =  Again, you might want to search on a particular code combination and this can be included in the where clause - along with other columns, say, from headers to  limit the data returned. The above highlighted one problem for me as the journal lines did not match the balances.  You might want to extend the select to include the sums of the headers as in one case headers for the period did not match the lines in that one journal for  November included some lines for November and some for October (which should be  impossible but happened because of a system problem - there was also a suspected problem in this area with running maintain batch periods -ie changing the period on the journal header and running posting before it completed).  Select h.je_header_id from gl_je_lines j, gl_je_headers h where j.je_header_id = h.je_header_id and j.period_name != and h.period_name = and j.status = 'P' and h.currency_code = and j.set_of_books_id = and h.actual_flag =   No update scripts are included as each case is different. Contact Oracle Support for  a solution if you suspect an imbalance in gl_balances.

Oracle E-Business Suite to Oracle Retail

Oracle E-Business Suite to Oracle Retail

1. A BPEL process extracts data from the Oracle E-Business Suite database.

BPEL processes publish XML files on the specific Java Messaging Service (JMS) topics on the Oracle Retail Integration Bus (RIB).

2. RIB is used to perform the data load into RMS.

The XML payload is verified. If any error is encountered, RIB rejects the message and sends it to the RIB hospital.

3. The XML payloads with validated data should be subscribed to by the specific RMS APIs and loaded to the RMS tables.

Oracle Retail to Oracle E-Business Suite

A BPEL process extracts the data from the Oracle Retail (ReIM, RMS, ReSA) database.

BPEL processes publish XML files in the specific Oracle E-Business Suite staging tables.

User Account Control (UAC) is enabled. Installation may lead to unpredictable results and thus will be stopped.

Error Installing EPM v11.1.2.1: "User Account Control (UAC) is enabled. Installation may lead to unpredictable results and thus will be stopped." [ID 1314042.1]
Modified 25-APR-2011 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution

Applies to:

Hyperion Essbase - Version: 11.1.2.1.000 to 11.1.2.1.000 - Release: 11.1 to 11.1
Microsoft Windows x64 (64-bit) - Version: 2008 R2
Symptoms

Installing EPM v11.1.2.1 fails with the error:

Error: "User Account Control is enabled. Installation may lead to unpredictable results and thus will be stopped."

EPMINS-01001: One or more preinstallation checks failed. Correct the errors before continuing with the installation.

Cause

User Account Control is enabled.

Solution

Per the EPM Installation Start Here Guide, "Prepare a user account", User Account Control must be disabled.

This can be done through User Accounts in Control Panel by clicking on Change User Account Control Settings, then drag the slider to Never notify.

This can also be accomplished using the following steps:

1. Click Start -> Control Panel.
2. In Control Panel, click User Accounts.
3. In the User Accounts window, click User Accounts.
4. In the User Accounts tasks window, click "Turn User Account Control on or off."
5. Uncheck the "Use User Account Control (UAC) to help protect your computer", and then click OK.
6. Click Restart Now to apply the change right away, or click Restart Later and close the User Accounts tasks window.

This change requires a reboot of the Windows 2008 Server.


Related
Products
Middleware > Enterprise Performance Management > Essbase > Hyperion Essbase
Keywords
FAIL; INSTALLATION; UAC; USER ACCOUNT CONTROL

Back to top