Recovery catalog for RMAN backup

Why Recovery catalog ? It is always recommended to have the recovery catalog. If the target database controlfiles are lost recovery can become difficult if not impossible.Having recovery catalog makes the DBA life easier at the time of critical scenario. Even for larger system the use of a recovery catalog can increase the backup performance. Recovery Catalog Schema can be created in the Target database or in any test or development database. It is  not at all recommended  to create the recovery catalog in the target database itself. Make sure to have a separate database for the recovery catalog always .Also its recommended to create the recovery catalog database in a different machine. If creating the Recovery catalog database in different machine is not possible then ensure that the recovery catalog and target databases do not reside on the same disk. If both your recovery catalog and your target database suffer hard disk failure, your recovery process is much more difficult. If possible, take other measures as well to eliminate common points of failure between your recovery catalog database and the databases you are backing up. The recovery catalog contains information about RMAN operations, including: + Datafile and archived redo log backup sets and backup pieces + Datafile copies + Archived redo logs and their copies + Tablespaces and datafiles on the target database + Stored scripts, which are named user-created sequences of RMAN commands + Persistent RMAN configuration settings How to create recovery catalog ? Creating recovery catalog is a 3 step process .The recovery catalog is stored in the default tablespace of the recovery catalog schema. SYS cannot be the owner of the recovery catalog. 1. Creating the Recovery Catalog Owner 2. Creating the Recovery Catalog 3. Registering the target database 1. Creating the Recovery Catalog Owner 1.1 Size of recovery catalog schema :  Size of recovery catalog schema depends on a) The number of databases monitored by the catalog. b) The rate at which archived redo log generates in the target database c) The number of backups for each target database d) RMAN stored scripts stored in the catalog 1.2 Creating the Recovery Catalog Owner Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example: % sqlplus '/ as sysdba' SQL> CREATE USER rman IDENTIFIED BY rman      DEFAULT TABLESPACE tools       TEMPORARY TABLESPACE temp      QUOTA UNLIMITED ON tools; SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman; 2. Creating the Recovery Catalog log in to rman and create the catalog schema.Look at this example: In the below example " catdb " is the catalog database connection string. Before creating the recovery catalog make sure to have the tnsnames.ora entry for the catalog database in the target server and the listener must be up and running in the catalog database server.You must be able to connect to the catalog database from sqlplus from the target server. % rman catalog rman/rman @ catadb RMAN> CREATE CATALOG; 3. Registering the target database  After making sure the recovery catalog database is open, connect RMAN to the target database and recovery catalog database and register the database . Make sure that your target database is either open or in Mount stage.Look at this example:  % rman TARGET / CATALOG rman/rman @ catdb RMAN> REGISTER DATABASE; RMAN creates rows in the catalog tables to contain information about the catalog database .Copy all the pertinent data from the controlfile into the catalog, synchronizing the catalog with the control file. You can register multiple target databases in a single recovery catalog, if they do not have duplicate DBIDs. RMAN uses the DBID to distinguish one database from another. How to Upgrade recovery catalog Schema ? When  you upgrade target database to the latest version you need to upgrade the RMAN catalog schema.Connect to RMAN from the target database so that you can use, the target database's RMAN executable. Look at the example : % rman target / catalog rman/rman @ catdb RMAN> UPGRADE CATALOG; RMAN-06435: recovery catalog owner is rman RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> UPGRADE CATALOG; Issuing 'upgrade catalog' will only upgrade the catalog schema to be compatible with the higher release of RMAN; it will not upgrade the catalog database in any way. You have to connect to recovery catalog database catdb and run "upgrade catalog" twice. How to upgrade recovery catalog database ? Upgrading the recovery catalog database is same as the any other database upgrade steps.Upgrading the catalog database do not upgrade the catalog database schema. How to remove catalog ? The "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation. Look at the example : RMAN> DROP CATALOG; How to unregister the target database from the recovery catalog ? From 10G onwards, the process is simplified by introducing a new RMAN command to unregister the target database from the recovery catalog.Look at the example : RMAN> UNREGISTER DATABASE <database_name> ; The command "unregister database " should be executed only at the RMAN prompt. This is a restriction to use this command.Also RMAN must be connected to the recovery catalog in which the target database is registered. ( Ref.  Note 252800.1 ) Prior to release 10G, in order to unregister the target database you need to execute the following statement in the recovery catalog database connected as recovery catalog schema owner.Look at the example : % sqlplus rman/rman @catdb SQL > DBMS_RCVCAT.UNREGISTERDATABASE(db_key, db_id); To unregister a database from the recovery catalog prior to Oracle 10g  (Ref. Note 1058332.6).   How to backup of the Recovery Catalog ? Recovery catalog database is just like any other database.This database backup need to be taken every time after the target database backup. You can take Physical backup or Logical backup of the catalog database.You can use RMAN for the backup of the recovery catalog database . Few guideline for recovery catalog database + Run the recovery catalog database in ARCHIVELOG mode so that you can do point-in-time   recovery if needed. + Set the retention policy to a REDUNDANCY value greater than 1. + Do not use another recovery catalog as the repository for the backups. + Configure the control file autobackup feature to ON. How to restore and Recover recovery catalog from Backup ? Restoring and recovering the recovery catalog is much like restoring and recovering any other database Compatibility of the Recovery Catalog When you use RMAN with a recovery catalog in an environment where you have run past versions of the database, you can wind up with versions of the RMAN client, recovery catalog database, recovery catalog schema, and target database that all originated in different releases of the database. Here is a note which gives detailed information about the compatibility matrix Ref. Note 73431.1 RMAN Compatibility Matrix   How to identify recovery catalog schema version ?   The schema version of the recovery catalog is stored in the recovery catalog itself. The information is important in case you maintain multiple databases of different versions in your production system, and need to determine whether the catalog schema version is usable with a specific target database version. To determine schema version of recovery catalog connect to catalog database from the recover catalog user and then query RCVER table. Look at the example : % sqlplus rman/rman @catdb SQL > SELECT * FROM rcver;   VERSION   ------------   11.01.00   If the table displays multiple rows, then the highest version in the RCVER table is the current catalog schema version. The table stores only the major version numbers and not the patch numbers. For example, assume that the rcver table displays the following rows: VERSION ------------ 08.01.07  09.02.00 10.02.00

