Inconsistent Balances Scripts

Inconsistent Balances Scripts [ID 101579.1]
		Modified 06-JAN-2011 Type TROUBLESHOOTING Status PUBLISHED

 Checked for relevance on 14-Apr-2009 Checked for relevance on 07-Jan-2011  PURPOSE -------      To identify inconsistencies in gl_balances or between  gl_balances and  gl_je_lines.   SCOPE & APPLICATION -------------------  This bulletin could be useful when there are inconsistencies in balances for  some accounts, customer can find inconsistency or corruption with information  between two or more modules of the applications. For example, Account Analysis Report and Summary Trial Balance.  With this information it is possible to verify the inconsistency, the periods  and sometimes the list of accounts involved.  Keep in mind that if there is one account with inconsistency is necessary to do  a rollback or to use the Balances Corruption Datafix script(Release 11i). Log a service request if these scripts show that there is an issue.  Balances Inconsistency Diagnostic Scripts ----------------------------------------  Versions Affected:      Oracle Applications Rel 10.7    Oracle Applications Rel 11    Oracle Applications Rel 11i    Platforms Affected:       GENERIC   Description:     ------------ When Process Post Journal Entries is run for each Journal, the transaction  amount is accumulated in a corresponding record of gl_balances.  If setup  includes the Average Balance feature the transaction amount is also stored in  gl_daily_balances.  The information in gl_balances must be consistent by itself and with the  journals posted in gl_je_lines.  Scripts that can help to verify whether there is an inconsistency. ------------------------------------------------------------------ 1. Totals in Gl_balances.  GL_BALANCES must be balanced for each period. This query will show you  the begin balances, period movements and end balances. The balances  must be equal in credits and debits. The script requires the set of books  identification, the period name and the balance type.  set linesize 200 col begin_dr format 999,999,999,999,999.99 col begin_cr format 999,999,999,999,999.99 col dr format 999,999,999,999.99 col cr format 999,999,999,999.99 col end_dr format 999,999,999,999,999.99 col end_cr format 999,999,999,999,999.99  SELECT translated_flag , currency_code , sum(begin_balance_dr) begin_dr , sum(begin_balance_cr) begin_cr , sum(period_net_dr) dr , sum(period_net_cr) cr , sum(begin_balance_dr) + sum(period_net_dr) end_dr , sum(begin_balance_cr) + sum(period_net_cr) end_cr FROM gl_balances WHERE set_of_books_id = &set_of_book_id AND period_name = '&period_name' AND actual_flag = '&Balance_type'            -- A Actual, B Budget, E Encumbrance AND template_id is null group by  translated_flag , currency_code ;   2. Gl_balances vs. Gl_balances     Sometimes, the calculated end balance for some period is not equal to     begin balance in the next period.     If you know the CCID or CCIDs, you can obtain the balances during one     or more fiscal years for some accounts.   col begin_dr format 99,999,999,999  col begin_cr format 99,999,999,999  col period_dr format 99,999,999,999  col period_cr format 99,999,999,999  col end_dr format 99,999,999,999  col end_cr format 99,999,999,999  col periodo_year format 9999  col periodo_num format 99  col periodo_name format a6  col currency format a4  set linesize 180  select code_combination_id ccid  , currency_code currency  , period_name  , actual_flag  , budget_version_id  , encumbrance_type_id  , translated_flag  , period_year  , period_num  , begin_balance_dr begin_dr  , begin_balance_cr begin_cr  , period_net_dr period_dr  , period_net_cr period_cr  , begin_balance_dr+period_net_dr end_dr  , begin_balance_cr+period_net_cr end_cr  from gl_balances  where set_of_books_id=&sob        and code_combination_id in ( &list_of_ccid )        and currency_code = upper ('&currency')        and actual_flag = '&balance_type'            -- A Actual, B Budget, E Encumbrance        and period_year in ( &list_of_years )        and (translated_flag <> 'Y' or translated_flag is null)  order by code_combination_id, currency_code, budget_version_id  , encumbrance_type_id, translated_flag , period_year, period_num /  3. Gl_balances vs gl_balances.      If you know or suspect the periods in which the corruption exists, you can     obtain all the accounts with this problem. This can only check periods within    the same calendar year. It does not check translated balances.  col end_previous format 999,999,999,999.99 col begin_next format 999,999,999,999.99 Select a.code_combination_id ccid, a.template_id, a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR + a.PERIOD_NET_DR - a.PERIOD_NET_CR end_previous,  b.BEGIN_BALANCE_DR - b.BEGIN_BALANCE_CR begin_next from gl_balances a , gl_balances b where a.set_of_books_id = &set_of_books_id and a.set_of_books_id = b.set_of_books_id and a.code_combination_id = b.code_combination_id and a.actual_flag = b.actual_flag and a.actual_flag = '&Batch_balance'            -- A Actual, B Budget, E Encumbrance and nvl(a.ENCUMBRANCE_TYPE_ID,-1)  = nvl(b.ENCUMBRANCE_TYPE_ID,-1) and nvl(a.budget_version_id,-1) = nvl(b.budget_version_id,-1) and a.currency_code = b.currency_code and ((a.translated_flag is null and b.translated_flag is null) or (a.translated_flag = 'R' and b.translated_flag = 'R')) and a.period_name = '&prior_period' and b.period_name = '&problem_period' and (a.BEGIN_BALANCE_DR + a.PERIOD_NET_DR != b.BEGIN_BALANCE_DR  or a.BEGIN_BALANCE_CR + a.PERIOD_NET_CR != b.BEGIN_BALANCE_CR) order by 1;  In 11i the standard report Summary Trial balance also gives a warning if the trial balance is  out of balance   4. Gl_balances vs. gl_je_lines     Sometimes the sum of posted transactions in gl_je_lines is not the same     as the balance stored in gl_balances for a period.  Please use the Diagnostic Note 260031.1 Oracle General Ledger Period Closing Activity Test or use the General 132 Report or the Account Analysis Report   In 11i please run the Account analysis report and General ledger 132 reports which should provide warnings if the journals and lines do not match.  This is confirmation that you have a problem.  RELATED DOCUMENTS ----------------- Bug:1178577 

Related Products Oracle E-Business Suite > Financial Management > Financial Control and Reporting > Oracle General Ledger Keywords GL_BALANCES; OUT OF BALANCE; TRIAL BALANCE

Back to top

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