Upgradation ( 10.2.0.4.0 to 11.2.0.4.0 )

Before doing to upgradation be careful to  check and take care of  prerequisite.

this is useful for interview point of preparation manily.

1. Install the 11.2.0.4 Binaries

2. run the Preupgrade script in old home database 

      spool /db/RMAN/preupgrade_AUTOSK.log 

    @/Oracle/app/oracle/product/11.2.0/rdbms/admin/utlu112i.sql

    spool off

 

    Note : utlu112i.sql to check space on tablespace obsolete (invalid) / deprecated parameters etc

3. run the utlrp,sql 

  SQL> @/Oracle/app/oracle/product/11.2.0/rdbms/admin/utlrp.sql

    Note : utlrp.sql to recompile invalid objects

4. copy the pfile,spfile,orapwd,listener.ora,tnsnames.ora,sqlnet.ora .... from old to new home 

5. change the value of compatiable in new home pfile 

     compatible=11.2.0

6. take the invalid object count and dba_registry

  SQL>  select comp_name,status,version from dba_registry;

                            COMP_NAME                      STATUS      VERSION

                           ------------------------------         -----------     ------------------------------

                        Oracle Database Catalog Views  VALID       10.2.0.4.0

                        Oracle Database Packages and T VALID       10.2.0.4.0

7. shutdown the database in old home

             SQL>shutdown immediate;

8. start the database in mount stage in old home 

              SQL>startup mount

9. connect to RMAN

rman target /

* database full database backup in old home

* take the backup of the database 

Rman>run

{

allocate channel ch1 type DISK;

allocate channel ch2 type DISK;

allocate channel ch3 type DISK;

allocate channel ch4 type DISK;

backup as compressed backupset format '/db/RMAN/%d_%T_%t_%s_%p.dbf' database ;

backup current controlfile format '/db/RMAN%d_%T_%t_%s_%p.ctl';

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

}

  

10. shutdown the database in old home because it is mount why means we have taken the backup 

          SQL>shutdown immediate;   (old home )

11.    start the database in upgrade mode using new 11g24 home.

    

  export ORACLE_SID=AUTOSK

 export ORACLE_HOME=/Oracle/app/oracle/product/11.2.0

 export TNS_ASDMIN=$ORACLE_HOME/network/admin

 export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 export PATH=$ORACLE_HOME/bin:$PATH

12. Becareful if you above script don't user (. oraenv) mind it 

13.sql>startup upgrade

     sql> spool /db/RMAN/upgrade.log

     @/Oracle/app/oracle/product/11.2.0/rdbms/admin/catupgrd.sql

     spool off

14. spool /db/RMAN/upgrade.log

    @/Oracle/app/oracle/product/11.2.0/rdbms/admin/catupgrd.sql

     spool off

   Note :IT WILL TAKE SOME TIME AFTER RUN THE SCRIPT OF catupgrd.sql

15.run the utlrp.sql in new home

startup the database and run utlrp.sql located on new home

@/Oracle/app/oracle/product/11.2.0/rdbms/admin/utlrp.sql

16. chekc the invalid object count and check version,status from ( dba_registry )

select comp_name,version,status from dba_registry;

select owner,count(*),object_name,object_type,status from dba_objects where status='INVALID' group by owner,object_name,object_type,status;

17. Check in New Home

SQL>select owner,count(*),object_name,object_type,status from dba_objects where                                       status='INVALID' group by owner,object_name,object_type,status;

18. SQL> select comp_name,version,status from dba_registry;

COMP_NAME                      VERSION                        STATUS

------------------------------ ------------------------------ -----------

Oracle Database Catalog Views  11.2.0.4.0                     VALID

Oracle Database Packages and T 11.2.0.4.0                     VALID

                                                    Thank you

 

FRA ( Flash Recovery Area )

* FRA ( flash Recovery Area ) introduced in 10g later it renamed Fast Recovery area in 11g,

* FRA contains rman backuparchivelogs files and flash logs .......

* Advantage of FRA it automatically remove obsolute backup ( expire backup ).

* The FRA is essentially a disk location for the storage of files related to the recovery process.These     files in the FRA are managed by Oracle and RMAN automatically.  Files that could be found in the      FRA include:

1. RMAN backups
2. control file
3. logs

• online redo log copies
• archived redo logs
• flashback logs

* FRA parameters are

      db_recovery_file_dest

      db_recovery_file_dest_size

SCAN Listener and Virtual IP

 Scan Listener :

* we add or remove a node from cluster,it was required to changes the connection setting in client
  ( applicaton )  by configuring the connection using SCAN NAME this problem is eliminated.

* Irrespective number of nodes oracle recommands to have 3 SCAN IP's and one SCNA NAME which be decide to any of the SCAN IP i round rabin manner.

* the Benifit is clients using SCNA do not need to change if you add or remove nodes in the cluster.

* SCAN is a single network name that resolves theree different IP's registered in DNS or GNS.

* if we are using DNS scan listener ip's will store in

  /etc/resolv.conf


* how to start the scan listener

   $ srvctl start scan_listener -i 2


VIP ( Virtual IP ) :

* VIP is a alternate VIP address assigned to each node,

* In a cluster during a node failure vip of the  failed nodes moves to surving node and relay to                  the  applicaton,

* how to start the vip

    $ srvctl start vip -i Vip_name -v
                                             

RAC Background Processes

RAC Specific Background Processes:

LMS : Lock Monitor Service ( Global Cache Service )

         * it is responsible for transferring the blocks whenever needed by node,

         * it is helpful in cache fusion.