scripts to monitor jdbc connections in Apps 11i and R12

 

Just cut and past the sql block below and save in a file named monitor_jdbc_conn.sql

To run the script, just execute:

sqlplus apps/apps
@monitor_jdbc_conn.sql

 

 

Rem Rem monitor_jdbc_conn.sql Rem Rem    NAME Rem      monitor_jdbc_conn.sql Rem Rem    DESCRIPTION Rem      This shows JDBC connection utilization on database by machine, process Rem      and module. Rem Rem    NOTES Rem      Runs as apps or apps read only user Rem   -- -- Set header information for all columns used --    set lines 120 set pages 500 column module  heading "Module Name"  format a48; column machine heading "Machine Name" format a25; column process heading "Process ID"   format a10; column inst_id heading "Instance ID"   format 99; column inst_id heading "Program"   format a15; column username for a10 column sid for 9999 column sql_text for a50      prompt ======================================================= prompt JDBC Connections   select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual / prompt =======================================================   prompt prompt JDBC Connection Usage Per JVM Process prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select count(*), process from v$session where program like '%JDBC%' group by process order by 1 asc /   prompt prompt Connection Usage Per Module prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select count(*), module from v$session where program like '%JDBC%' group by module order by 1 asc /   prompt prompt Connection Usage Per process and module  prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select count(*), process, module from v$session where program like '%JDBC%' group by  process, module  order by 1 asc /   prompt prompt Idle connections for more than 3 hours  prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select count(*),machine, program  from v$session where program like '%JDBC%' and  last_call_et > 3600 *3 group by machine, program /   prompt prompt Active connections which are taking more than 10 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select * from v$session where program like '%JDBC%' and last_call_et > 600 and status = 'ACTIVE' order by last_call_et asc /   prompt prompt Statements from JDBC connections taking more than 10 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select s.process, s.sid,  t.sql_text from v$session s, v$sql t where s.sql_address =t.address  and s.sql_hash_value =t.hash_value and s.program like '%JDBC%' and s.last_call_et > 600 and s.status = 'ACTIVE' /   prompt prompt Active connections which are taking more than 20 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select * from v$session where program like '%JDBC%' and last_call_et > 1200 and status = 'ACTIVE' order by last_call_et asc /   prompt prompt Statements from JDBC connections taking more than 20 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select s.process, s.sid,  t.sql_text from v$session s, v$sql t where s.sql_address =t.address  and s.sql_hash_value =t.hash_value and s.program like '%JDBC%' and s.last_call_et > 1200 and s.status = 'ACTIVE' /   prompt prompt Active connections which are taking more than 30 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select * from v$session where program like '%JDBC%' and last_call_et > 1800 and status = 'ACTIVE' order by last_call_et asc /   prompt prompt Statements from JDBC connections taking more than 30 min to run prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select s.process, s.sid,  t.sql_text from v$session s, v$sql t where s.sql_address =t.address  and s.sql_hash_value =t.hash_value and s.program like '%JDBC%' and s.last_call_et > 1800 and s.status = 'ACTIVE' /   prompt prompt Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   select s.sql_hash_value, t.sql_text, s.last_call_et from v$session s , v$sqltext t where s.username = 'APPLSYSPUB' and s.sql_hash_value= t.hash_value  and t.sql_text like  '%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%';   prompt =======================================================

