Overview of RMAN Recovery Catalog

  RMAN Recovery catalog is a schema that is created in separate DB. It contained RMAN metadata obtained from the target database control file. RMAN stores, uses, and maintains the information in the recovery catalog. The recovery catalog is maintained by RMAN when you do following Register the target database in catalog Resynchronize the catalog with control file of target DB Change the information about the backups or files. Perform backup,restore,recovery operations   RMAN Catalog Contents Datafile and archived redo log file backup sets and backup pieces. Datafile copies Archive and redo log files The physical structure of the target database.   Why use a recovery catalog? It makes restore if all the target database is lost, including the controlfile. As that controlfile contains all backup information, the catalog would then be used to identify a controlfile backup If an RMAN catalog is not used, the controlfile autobackup should be configured to

Difference between RMAN Validate and crosscheck commands

  RMAN validate and crosscheck commands are using for maintenance activity RMAN Validate RMAN Validate is the 11g New feature. It’s used to find the corrupted blocks and missing files This command is check for validity of data files and update the v$database_block_corruption view in case it finds any corruption Validate command can also use to check Database, Tablespace, Datafile, Spfile, Archivelog, Control file   RMAN VALIDATE COMMANDS Validate whole Database RMAN> backup validate database; Validate Tablespace RMAN> backup validate tablespace system; Validate a specific datafile RMAN> backup validate datafile 8; Validate SPFILE RMAN> backup validate spfile; Validate archived redo log files RMAN> backup validate archivelog all; Validate current control file RMAN> backup validate current control file;   Following commands, we will see backup is valid and available for restoration process.   RMAN> restore database valid

Overview of Oracle Database Result Cache

Result cache introduce in 11g It’s area of memory either in SGA/ client application memory It’s allows results of query to be cached in SGA Possible to reduce amount of physical I/O, logical I/O, number of sorts, amount of CPU   How Result cache works When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache. When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.   Result cache Type There are two types of result cache there Server Result cache Used shared memory Available to all session Client Result cache

R12.2 Application Health check Report

Oracle Application R12.2.3 Instance Health Check - PROD   1.Database Server :<Hostanme> 2.Oracle RDBMS Version: 3: Operating System : Red Hat Enterprise Linux Server release 6.6 (Santiago) 4.Disk Utilization Volume Total Disk Space Size in GB Available Disk Space Size in GB / 100G 64G /Oracle 984G 244G /Backup 606G 201G   5 Background services Background Services Status SMON UP POMN UP CKPT UP LGWR UP DBWR UP Listner status UP     6.Alert Log check for critical ORA Errors No errors 7.Instance Efficiency Parameter Recommended Current status Remark Buffer Hit Ratio 98 93   L

How to set Workflow Mailer Override Address from Backend in R12.2

  Step 1 : Login the application Step 2: Use below script $FND_TOP/sql/afsvcpup.sql Cd $FND_TOP/sql/ Sqlplus apps/apps SQL> @ afsvcpup.sql Select component id for Workflow Notification Mailer - default is 10006 Enter Component Id: 10006 Show you the parameters and their values for the mailer Select the comp param id for the Override address (it will say Test Address ) - default is 10093 This script will display the following: You have selected parameter: Test Address Current value of parameter: NONE Enter a value for the parameter: <Here you will put the email address > Retest the Workflow mailer and confirm the override now works as expected.   Using SQL Query’s to set email address First check the current address use below query SELECT fscpv.parameter_value FROM fnd_svc_comp_params_tl fscpt, fnd_svc_comp_param_vals fscpv WHERE fscpt.display_name = 'Test Address' AND fscpt.parameter_id = fscpv.parameter_id;   Update email addre

How to Recover apps password in r12.2

  If we forget the apps password for r12.2 instance follow the below steps to recover the password Step 1 : Login to Database server sqlplus / as sysdba STEP 2 : Create Function for to decrypt the encrypted password SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME ',java.lang.String) return java.lang.String'; / Function created. STEP 3 : Query for encrypted password SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST'; Output ENCRYPTED_FOUNDATION_PASSWORD -------------------------------------------------------------------------------- ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A STEP 4: Query for decrypt the password SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99

ORA Errors

This ORA Error post will update frequently for same page  ORA-00031: session marked for kill. The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. Solution Find the thread in OS level select vs.sid,vs.username,vs.osuser, vs.process,vp.spid from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username='<User_name>' and vs.osuser='<applmgr>'; Will get output like this SID    USERNAME     OSUSER   PROCESS   SPID 123        AP              applmgr     456          8765 Kill the session from OS level kill –9 spid Login databas