LMON : Lock Monitor Process ( Global Enque service )

         * Lmon will monitor the mechanism of LMD.
 
         * it is responsible for reconfiguration of local resources whenever an instances joins or leaves                 the cluster.

        * Responsible for dynamic remastering.

LMD : Lock Monitor Daemon ( Global Enque Service )

       * LMD will do the locking on blocks.

       * this daemon handles dead lock detections and remote enque request.


Lck : Lock Process :

       * it manages requests that are related to shared pool ( library cache and data dictinary cache ).

       * it manages non-cache fusion data.

       * it shares the parsing data.

DIAG: Diagnosability Daemon 

        *Monitors the health of the instance and captures the data for instance process failures.


    Note : i have given information RAC specific background processes,which process what do's,

Clusterware Processes

Clusterware Processes are

1. CRSD ( Cluster Ready service Daemon )

2. CSSD ( cluster syncronized service Daemon )

3. EVMD  ( Event Manger Daemon )

4. CTSD  ( Cluster Time syncronized Service Daemon )  ............


1. CRSD ( Cluster Ready Service Daemon )  :
   
     * it manages the OCR
   
     * it monitors and restarts hasd,failed instances and listener

     *  Generates events ( up, down ) where there is a change in state of  Cluster resources.

     *   Performs failover operations.