Oracle E-Business Suite System Survey

 

This post contains a list of system survey questionnaires that Oracle Applications development
should know from the viewpoint of performance. This output will help in understanding h/w configuration
and its related environment for Oracle E-Business Suite.

Oracle Apps dba or someone having similar role will be best person to provide the output.
Sql*plus login to production database with apps user is required.

 

The first 6 questions should be provided manually and rest of them could be gathered by script. 1. Hardware Information * DB sever - System Model : - OS version : - # CPUs : - CPU speed : - MEMORY : - Disk I/O : - Etc : * CM (Concurrent Manager) sever - System Model : - OS version : - # CPUs : - CPU speed : - MEMORY : - Disk I/O : - Etc : * AP (Application) sever - System Model : - OS version : - # CPUs : - CPU speed : - MEMORY : - Disk I/O : - Etc : 2. environment information (# tiers, network setup, RAC etc..) 3. # of total and avg. concurrent users during peak times. 4. DB size (including version) 5. modules being used. 6. # of employees ==== The following sql script will gather additional information from the production database. After sql*plus login with apps user, pl run system.sql and get the spool output file, system.txt under the current directory. --File name : system.sql spool system.txt set linesize 180 set pagesize 600 prompt Apps Version SELECT release_name from fnd_product_groups; prompt DB SERVER INFORMATION col host_name format a20 COL VERSION FORMAT A15 COL STATUS FORMAT A10 COL THREAD# FORMAT 99 select INST_ID, INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSION , STATUS , PARALLEL , THREAD# from gv$instance ; PROMPT MAJOR TABLE LIST select owner, table_name, num_rows, LAST_ANALYZED From dba_tables where table_name in ( 'AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AR_PAYMENT_SCHEDULES_ALL', 'RA_CUSTOMER_TRX_ALL', 'RA_CUSTOMER_TRX_LINES_ALL' , 'HZ_PARTIES', 'HZ_CUST_ACCOUNTS', 'AS_SALES_LEADS', 'AS_ACCESSES_ALL_ALL', 'BOM_STRUCTURES_B', 'BOM_COMPONENTS_B', 'CS_INCIDENTS_ALL_B', 'FA_ADJUSTMENTS', 'FA_DEPRN_DETAIL', 'FA_DEPRN_SUMMARY', 'FND_USERS', 'GL_JE_HEADERS', 'GL_JE_LINES', 'MRP_GROSS_REQUIREMENTS', 'MRP_RECOMMENDATIONS', 'MRP_FULL_PEGGING', 'MRP_BOM_COMPONENTS', 'MTL_MATERIAL_TRANSACTIONS', 'MTL_TRANSACTION_ACCOUNTS', 'MTL_SYSTEM_ITEMS_B', 'HR_ORGANIZATION_INFORMATION', 'HR_OPERATING_UNITS', 'MTL_PARAMETERS', 'OE_ORDER_HEADERS_ALL', 'OE_ORDER_LINES_ALL', 'PO_HEADERS_ALL', 'PO_LINES_ALL', 'PO_VENDORS', 'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ATRIBUTE_VALUES', 'WF_NOTIFICATIONS', 'WF_NOTIFICATION_ATTRIBUTES' , 'WSH_DELIVERY_DETAILS' , 'WSH_DELIVERY_ASSIGNMENTS', 'WSH_NEW_DELIVERIES', 'WSH_DELIVERY_LEGS', 'WSH_TRIP_STOPS', 'WSH_TRIPS' ) order by table_name ; PROMPT number of daily concurrent requests. SELECT trunc(REQUESTED_START_DATE), count(*) FROM FND_CONCURRENT_REQUESTS WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate group by rollup(trunc(REQUESTED_START_DATE)) ; PROMPT Applications versions and family pack versions. COL APPLICATION_NAME FORMAT A60 COL SHT_NAME FORMAT A10 col PATCH_LEVEL FORMAT A20 SELECT A.APPLICATION_SHORT_NAME SHT_NAME,T.APPLICATION_NAME, I.STATUS, NVL(I.PATCH_LEVEL, 'n/a') PATCH_LEVEL, I.DB_STATUS FROM FND_PRODUCT_INSTALLATIONS I, FND_APPLICATION A, FND_APPLICATION_TL T WHERE A.APPLICATION_ID = I.APPLICATION_ID AND A.APPLICATION_ID = T.APPLICATION_ID AND T.LANGUAGE = USERENV('LANG') ORDER BY 1 ; PROMPT Multi-org being used. select MULTI_ORG_FLAG org, MULTI_LINGUAL_FLAG lingual, MULTI_CURRENCY_FLAG currency from FND_PRODUCT_GROUPS ; PROMPT DB size with Tablespace set head on set pagesize 30 select NVL(tablespace_name,'** Total **') "Tablespace Name", sum("allocated") "Allocated(M)", sum("used") "Used(M)", sum("free") "Free(M)", sum(df_cnt) "#(File)" from ( select a.tablespace_name, trunc(b.assigned/1048576) "allocated", trunc((b.assigned-a.free)/1048576) "used", trunc(a.free/1048576) "free", df_cnt from ( select tablespace_name, sum(bytes) free from dba_free_space group by tablespace_name ) a, ( select tablespace_name, sum(bytes) assigned, count(*) df_cnt from dba_data_files group by tablespace_name ) b where a.tablespace_name = b.tablespace_name UNION ALL SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt from dba_temp_files group by tablespace_name ) group by rollup(tablespace_name) ; spool off

