Sunday, March 23, 2008

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.


Saturday, March 22, 2008

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.