2. CSSD ( Cluster Syncronization service Daemon ) :

     * Cssd process will write the information to VD.

     * it performs NHB $ DHB operaitons.

    * node evietion ( reboot 0


3. EVMD ( Event Manager Daemon ):

   * it publishes events ( up,down ) to ONS ( Oracle Notification service )


4. CTSSD ( Cluster  Time Syncronization service Daemon ) :

  *  Inorder to syncronize the data and time


Note :
        i have given above matter just for understanding purpose and what it will do's.

Clusterware Components

Clusterware Components are

1. OCR ( Oracle Cluster Registry )

2. VD ( Voating Disk )

3. OLR ( Oracle Local Registry )


1. OCR ( Oracle Cluster Registry ) :

* OCR contains information cluster configuration like how many Database,how many instances,how     many services and state of the services

* CRSCTL,SRVCTL,LSNRCTL will updates the information to the OCR whenever state the change     of instance,listener are other services

* when OCR corrupted we are not able to start the cluster

* every four hours it will take automatic bakup,if you want to take manually as you want

     $ocrnfig -export

* To check ocr file location

  $ocrconfig


2. VD ( Voating Disk ):

* VD contains information Node membership information,every 3o seconds each node will send a          meaasge or signal to vating disk,stateing that is alive.

* if it is not able to send a signal to VD, VD will send some messages or signals to node and it will initiate the node reboot.

* every four hours backup also taking along with OCR,

* if voating disk is corrupted we are not able to start the cluster.

* To check VD location

  $crsctl query css votedisk


3. OLR ( Oracle Local Registry ):

* OLR contains information that allow cluster processes to be started up with the  OCR being in the ASM storage system,since the asm file system is unavailable untill the grid processes are started up a  local copy of the contents of the ocr is required which is store din OLR

* OLR contains GRID_HOME,GRID_VERSION,GPNP PROFILE,local host name,active version,ocr latest backup and location,node name,ocr about ocr daily,weekly backup location.

* To check olr location
 $ocrconfig -local

Patching

Patch is a program ( set of instructions ) to fix a particular problem or enhance / add a particular feature in existing program/product/software.

Oracle Patches Types

In Oracle, there are different types of patching available; there are several types of patches released by Oracle that could be applied to the software you are running. There are on-off patches to fix bugs, regularly released patches, and security patches. In the following I will explain about different types of patches that are available to be installed on your RDBMS software.

Critical Path Update/Security Patch Update (CPU/SPU)

Initially this kind of patches were called CPU patches, but later these were named as SPU patches. These are released quarterly (every 3 months) and contain security fixes only. These patches are no more available starting release 12c, and only PSU patches (discussed later) are available starting Oracle 12c Release 1. For 11.2.0.4 releases, SPUs are still being released on quarterly basis, probably until the end of life of 11.2.0.4.

Patch Set Update (PSU)

Patch Set Updates is a superset of SPU and contains all security patches that are part of PSU, and also other important bug fixes. PSU is also released quarterly.

SPUs and PSUs (and also Proactive Bundle Patches) are mutually exclusive, which means that you can only go for either of these and can’t install both. If you have installed a PSU on an Oracle release, now you should always install new PSUs on the top of it and you cannot revert to applying SPU patches. If you want to change your patching strategy and want to install SPUs, there is a proper way to switch your strategy which would include a complete rollback of the installed PSU and any other interim patches that were installed with (or on top of) the PSU (as some interim patches can only work with PSU patches, or a specific PSU release and needs to be rolled back if PSU is rolled back) and then install the SPU and then install interim patches once again that are suitable for the SPU. But after that you would need to stick with SPU patches strategy as you can’t revert to PSU strategy unless you repeat the same as explained above.

We can download a single PSU to have it installed on both GI homes and RDBMS home in a single go rather than downloading individual PSU patches for both homes and installing separately.

Both of above two are for Unix based environments. For Windows environment, you would need to download and install Bundle Patches.

Bundle Patch (BP)

Term “Bundle Patches” is used for quarterly released patches (like SPU and PSU) for Windows based environments and Engineered systems (Exadata etc.) patches. Like PSU patches, a single downloaded patch can be used to patch GI and RDBMS homes together.

Quarterly released patches for Engineered systems like Exadata are also called Bundle Patches and are super set of PSU. IT means that if you want to install quarterly patches on engineered systems, you would need to download and install Bundle patches. Although quarterly patches for Windows environment and for engineered systems are called Bundle Patches, but these are different patches actually.

Database Proactive Bundle Patch (DPBP)

Starting April 2016, the database patch for engineered system (Exadata etc.) has been renamed to Proactive Bundle Patch. Proactive Bundle Patch is a super set of PSU and contains some extra bug fixes. Although Proactive Bundle Patches are for engineered systems, these can still be applied on non-engineered systems. These are obviously better than applying PSUs as these contain some additional bug fixes.

You can switch your strategy of installing patches from SPU to PSU, PSU to Proactive Bundle Patch or Proactive Bundle Patch to SPU, but you would need to following instructions I have already explained above.

1. One - off patches ( Bug fix )

2. CPU ( Security patches )  i.e critical path update - CPU

3. upgrade patches ( bug fixes )

4. PSU ( bug fixes are security patches ) i.e PSU - path set update ( in 14th 2009 introduced )

5. SPU (security path update )

6. Bundle patches ( in 12c )


How to Apply a Patch:

i have given  below just for understanding purpose only, how to apply the patches

1. take the backup of oracle home and oracle inventory

  $ tar cvf   backuphome.tar $oracle_home


2. check the registry,history and invalid object count

    SQL>select * from registry$history,opathc lsinventory,invalid object count


3. download the patch and unxip the patch

4. check the version and read me document

5. stop the database and listener

6. opatch apply patchnumber

7. open the database and catbundle.sql script for CPU/PSU patches

      i.e catbundle it will update to registry$history

ASM ( Automatic Storage Management )

1. ASM stands for Automatic Storage Management.

2. it's providing Automatic Load Balancing overall Availiable disk ,it reduce the hot spot in file system,it prevents fragementation of Disks.

3.  it does Stripping and Mirroing  to increase relaiability and performance.

4. An ASM instance does not have it's own datafiles like a regular oracle  database.

5. A single ASM instances can manage all oracle database instance on that server
             CSS background process should be up and running to maintain ASM instances and database.

6. Manditory parameter pfile or spfile in ASM instance.

    instance_type=ASM
    instance_name=+ASM
    asm_diskstring=/dev/sda1,/dev/sda2
    asm_power_limit=2
    asm_diskgroup=DATA

7. asm_power_limit

       * it repreasents max. speed of asm instance during a rebalancing disk.
       * values = 1 to 11 ( by default 2 in ASM )

8. ASM instances uses several background processes like SMON,PMON,RBAL,ARBN,ASMB.........

   * RBAL - coordinate the disk activity

  * ASMB - coordinate the instance and database instance;

 * ARBN - process performs rebalancing work which can include moving data extends.


How to create a disk in disk group:

SQL> create diskgroup DATA disk '/dev/sda3';


how to ADD a disk in diskgroup:

SQL>alter diskgroup DATA add disk '/dev/sda5','/dev/sda6';


how to Check ASM disk :

SQL>select name,header_status,path,disk_number,free_mb,total_mb from v$asm_disk;



                                                Thank you

Crone Tab

It is a utility,it is used to Schedule the script ( backup ) particular point of time.

crontab stands for "cron table," because it uses the job scheduler cron to execute tasks; cron itself is named after "chronos," the Greek word for time.

Cron Tab Format :

<minutes><hours><day><month><week> cron.sh( cron file name );

Cron Tab Commands:

1. $ Crontab -e

           -  To enter the script( data ) in empty file or existing file.

2. $ Crontab -l

            - To check how many lines in the file.

3. $ Crontab -r 

               -   To Remove your crontab, effectively un-scheduling all crontab                            jobs.

4. sudo crontab -u slm -e

            - Edit the crontab of the user named slm. The -u option requires                        administrator privileges, so the command is executed using sudo.

5. sudo crontab -u saleem -l

             - View the crontab of user saleem.

6. sudo crontab -u abc -r

          - Remove the crontab of user abc

7. $ Crontab -l   >  Crontab.bkp

            -  it will take backup crontab.  

Examples Of crontab Entries :

15 6 2 1 * /home/slm/backup.sh

Run the shell script /home/slm/backup.sh on January 2 at 6:15 A.M.

15 06 02 Jan * /home/slm/backup.sh

Same as the above entry. Zeroes can be added at the beginning of a number for legibility, without changing their value.

0 9-18 * * * /home/carl/hourly-archive.sh

Run /home/carl/hourly-archive.sh every hour, on the hour, from 9 A.M. through 6 P.M., every day.

0 9,18 * * Mon /home/wendy/script.sh

Run /home/wendy/script.sh every Monday, at 9 A.M. and 6 P.M.

30 22 * * Mon,Tue,Wed,Thu,Fri /usr/local/bin/backup

Run /usr/local/bin/backup at 10:30 P.M., every weekday.

Cronetab Options :

-u   :

Append the name of the user whose crontab is to be tweaked. If this option is not given, crontab examines "your" crontab, i.e., the crontab of the person executing the command. Note that su can confuse crontab and that if you are running it inside of su you should always use the -u option for safety's sake. The first form of this command is used to install a new crontab from some named file, or from standard input if the file name is given as "-".

-l :

Display the current crontab

-r :

Remove the current crontab.

-e :

Edit the current crontab, using the editor specified in the VISUAL or EDITOR environment variables

-i :

Same as -r, but gives the user a "Y/n" prompt before actually removing the crontab

-s :

SELinux only: appends the current SELinux security context string as an MLS_LEVEL setting to the crontab file before editing or replacement occurs. See your SELinux documentation for details.

Last Analyzed of Particular Table

To check  last analyse particular table :

1. To check particular user of tables

        SQL>select owner,tables_name,last_analyzed,sample_size from dba_tables where owner='RPM12' order by table_name;

2. To check particular Last analysed table

   SQL>select  num_rows,last_analyzed,sample_size,chain_cnt,partitioned,owner,table_name from                  dba_tables where table_name='ITEMMASTER';

3. Checking Indexes

     SQL> select owner,table_name,index_name,SAMPLE_SIZE,LAST_ANALYZED,BLEVEL from dba_indexes where owner='RPM12';

4. execute sql script the stats gather table

     SQL>exec dbms_stats.gather_table_stats('rpm12','itemmaster');

Note: to execute this statement  after again check table_last analyzed than get result.

5.finally check the last analyzed of table (data)

    SQL>select  num_rows,last_analyzed,sample_size,chain_cnt,partitioned,owner,table_name from                   dba_tables where table_name='ITEMMASTER';

How Standby Database Build ?

Actually Standby Database Creation  i know two types
1. Using Backup of Primary Databse
2. Using Duplicate command
        Both are Allmost similar but depend upon the company Documents they will do.

Standby Database Creation :

1. In primary database,edit and insert some parameter

    $ vi pfile.sql
             instance_name=dev
            database_name=dev
            db_unique_name=primary
            log_archive_dest_2='target service name' valid for=(primary dbname,standby dbname)                                                          lgwr Assync oraffirm or no affirm
            standby_file_management=auto
            fal_server='remote tns name'
            fal_client='local tns name'

2. In Standby database,edit and insert some parameter

  $ vi pfile.sql
 
             Instance_name=dev
            database_name=dev
            db_unique_name=Standby
            db_file_name_convert='/u01/home/dev','/u01/home/dev/standby/data'
            log_file_name_convert='/u01/home/dev','/u01/home/dev/standby/data'

            log_archive_dest_1='/u01/home/standby/archive'

3. Listener,Tns service,pwd files are common to create,if already have use them.

4.Take the primary database backup ( cold backup )

5. Take the standby control file 

    

    Rman>backup controlfile for standby '/u01/home/standby/controlbakcup.ctl';

6. put the database in no mount ( standby database )

7. Restore the standby control file 

    Rman>restore control file from '/u01/home/standby/controlbakcup.ctl';

8. Put the database in mount stage ( standby database )

     SQL>startup mount;
         

9. start the MRP process

   SQL>alter database recover managed standby database disconnect;




Createing Standby database using  Duplicate Command :

 Rman>duplicate target database for standby from active database;

Standby Database Types in Data Guard ?

In Standby Database are three types

1. Physical Standby 

2. Logical Standby

3. Snapshot Standby

1. Physical Standby:

    

  Physical standby are have three types of  modes ,they are 

                     i. Maximum Protection

                    ii. Maximum Performance

                   iii. Maximum Availiability

   i. Maximum Protection :

            * In Max protection Mode Log writer writes redo entries to standby log file, for that reason                    we require additional Standby logs  in standby database,

           *  If  network  goes down  between primary and standby,Primary and standby is not                                  communicate due to network issue,primary automatically will goes down.

   To Set Max. Protection:

       

       In  Primary Database: 

           1. Put the database in mount.

                 SQL>alter database open;

           2. SQL>alter database set standby database to maximize protection;

           3. open the databse 

                SQL>alter database open;

 ii. Maximum Performance : 

           * In Maximum performance Archive logs will be transfer to primary to standby by Log                          network  Service(LNS).

           * Then this archive logs will be write to the database by MRP process.

          * If any network issue happen in primary and standby,FAL SERVER will monitor the Network               Issue onces it comes up FAL SERVER  send a request to FAL CLIENT  saying that i                           have these  many archives can you please send the remaining one.

  Note: FAL SERVER AND FAL CLIENT  used in order to resolve the archivelog gaps

  iii. Maximum Availiability :

         * In Maximum Avaliability first it behave like a  maximum protection write the redo entries to                Standby,if it fails to write standby logs due to network issue then it will behave like a max.                  performance and send the  archive gs to standby database and MRP process apply this                          archive  to standby database.        

                    

                  

    To set  Primary Database :

       1. put the database mount

       2. SQL>alter database set standby database to maximize availability;

       3. open the database

            SQL>alter database open

       4. To check standby  Database Modes

           SQL> select protection_mode from v$database

2. Logical Standby :

               in READ ONLY state, LSP will run

3. Snapshot standby Database :

Physical standby database can be converted to snapshot  standby database, which will be in  READ WRITE mode, can do any kind of testing, then we can convert back snapshot standby database to physical standby database and start MRP which will  apply all pending archives

                                                         Thank You

Oracle Apps DBA Interview Questions and Answers

1. Explain Architecture of Oracle Apps 11i?

Apps 11i is Mutli Tier architecture with Desktop Tier (Client Tier), Middle Tier (Application Tier) and Database Tier.

2. What is iAS Patch ?

iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shipped as Shell scripts & you apply iAS patches by executing Shell script.

3. I am applying a patch , can I open another session and run adadmin ?

 Yes, We can run unless you are running a process where workers are involved

4. I am applying a patch , can I open another session in another node and run adpatch?

No because it will create tables while running first session when you start the 2nd session it will fail due to the first

5. Can you clone from multi node system to single node system & vice versa?

Yes, this is now supported via Rapid Clone, Check if your system has all prerequisite patches for Rapid Clone and you are on latest rapid clone patch.

6. Does rapid clone takes care of Updating Global oraInventory or you have to register manually in Global oraInventory after clone?

Rapid Clone automatically updates Global oraInventory during configuration phase. You don't have to do anything manually for Global oraInventory.


7.  How to determine Oracle Apps 11i Version ?

select RELEASE_NAME from fnd_product_groups;

You should see output like

RELEASE_NAME———————–11.5.10.2

8. How to find Database version ?

 SQL> select * from v$version;

The command returns the release information, such as the following:Oracle9i Enterprise Edition Release 9.2.0.7.0 – ProductionPL/SQL Release 9.2.0.7.0 – ProductionCORE 9.2.0.7.0 ProductionTNS for 32-bit Windows: Version 9.2.0.7.0 – ProductionNLSRTL Version 9.2.0.7.0 – Production

9. How to find opatch Version ?

opatch is utility to apply database patch , In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”

You can check OPatch -lsinventory


10.   What is difference between adpatch & opatch?

# adpatch is utility to apply oracle apps Patches whereas
# opatch is utility to apply database patches

11.   Can you use both adpatch & opatch in Apps?

Yes you have to use both in apps, for apps patches you will use adpatch utility and for applying database patch in apps you will use opatch utility.

12.   Where will you find forms configuration details apart from xml file?

Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg for forms client connection used each time a user initiates forms connection.

13.   What is forms server executable Name?

f60srvm

14. How to find out invalid objects in the database

 select count(*) from dba_objects where status =’INVALID’

15. How you will see hidden files in linux/solaris?

 ls -la

16. How to find that the database is 64-bit/32-bit?

 $RDBMS_ORACLE_HOME/bin/file oracle


17. What is top command?

 top is a operating system command, it will display top 10 processes which are taking high cpu and memory. 8. What is a patch?Ans : A patch can be a solution for a bug/it can be a new feature.

18. What are the different types of patches?

 oneoff, mini packs, family packs, maintanance packs, rollup pathches, colsolidated patches.


19. What is a oneoff patch?

 An oneoff patch is a small patch of (20-90K size) without any pre-req’s


20. What is a mini pack ?

 A mini pack is one which will upgrade any product patchset level to next level like AD.H to AD.I

21. What is Family pack ?

 A Family pack is one which will upgade the patchset level of all the products in that family to perticular patchsetlevel.


22. What is Maintanance pack ?

 A maintanance pack will upgrade applications from one version to another like 11.5.8 to 11.5.9


23. What is a Rollup patch?

 A rollup patch is one which will deliver bug fixes identified after the release of any major application versions like 11.5.8/11.5.9


24. What is consilidated patch?

Consolidated patches will come into pictures after upgrades from one version of applications to anoter, all post upgrade patches will a consolidated and given as consolidated patch.


25. How u will find whether a patch is applied/not?

 Query ad_bugs.


26. What is the other table where u can query what are the patches applied?

 Ad_applied_patches


27. What is the difference between ad_bugs and ad_applied_patches?

 A patch can deliver solution for more than one bug, so ad_applied_patches may not give u the perfect information as in case of ad_bugs.


28. How u apply a patch?

 adpatch


29. What inputs you need to apply a patch other than driver name and etc?

apps and system passwords


30. What are the table u r adpatch will create and when?

Adpatch will create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table when it will apply d,g and u drivers


31. What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table?

 FND_INSTALL_PROCESSES table will store the worker information like what job is assigned to which worker and its status. AD_DEFERRED_JOBS will come into picture when some worker is failed, it will be moved to AD_DEFERRED_JOBS table, from where again adpatch will take that job and try to resign, after doing this 3 times if still that worker is failing, then adpatch will stop patching and throw the error that perticular worker has failed. We need to trouble shoot and restrart the worker.

32. If it is a multinode installation which driver we need to apply on which node?

 c,d,g on concurrent node and c, g on web node. If it is u-driver we need to apply on all nodes.


33. While applying a application patch is that necessary that u r database and listener should be up?

 Yes . why because adpatch will connect to database and update so many tables etc…..



34. While applying a patch if that patch is failing because of a pre-req then how you will apply that pre-req patch and resume with the current patch?

 We need to take the backup of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables and restart directory at APPL_TOP/amdin/SID and then use adctrl to quit all the workers. Then apply the pre-req patch , after that rename u r restart directory to its original name and create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables from the bcakup tables. Start adpatch session and take the options want to continue previous session.


35. What is adctrl?

 Adctrl is one of the adutilities, which is used to check the status of workers and to manage the workers.


36. Can u name some of the menu options in adctrl?

 Check the status of workers, tell manager that worker has quited, restart a failed worker etc….


37. How to skip a worker and why?

 We can skip a worker using option 8 in adctrl which is hidden. We will go for skipping a worker when we have executed the job which the worker is supposed to do.


38. How adpatch knows what are the pre-req’s for the patch which it is applying?

 With every patch a file called b.ldt file will be delivered which contain the pre-req information. adpatch load this into databse using FNDLOAD and check , whether those pre-req patches were applied or not.


39. What is FNDLOAD ?

 FNDLOAD is a utility which is similar to sqlloder but loads code objects into database, where as SQLLOADER loads data objects into database.


40. What c-driver will do?

 C-drive copies the files from patch unzipped directory to required location in u r application file system. Before copying it will check the file version of the existing file at the file system with the file version of the file in the patch. If the patch file version is higher than what it is at file system level then only c-driver will copy that files.


41. How adpatch will know the file versions of the patch delivered files?

With each patch a file with name f.ldt is delivered , which contain the file versions of the files dilivered with the patch. Adpatch will use this file to compare the file versions of files its delivering with the file on file system.


42. What is the adpatch log file location?

 APPL_TOP/admin/SID/log


43. What is the worker log file name and its location?

 adwork01,adwork02…… and location is APPL_TOP/admin/SID/log


44. How u will know what are the files the patch is going to change just my unzipping the patch?

When u unzip a patch it will keep all the files related to a particular product under that directory inside u r patch directory for example if the patch delivering files related to FND product then it will create a sub directory under the patch directory with the name FND in which it will put all related files to that product


45. What is the significance of backup directory under u r patch directory?

When we apply a patch it will keep the copy of the files which its going to change in file system.


46. What are the different modes you can run your adpatch?

1.Interactive – default mode

2.Non interactive – Use defaults files to store prompt values

(adpatch defaultsfile= interactive=no)

3. Test – Without actually applying a patch just to check what doing.(adpatch apply=no)


47. How u will monitor u r applications as well as database?

 We have our custom scripts which is sheduled to run at a specific time which will monitor whether applications and databases are up/not. And it will mail us if some processes is not running. And we have one script which will check database alert log for ORA errors and mails it to us . Based on this we will react.


48. What are the latest ORA errors u have encountered?     

 Useually we will get the ORA errors like unable to extend the tablespace by so and so size. And we will check those tablespaces for space, if space is not there we will resize the datafile and add one more datafile.

50. Which table u will query to check the tablespace space issues?

 bytes column in dba_free_spaces and dba_data_files


51. Which table u will query to check the temp tablespace space issues?

 dba_temp_files


52. What is temp tablespace? And what is the size of temp tablespace in u r instances?

 Temp tablespace is used by so many application programs for sorting and other stuff. Its size is between 3 to 10 GB.


53. What is autoconfig?

Autoconfig is an adutility which is used to maintain application environment and configuration files.


54. What are the parameter autoconfig will ask for?

Context file name and apps password


55. What is context file?

 Context file is a central repositary, which stores all application configuration information. The name is like _ .xml


56. How you will find autoconfig is enabled/not for u r applications?

 1. Open any env / configuration files, the first few lines will tell u that this files are maintained by autoconfig.2. If contextname.xml file is there in APPL_TOP/admin


57.  How autoconfig will create env and configuration files?

 Autoconfig will go to each and every top template directory take the templates from there and fill the values from xml file and create the required files.


58. In how many phases autoconfig will run?

 Autoconfig will run in 3 phases.

1.INIT – Instantiate the drivers and templates

2.SETUP – Fill the templated with values from xml and create files

3.PROFILE – Update the profile values in database.


59. What is the location of adconfig log file?

 APPL_TOP/admin//log/


60. Is it possiable to restore a autoconfig run?

Ans : Adconfig will create a restore.sh script at $APPL_TOP/admin//out/. This restore.sh will copy the backed up files before autoconfig run to its original locations. But the profile values updated in the database can’t be restored back.

61. How to run autoconfig in test mode?

 adchkcfg.sh script at AD_TOP/bin. This script will run autoconfig in test mode and create the difference file which tells us what is going to change , when u actually run autoconfig.


62. How to find autoconfig is enabled or not for database?

 If we have appsutil directory under RDBMS_ORACLE_HOME


63. When a patch delivers java files what extra file u will get when u unzip the patch, other then u r dirver and readme files?

 j.zip52.


64. What is apps.zip/appsbrog2.zip file?

 apps.zip/appsbrog2.zip is the patchable archive of all java class files required for oracle application.Apps.zip was used to old application version, but from 11.5.8 onwards its appsbrog2.zip


65. What is the location of apps.zip/appsbrog2.zip?

 AU_TOP/java and JAVA_TOP


66. What is for “validating apps schema” option in adadmin?

 It will check for the corrupted objects in apps schema


67. What is “compile apps schema” option in adadmin?

 It will compile the invalid database objects.


68. How to find invalid objects in database?

 select count(*) from dba_objects where status=’INVALID’;


69. How to find MRC is enabled or not?

 In adadmin if covert to MRC options is there , then MRC is not enabled,If maintain MRC options is there , then MRC is enabled.


70. How to find Multi-Org is enabled or not?

 In adadmin if covert to Multi org option is there, then Multi-org is not enabled. If maintain multi-org options is there, then Multi-org is enabled.


71. What is mean by MRC?

MRC stands for Multiple reporting Currency, this should be enabled to see the reports in different currencies like (rupees,yaans etc).


72. What is Multi-Org?

If this is enabled we can store multiple organization information in a single oracle application instance.


73. What is the configuration file for adutilities (like adadmin,adconfig etc)?

 adconfig.txt @APPL_TOP/admin


74. What is adrelink?

 adrelink will relink the executables with the libraries. Generally we will go for adrelink when some patch delivers some library files, or when executables were corrupted.


75. How to find the version of a file?

1. adident Header 2. strings -a filename grep Header


76. What is adodfcmp utility?

 This utility is used to recreate/repair corrupted database objects from odf(object defination files) files.


77.  How you will change apps password?

 FNDCPASS 0 y apps/ system/ SYSTEM APPLSYS


78. What if apps password is changed with alter command?

 Applications won’t work.


79. What is the difference between alter and FNDCPASS in changing apps password?

 FNDCPASS will update some fnd tables other than standard tables.


80. Where the FNDCPASS utility is located?

Concurrent node @FND_TOP/bin


81. How to find out what component of u r oracle applications were installed on which node?

 Xml file (context file)


82. How to find the version of httpd/Apache web server?

 $IAS_ORACLE_HOME/Apache/bin/httpd –version


83. What is the configuration file for httpd and what is the location of it ?

 httpd.conf @IAS_ORACLE_HOME/Apache/Apache/conf


84. Where you will see when you have some problem with u r webserver(httpd/Apache)?

 access_log & error_log @IAS_ORACLE_HOME/Apache/Apache/logs


85. When Apache starts what other components its start ?

 PL/SQL Listener, Servlet Engine, OJSP Engine


86. What is jserv?

 jserv is nothing but servlet engine which will run u r servlets. It’s a module of apache which supports servlets.


87. What is self service application?

Whatever part of u r oracle application u r able to see through web browser is self service.


88. Where u will see when u r not able to get self service applications?

 access_log,error_log, error_pls, jserv.log, wdbsvr.app(for apps password)


89. What is the location of jserv.log?

 IAS_ORACLE_HOME/Apache/Jserv/log


90. What is the location of wdbsvr.app ?

 IAS_ORACLE_HOME/Apache/modplsql/cfg


91. What are jserv.conf and jserv.properties files?

 These are the configuration files which were used to start jvm’s(servlet engine) by apache.


92. What is mean by clearing cache and bouncing apache?

1. Stop apache (adapcctl.sh stop apps)

2. Clear cache – Go to $COMMON_TOP/html/_pages and delete _oa_html directory

(rm –r _oa__html)

3. Start apache (adapcctl.sh start apps)


93. What is forms configuration file and its location?

 appsweb_contextname.cfg @$COMMON_TOP/html/bin


94. What are the different modes u can start u r form server?

 socket and servlet


95. What is the difference beween socket and servlet mode?

 In socket mode forms sessions are represented by f60webmx

In servlet mode forms sessions are represented by apache processes.


96. What is forms metric server and client?

When there are more than one form sever instances then forms metric server and clinet will be used to load balance.


97. Where the forms server related errors will be logged?

 access_log and error_log


98. What is report server configuration and log file name and its location?

Configuration file – REP_.ora
Log file – REP_.log @806_ORACLE_HOME/reports60/server


99. What is CGIcmd.dat file and its location?

 CGIcmd.dat file is the run time parameter file the report server located @ 806_ORACLE_HOME/reports60/server


100. What is the significance of DISPLAY variable?

 Vnc server should be up and running at the specified port value in DISPLAY variable, otherwise reportserver may not able to show the graphics in Reports.


101. Where is the concurrent manager log file located?

 $COMMON_TOP/admin//log or $APPLCSF/$APPLLOG


102. Is apps password necessary to start all the components of oracle application?

 No. Only to start/stop concurrent managers apps password is needed.


103. What is a concurrent manager?

 A concurrent manager is one which runs concurrent requests.


104. What are the different types of concurrent managers?

 1. Internal concurrent manager – Will start all other managers and monitor

2. Standard Manager – All concurrent request by default will to go this

3. Conflict resolution manager – Concurrent programs with incompatabilites will be handled by this

4. Transaction manager – Handle all transaction requests


105. What are actual and target count in ‘Adminster Concurrent Managers form’?

 Target is the no. of concurrent processes a manager is supposed to start(specified in the defination of concurrent manager).Actual is the no. of processes a manager started actually.

Target and Actual should be always same.


106. What if Target and Actual are not same?

 It means at operating system level resources are low to accomidate the required processes for concurrent managers.

107. What are work shifts?

 Work shifts are nothing but timings at which the concurrent manager is supposed to run.


108. What if internal concurrent manager target and actual are not same?

 we need to bounce the concurrent manager using adcmctl.sh

109. How to bounce a single concurrent manager?

From frontend using ‘Administer Concurrent Manager form’.


109. When we change apps password , is it necessary to bounce application?

Only we need to bounce concurrent managers.

110. What is dbc file and its location?

dbc file contain database connection information. DBC file is used by oracle applications to connect to database. Its location is $FND_TOP/secure

How to Trace a Particular Session ( User ) ?

To Enable trace file for Particular Sessin (User ):

First we have to know spid number,i know to methods to trace a session one method is oradebug and another method execute the file.


Method 1:

SQL>Select a.pid, a.spid, a.username from v$process a ,v$session b where b.paddr=a.addr and sid=429;

       PID             SPID      USERNAME
      ----------    ------------   ---------------
       115             14727        oracle

SQL>oradebug setospid 14727;
    Oracle pid: 115, Unix process pid: 14727, image: oracle@PSCF

SQL>oradebug event 10046 trace name context forever,level 12;
Statement processed.

SQL>oradebug tracefile_name;
Statement processed.

SQL>oradebug unlimit;
/Oracle/app/oracle/admin/RPM12/udump/rpm12_ora_14727.trc

Method 2:

SQL>exec dbms_system.set_sql_trace_in_session(777,143,true);

777 - sid number

143 - serial number

true - enable


To Disable the Trace File:

SQL>oradebug setospid 14727;
    Oracle pid: 115, Unix process pid: 14727, image: oracle@PSCF

SQL>oradebug event 10046 trace name context off;
Statement processed.

Generating Tkprof:

After the trace the particular session we get a trace file but it is a binary format for that resion we are generating the tkprof

tkprof /ofi11i/admin/TESTCE2/udump/testce2_ora_24248572.trc testce2_ora_24248572.txt sort=exeela,fchela,prsela

How to Generate Tkprof and User of Tkprof

Use of Tkporf :

              Tkprof is used to convert Binary file ( Trace File ) to  Text file ( Readable format ).


How to Generate TkProf :

Below i have given trace file name( . trc ) and text file name( .txt ) ,trace file (binary format ) data will be copied to text file ( Readable format ).after i have given sorting order like executing,fetching and parsing

$ tkprof /Oracle/app/oracle/admin/dev/udump/rpm12_ora_20524.trc                                        /Oracle/app/oracle/admin/dev/udump/rpm12_ora_20524.txt   sort=exeela,fchela,prsela

Check :

After Generating tkprof i have checked in cat command or more command,below i have given just example of the text file ( i have pasted just few lines only below text ).

[oracle@slm~] $ cat /Oracle/app/oracle/admin/RMSCZ01/udump/rmscz01_ora_27878.txt

TKPROF: Release 10.2.0.4.0 - Production on Thu Mar 2 15:23:03 2017

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: /Oracle/app/oracle/admin/dev/udump/rpm12_ora_27878.trc
Sort options: exeela  fchela  prsela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

select (:b0||ticb_loc)
from
 tsc_po_req_tsf where (ext_ref_no=TO_NUMBER(:b0) and rownum=1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    272      0.03       0.03          0          0          0           0
Fetch      272    379.58     383.56      65462    8950848          0         272
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      545    379.62     383.60      65462    8950848          0         272

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 63

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     271        0.00          0.00
  db file sequential read                        35        0.01          0.02
  db file scattered read                       4325        0.09          9.48
  latch free                                      2        0.00          0.00
  SQL*Net message from client                   271        0.00          0.10
  latch: cache buffers chains                     4        0.00          0.00
********************************************************************************

How DataGuard Gaps Resolve ?

If archivelogs Gap is more,we have to take SCN Incremental Backup

Syncing DataGuard using SCN Based Incremental Backup :

1. Check the  Bothside ( primay and Standby Database ) Current SCN number,applied Archivelogs and Archivelog Gaps

   By using this views v$archive_log ( to check Applied archivelogs )
                              v$archivelog_gap  (to check archivelog gaps )
                              v$database (SCN Information)
   2. take the SCN incremental Backup
    
 Rman>backup incremental from SCN '143143' database format                                                  '/u01/rman_inc_local/forstandby_%' tag 'forstandbylocal';

3. Tansfer that backup sets created on the primary system to  Standby system.

    $ scp backupfiles username@target server name:'/u01/h0me/standby'

4. connect to standby database as the rman target and catalog all incremental backup piece.

        Rman>catalog start with '/u01/rman_inc_local'

5. recover the standby database with no redo option.

    rman >recover database noredo;


6. in Rman, connect to the primary database and create a standby controlfile backup

   Rman>backup current controlfile for standby format '/u01/rman/standbycontrol.ctl';


7. stop the MRP process

8. Shutdown the standby database.

9. start the databae no mount

10. Restore the standby controlfile

Rman>restore standby controlfile from '/u01/rman/standbycontrol.ctl';


11. Mount the Standby Database

12.start the MRP process

13. Check the Current SCN number,applied Archivelogs and Archivelog Gaps

   By using this views v$archive_log,v$archivelog_gap,v$database(SCN Information)


14. Check DG configuration status using broker

OCMCWPAL>dgmgrl
DGMGRL> connect sys/pwd@tns
DGMGRL>show configuration verbose;

It wil show Protection mode: like max_availibility

If archivelogs Gap is less,then we have to Register the logs 

1. Check the  Bothside ( primay and Standby Database ) Current SCN number,applied Archivelogs and Archivelog Gaps

   By using this views v$archive_log ( to check Applied archivelogs )

                              v$archivelog_gap  (to check archivelog gaps )

                              v$database (SCN Information)

2. Stop the MRP process
     
3. Register the log files
            SQL>alter database register logfile '/u01/home/arch77.dbf';

4. Start the MRP process

How RMAN Works ?

* when ever user Executea  Backup Statement first it will read in control file for datafile Location,

 * Later it will read  the datafile Header,it will copy the  used filled blocks Data to backup Piece Via Large pool.

How to Refresh a Table ?

1. Check the size of the Table in Dev.

 SQL> select sum(bytes/1024/1024/1024) from dba_tables where tablename='EMP';

SUM(BYTES/1024/1024/1024)
-------------------------
                .00012207

2. check the free tablespace size in target  database by using dba_freespace

3. Export the Table

  

   $ expdp dumpfile=emp.dmp directory=export tables=rms12.emp logfile=tables_emp.log

4. Copy the Dump file to target server

    $ scp dumpfilename username@target servername:/u01/backup/export

5. import the table

     $ impdp dumpfile=emp.dmp directory=import tables=rms12.emp

Note : Table_exist_action = Skip or Replace or append or turncate

                                                Thank You

How to a Refresh Schema ?

1. Check the size of the Schema in Dev.

 SQL> select sum(bytes/1024/1024/1024) from dba_segments where owner='SLM';

SUM(BYTES/1024/1024/1024)
-------------------------
                .00012207

2. check the object count and status in Dev

SQL> select owner,object_type,count(*) from dba_objects where owner='SLM' group by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(*)

--------------------             -------------------               ----------

SLM                                     TABLE                              17

3. Check the available space in the file system

     $ df -h or $ df -g

4. create a directory OS Level and Database Level.

    $mkdir export                       -    os level

    sql>create directory export as '/db/DBATEST/i1/export';

5. check the directory created or not at database level of source database by using

   

              dba_directories

6. Export the Schema

  

   $ expdp dumpfile=rms12.dmp directory=export schemas=rms12 logfile=schemas_rms12.log

7. Copy the Dump file to target server

    $ scp dumpfilename username@target servername:/u01/backup/export

8. check the free tablespace size in target by using dba_freespace

9. If schema is already exists it will droped it.

   $ drop user username cascade;

10. import the Schema 

     $ impdp dumpfile=rms12.dmp directory=import schemas=rms12 

11. Finally check the object count and status

    

       SQL> select owner,object_type,count(*) from dba_objects where owner='SLM' group by                               owner,object_type;

               OWNER                          OBJECT_TYPE           COUNT(*)

               --------------------             -------------------               ----------

               SLM                                     TABLE                              17

                                                Thank You