How do you manually regenerate a form in Release 12?

1. Check the FORMS_PATH env variable. Assuming these are US forms the FORMS_PATH should include $APPL_TOP/resource and $APPL_TOP/forms/US. 2. To compile a form manually from the command line try either of these two command line arguments (replace FNDFBMAS.fmb with the form of your choice.): frmcmp.sh module=FNDFBMAS.fmb userid=apps/<apps password> module_type=form batch=yes compile_all=yes frmcmp_batch module=FNDFBMAS.fmb userid=apps/<apps password> module_type=form batch=yes compile_all=yes

How to distribute the load of concurrent requests in the same node ?

In order to distribute concurrent requests to distincts concurrent managers on the same node, you have to : 1 - Create a new concurrent manager on the forms Define Concurrent Managers from the System Administrator responsibility ( set the same setup as the Standard Concurrent manager ), then click on the button Specialization Rules and Include the concurrent programs you want to run on this concurrent manager. 2 - Query the Standard concurrent manager on the forms Define Concurrent Managers from the System Administrator responsibility, then click on the button Specialization Rules and Exclude the concurrent programs you include in step 1 for the new concurrent managers. 3 - Bounce the concurrent managers.

Steps to recover Applications context file if it is corrupted or deleted accidentally?

The Applications context file can be retrieved by running the adclonectx.pl script.

To retrieve the Applications tier context file,

• perl /clone/bin/adclonectx.pl retrieve
• On being prompted for the context file to be retrieved, select the option of retrieving the

          Applications tier context file that has been lost and retrieve it to the default location specified

          by the script.

The above command can be used only when INST_TOP the is still intact. In case that has also been lost

accidentally, the Applications tier context file may be retrieved as follows:

• Execute the following command on the Database tier:

  perl /appsutil/clone/bin/adclonectx.pl retrieve

• On being prompted for the context file to be retrieved, select the option of retrieving the

          Applications tier context file that has been lost.

• While confirming the location for the context file, set it to any existing directory with write permission.
• Once the context file has been generated in the specified location, move it to the location specified

          for the context file in the context variable 's_contextfile'.

To retrieve the Database tier context file,

• Execute the following command on the Database tier:

  perl /appsutil/clone/bin/adclonectx.pl retrieve

• On being prompted for the context file to be retrieved, select the Database tier context file and

          retrieve it to the default location specified by the script.