RMAN Database Restore and Recovery scenarios

RMAN  performs backup and recovery tasks on the databases and automates administration of the backup strategies.
Case 1: ALL DATA FILES ARE LOST
Case 2: FEW BLOCKS IN DATAFILE GOT CORRUPTED
Case 3: ONLY ONE DATA FILE IS LOST/CORRUPTED
Case 6:  ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST
Case 5: SPFILE/PFILE LOST
CASE 1 : ALL DATA FILES ARE LOST
Assumption :
you have good backups of your database
you have access to all redo archive files generated after the backup was taken.
you have all the redo required to recover the database datafiles.
You can perform a complete database-level recovery in this situation with either the current control file or a backup control file.
i) Using current control file
In this situation, we simply start up the database in mount mode, issue the restore and recover commands, and then open the database:
$ rman
RMAN> connect target /
RMAN> startup mount
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Remember that RMAN will look in older backups if it can’t find a backup piece or if corruption is detected.
RMAN will search through backup history until it locates a good backup or until it exhausts all possibilities. This feature is called restore failover.
What about Read only and temporary tablespaces?
Read only tablespaces:
NOTE: By default, the restore command skips datafiles associated with read-only tablespaces. If you want read-only tablespaces restored, then you must use the check readonly command.
RMAN> restore database check readonly;
Temporary Tablespaces:
You don’t have to restore or re-create missing locally managed temporary tablespace tempfiles. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles.
If you wanna restore form some specific backups which you tagged:
RMAN> restore database from tag WEEKLY_BACK
How to use until clause with RMAN restore command
You can also tell RMAN to restore datafiles from a point in the past using the until clause of the restore command in one of the following ways:
A) Until SCN
If you know the SCN in a backup piece that you want to restore from
RMAN> restore database until SCN 5636176;
B) Until sequence
If you know the log sequence number that you want to restore up to
RMAN> restore database until sequence 17;
C) Until restore point
If you’ve created restore points, then you can also use the restore point name
RMAN> restore database until restore point WEEKLY_BACK;
D) Until time
You can also specify a point in time from which you want RMAN to restore an older backup.
RMAN> restore database until time ‘sysdate – 5′;
RMAN> restore database until time “to_date(’08-jan-2016 13:00:00′, dd-mon-rrrr hh26:mi:ss’)”;
ii) Using backup control file
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
You are required to open your database with the open resetlogs command anytime you use a backup control file during a recovery operation.
CASE 2: FEW BLOCKS IN DATAFILE ARE CORRUPT
There can be a corrupt block in a large datafile in your database which RMAN can easily report. Now it would take significant effort and time to perform the traditional restore and recover of a large datafile. So instead we will just recover the corrupt block and not the entire datafile.
For Oracle Database 10g or Oracle9i Database, use the blockrecover command to perform block media recovery.
As for Oracle Database 11g or newer, we will use the recover datafile … block command as shown below:
ONLY FOR TESTING FIRST WE CREATED SOME DB BLOCK CORRUTION
SELECT header_block FROM dba_segments WHERE segment_name=’apps';
HEADER_BLOCK
————
33
$ dd of=/u01/oracle/DB11G/oradata/apdba/users01.dbf bs=8192 conv=notrunc seek=15 << EOF
> corruption test
> EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 0.000159796 s, 100 kB/s
 ALTER SYSTEM FLUSH BUFFER_CACHE;
select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 15)
ORA-01110: data file 6: ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf’
A) CHECK CORRUPTION USING RMAN
RMAN will automatically detect corruption in blocks whenever a backup or backup validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view.
RMAN> backup validate database archivelog all;
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
6 FAILED 0 18 667 1086086
File Name: /u01/oracle/DB11G/oradata/apdba/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 90
Index 0 39
Other 1 693
In Alert Log:
Wed Feb 26 13:53:28 2016
Hex dump of (file 6, block 15) in trace file /u01/oracle/DB11G/diag/rdbms/apdba/apdba/trace/apdba_ora_6736.trc
Corrupt block relative dba: 0x01000093 (file 6, block 15)
Bad header found during validation
Data in bad block:
type: 99 format: 7 rdba: 0x69767075
last change scn: 0x7365.76206e6f seq: 0x76 flg: 0x0a
spare1: 0x72 spare2: 0x72 spare3: 0x0
consistency value in tail: 0x0eef0602
check value in block header: 0xb863
block checksum disabled
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
Reread of blocknum=15, file=/u01/oracle/DB11G/oradata/apdba/users01.dbf. found same corrupt data
In V$DATABASE_BLOCK_CORRUPTION view:
RMAN backup populates V$DATABASE_BLOCK_CORRUPTION.
 select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE#              CORRUPTIO
———- ———- ———- —————— ———
6         15    1             0                         CORRUPT
Using DBV:
dbv file=/u01/oracle/DB11G/oradata/apdba/users01.dbf blocksize=8192
B) CORRECT DATA BLOCK CORRUPTION
Database can be in mounted or open when you are performing block-level recovery. Also you do not have to take the datafile being recovered offline. Block-level media recovery allows you to keep your database available hence reducing the mean time to recovery since only the corrupt blocks are offline during the recovery.
RMAN CAN NOT perform block-level recovery on block 1 (datafile header) of the datafile.
RMAN> recover datafile 6 block 15;
C) VERIFY
select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
CASE 3: ONLY ONE DATA FILE IS LOST
You have one datafile that has experienced media failure. You don’t want to restore and recover the entire database or all datafiles associated with the tablespace. You just want to restore and recover the datafile that experienced media failure.
In this example we lost /u01/oracle/DB11G/oradata/apdba/users01.dbf
SCENARIO 1: DATABASE IS NOT OPEN
Here you restore/recover datafile from backup
A) CHECK IF YOU HAVE BACKUP PRESENT
RMAN> list backup;
B) MOUNT DATABASE
$ rman
RMAN> connect target /
RMAN> startup mount;
C) RESTORE DATAFILE
RMAN> restore datafile 6;
D) RECOVER DATAFILE
RMAN> recover datafile 6;
E) OPEN DATABASE
RMAN> alter database open;
SCENARIO 2 : DATABASE IS OPEN
Scenario 2 CASE 1: Datafile image copy from FRA
Here we will use datafile image copy from Flash Recovery Area (FRA), if it is enabled. This is FASTER WAY to recover a datafile.
A) check present files of the database:
RMAN> report schema;
B) Check image copies of the damaged datafile:
In our scenario datafile 6 is damaged.
RMAN> list copy of datafile 6;
C) Take ‘damaged’ datafile ‘offline’
RMAN> sql ‘alter database datafile 6 offline';
D) Switch to the copy of the file in the FRA
RMAN> switch datafile 6 to copy;
E) Recover the datafile copy and make it online
RMAN> recover datafile 6;
RMAN> sql ‘alter database datafile 6 online';
sql statement: alter database datafile 6 online
F) Check the datafiles once again:
RMAN> report schema;
AT THIS STAGE DATAFILE IS ONLINE AND DATABASE CAN BE USED NORMALLY.
When you bring the datafile back online, the tablespace will be brought online as well. The tablespace is now operational. But we don’t want to leave the database using a file in the flash recovery area, though, especially not for the long term.
BELOW STEPS ARE ADVISED TO BE DONE CAN BE DONE LATER ON (say weekend or off-business hours)
In below steps we will move back the data file to its original location.
G) Remove the ‘original damaged’ file at the OS level from the original location, if present
$ /u01/oracle/DB11G/oradata/apdba
$ mv users01.dbf users01.dbf.bkp
H) Create an image copy of the file & place it in the file’s original location
RMAN> backup as copy datafile 6 format ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
I) Take the datafile offline:
RMAN> sql ‘alter database datafile 6 offline';
sql statement: alter database datafile 6 offline
J) Switch to the copy of the file
RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy “/u01/oracle/DB11G/oradata/apdba/users01.dbf”
K) Recover the datafile copy and make it online
RMAN> recover datafile 6;
RMAN> sql ‘alter database datafile 6 online’;
L) Check the datafiles once again:
RMAN> report schema;
RMAN> backup as copy datafile 6;
$ RMAN
RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline”;
sql statement: alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline
RMAN> restore datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
RMAN> recover datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
RMAN> sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” online”;
NOTE: When using the RMAN sql command, if there are single quote marks within the SQL statement, then you are required to use double quotes to enclose the entire SQL statement and then also use two single quote marks where you would ordinarily just use one quote mark.
If you want to put the restored datafile to new location, use below RMAN commands instead
RMAN>run
{
sql “alter database datafile ”/u01/oracle/DB11G/oradata/apdba/users01.dbf” offline”;
set newname for datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf’ to ‘/backups/users01.dbf';
restore datafile ‘/u01/oracle/DB11G/oradata/apdba/users01.dbf';
switch datafile all;
recover datafile ‘/backups/users01.dbf';
sql “alter database datafile ”/backups/users01.dbf” online”;
}
Remember that set command needs to be used inside a run block
 select file_name from dba_data_files;

FILE_NAME
——————————————————–
/u01/oracle/DB11G/oradata/apdba/users02.dbf
/u01/oracle/DB11G/oradata/apdba/system02.dbf
/u01/oracle/DB11G/oradata/apdba/undotbs02.dbf
CASE 6: ALL DATAFILES OF A TABLESPACE GOT CORUPTED/LOST
One of the tablespace is giving errors. the tablespace has multiple datafiles and all got corrupted.
If only one datafiles had error, we would have gone for datafile level recovery but since all datafiles associated with have corruption so it is better to go for tablespace level recovery .
Scenario 1: Database NOT Open
This solution works for any tablespace in your database (including system and undo tablesapce).
In this example, we are restoring the users tablespace:
There are two data files in this tablespace and both got corrupted somehow.
$ RMAN
RMAN> startup mount
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
Scenario 2: Database is Open
Sometimes it is not possible to take the database down. You can take a tablespace offline, restore, and recover it while your database is open. This works for any tablespace except the system and undo tablespaces.
This example takes users offline and then restores and recovers before bringing it back online:
Here also we got the same error in users tablesace as showin in scenario 1 forcing us to go for restore/recover of users tabespace.
$ RMAN
RMAN> SQL ‘ALTER TABLESPACE USERS OFFLINE IMMEDIATE';
RMAN> RESTORE TABLESPACE USERS;
RMAN> RECOVER TABLESPACE USERS;
RMAN> sql ‘alter tablespace users online';
CASE 5: SPFILE/PFILE LOST
Sometimes spfile gets accidentally deleted or you are not able to open database using current spfile
Scenario 1: Using a Recovery Catalog
We will start Oracle instance without parameter file for retrieval of spfile
$ RMAN
RMAN> startup nomount
RMAN> restore spfile;
RMAN> startup force;
Scenario 2: Not Using a Recovery Catalog
Here you must tell the DBID of database to RMAN process.
$ rman
RMAN> connect target /
RMAN> set dbid 8473653926;
RMAN> startup force nomount;
RMAN> restore spfile from ‘/backups/apdba/controlfile_apdba_c-8473653926-20160226-06′;
RMAN> startup force;
NOTE: If our spfile autobackup was in default location, we could have used below command:
RMAN> restore spfile from autobackup;

Gather Schema Statistics Oracle Applications

Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.
The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.
As a general rule, run Gather Schema Statistics under the following circumstances:
1.  After there has been a significant change in data in either content or volume.
2.  After importing data.
3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.
Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.
 The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).
How to run Gather Schema Statistics concurrent program:
1. Log on to Oracle Applications with
    Responsibility = System Administrator
2. Submit Request Window
    Navigate to: Concurrent > Requests
3. Query for the Gather Schema Statistics
4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database
5. Submit the Gather Schema Statistics program
Parameters :
------------------
Schema Name:  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas
Percent:  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100
Degree:  The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum ofparallel_max_servers and cpu_count.
Backup Flag:  NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.
Restart Request ID:  In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior
Gather Options:  GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default
Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options
Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.
How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :
When we submit Gather Schema Stats with Parameter  ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.
 select count(table_name)  from  dba_tables  where  last_analyzed  is  not null  and  owner= <custom_schema_name>;
Here you realize none of the tables in custom schema are analyzed.
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:  
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie  FND_ORACLE_USERID  and  FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
          from fnd_oracle_userid a,
         fnd_product_installations b
         where a.oracle_id = b.oracle_id
         order by sname;
Note : When custom schema are created the entry is not made in  FND_PRODUCT_INSTALLATIONS  and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schema as well.
GATHER SCHEMA STATS from Back-end
Use the following command to gather schema statistics:
exec fnd_stats.gather_schema_statistics(‘ONT’) < For a specific schema >
exec fnd_stats.gather_schema_statistics(‘ALL’) < For all schemas >
Use the following command for gathering statistics on a temporary table
(ie: temporary tablename = TEMP_tmp in schema ABC):
exec fnd_stats.gather_table_stats(‘ABC’,'TEMP_tmp’);
Note 1065813.1 How to Gather Statistics on Custom Schemas for Ebusiness Suite 11i and R12? (Doc ID 1065813.1)
Monitoring:
exec fnd_stats.ENABLE_SCHEMA_MONITORING (SCHEMA_NAME);

ADOP Online Patching in Oracle Apps R12.2

Online patching is supported by the capability of storing multiple application editions in the database, and the provision of a dual application tier file system. At any given point in time, one of these file systems is designated as run (part of the running system) and the other as patch (either being patched or awaiting the start of the next patching cycle).
For applying a patch in R12.2 you need to use adop and run through all below phases in sequence mentioned below.
1) adop phase=prepare
2) adop phase=apply patches=<patch_number1>,<patch_number2> workers=<number_of_worker>
3) adop phase=finalize workers=<number_of_worker> (called automatically)
4) adop phase=cutover workers=<number_of_worker>
5) adop phase=cleanup (called automatically)
OR
Running all phases in single command:
adop phase=prepare,apply,finalize,cutover,cleanup patches=<patch_number1>,<patch_number2>
DESCRIPTION OF EACH PHASE
1) PREPARE PHASE DETAILS
Used to start a new online patching cycle
How to execute:
A) Set the environment by executing (sourcing) the run file system environment file:
$ source <EBS install base>/EBSapps.env run
B) Verify envirionment
You can confirm that the environment is properly set by examining the relevant environment variables:
$ echo $FILE_EDITION
 run
$ echo $TWO_TASK
 dbSID
C) Download Patches
Download patches to be applied and place then in the $PATCH_TOP directory of your system. This directory is pre-created by the install in the non-editioned file system (fs_ne) and should not be changed.
Important: On a multi-node system with non-shared file systems, you must copy the patch files to each separate $PATCH_TOP directory, so that the patch files are available from the same location on all nodes.
D) Unzip the patch
$ unzip <patch>.zip
E) Run Prepare Command
Prepare the system for patching by running the following command to start a new patching cycle:
$ adop phase=prepare
 What it will do:
• Checks whether to perform a cleanup, which will be needed if the user failed to invoke cleanup after the cutover phase of a previous online patching cycle.
• Checks the integrity of the database data dictionary. If any corruption is found, adop exits with an error.
• Checks system configuration on each application tier node. A number of critical settings are validated to ensure that each application tier node is correctly registered, configured, and ready for patching.
• Checks for the existence of the “Online Patching In Progress” (ADZDPATCH) concurrent program. This program prevents certain predefined concurrent programs from being started, and as such needs to be active while a patching cycle is in progress (that is, while a database patch edition exists). If the ADZDPATCH program has not yet been requested to run, a request is submitted.
Note: ADZDPATCH is cancelled later on when the cutover phase is complete.
• Checks to see if the patch service has been created. adop requires that a special database service exists for the purpose of connecting to the patch edition. This service is created automatically, but its continued existence is validated on each prepare.
It can be checked by the database parameter SERVICE_NAME
SQL> show parameter service_name
NAME TYPE VALUE
 ------------------------------------ ----------- ---------------
service_names string dba, ebs_patch
Here dba is the SID of our database and ebs_patch is additional service_name which is required by online patching tool.
If you look at tnsnames.ora file in the Application tier $TNS_ADMIN directory you will find below kind of entry:
<SID>_patch=
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=<your_database_server_name>)(PORT=<your_database_port>))
 (CONNECT_DATA=
 (SERVICE_NAME=ebs_patch)
 (INSTANCE_NAME=<your_database_SID>)
 )
 )
During patching phase, adop will use this tns entry to connect to database.
• Invokes the TXK script $AD_TOP/patch/115/bin/txkADOPPreparePhaseSynchronize.pl to synchronize the patches which have been applied to the run APPL_TOP, but not the patch APPL_TOP.
• Checks the database for the existence of a patch edition, and creates one if it does not find one.
2) APPLY PHASE DETAILS
In the apply phase, adop applies the specified patches to the system. Patches are applied to the patch edition of the database and file system.
How to execute:
Example:
$ adop phase=apply patches=1234,7891 workers=8
Where 1234 and 7891 are the patch numbers
What it will do:
If a post-installation patch step mentions any tasks that need to be performed explicitly, where they are run from depends on the type of patching:
• In a normal online patching cycle, the steps should be executed from the patch file system after the apply phase.
• If the patch is being applied in hotpatch mode or downtime mode, the steps should be executed from the run file system after the apply phase.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3) FINALIZE PHASE DETAILS
The finalize phase will be executed while the application is still online. It is used to perform any remaining processing that is needed to ensure the system is ready for the fastest possible cutover.
Used to perform the final patching operations that can
How to execute:
$ adop phase=finalize
What it will do:
• Pre-compute DDL that needs to be run at cutover.
• Compile all invalid objects.
• Validate that the system is ready for cutover.
If finalize_mode=full, compute statistics for key data dictionary tables for improved
performance.
VERY IMPORTANT 1 : Up to this phase, you can run a special phase called abort, which will undo the changes made so far in the patching cycle. After cutover is complete, however, you cannot do this.
VERY IMPORTANT 2 : In an online patching cycle, the requisite JAR files are initially stored in the $APPL_TOP/admin/<SID>/out directory, and then uploaded into the database during the cutover phase. Therefore, the out directory must not be deleted at least until cutover (next phase) is complete.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4) CUTOVER PHASE DETAILS
Used to perform the transition to the patched environment. Shuts down application tier services, makes the patch edition the new run edition, and then restarts application tier services. This is the only phase the involves a brief downtime.
Important: No users should remain on the system during cutover, as there will be a short downtime period while the application tier services are restarted. Also, any third-party processes connected to the
old run edition of the database should be shut down, or they will be terminated automatically.
How to execute:
$ adop phase=cutover
What it will do:
• Shut down internal concurrent manager. The adop utility signals the internal concurrent manager to shut down, but will wait for any existing concurrent requests to finish before it proceeds with cutover actions.
Note: Cutover will take longer if it has to wait for long-running concurrent processes to complete. In such a case, you can expect to see an informational message of the form: [STATEMENT] [END 2013/10/28 23:47:16] Waiting for ICM to go downIf you do not want to wait for in-progress concurrent requests to finish normally, you can terminate the internal concurrent manager by executing the adcmctl.sh abort command from a different shell.
• Shut down application tier services: All application tier services are brought down. During this period, the system is unavailable to users.
• Cutover database: Promote patch database edition to become the new run database edition, using adzdpmgr.pl script.
• Cutover file system: Promote patch file system to become the new run file system, switching the $FILE_EDITION values in the patch and run enviroments. The current patch APPL_TOP becomes the new run APPL_TOP, and the current run APPL_TOP becomes the new patch APPL_TOP. Terminate old database sessions: Terminate any database connections to the old run edition of the database.
• Start application tier services: Application tier services are restarted, on the new run edition. The system is now available again to users
• ADZDPATCH concurrent program is cancelled when the cutover phase is complete.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
5) CLEANUP PHASE DETAILS
Important: If you fail to run the cleanup phase explicitly, it will be run automatically on the next prepare cycle, but this will cause a delay in starting your next online patching cycle.
How to execute:
$ adop phase=cleanup
What it will do:
• Various actions are performed during cleanup, including dropping (removing) obsolete: Crossedition triggers, Seed data, Editioned code objects (covered objects), Indexes, Columns, Editions.
Using parameter cleanup_mode:
a) cleanup_mode=quick – Performs minimum cleanup, including removal of obsolete crossedition triggers and seed data.
Use quick cleanup when you need to start the next patching cycle as soon as possible. For example, if you want to start a new patching cycle right away, but have not yet run cleanup from the previous patching cycle, you can use quick cleanup mode to complete the essential cleanup tasks as fast as possible.
b) cleanup_mode=standard – Does the same as quick mode, and also drops (removes) obsolete editioned code objects (covered objects).
This is the default mode , so does not need to be specified.
c) cleanup_mode=full – Performs maximum cleanup, which drops all obsolete code and data from earlier editions
Use full cleanup when you want to recover the maximum amount of space in the database. If you have run a large number of patching cycles, or applied a very large patch such as a rollup, significant space may be consumed by obsolete table columns and recovered by running a full cleanup. A full cleanup should only be performed when there is no immediate need to start a new patching cycle.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
THERE ARE TWO SPECIAL PHASES:
A) ABORT PHASE DETAILS
Abort PHASE is conditional phase. This phase cannot be specified with any other phase.
If for some reason either the prepare or apply phase failed or gave problems, you can abort the patching cycle at either of these points by running a special phase with the Command. The actions taken will be discarded (rollbacked).
IMPORTANT: This abort command is only available up to (but not including) the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle.
How to execute:
The command to perform this operation is:
$ adop phase=abort
What it will do:
• Confirms that there is an in-progress online patching cycle, so the abort call is therefore valid.
• Checks for the existence of a patch edition and drops one if it exists.
• Cancels the ADZDPATCH concurrent program, if it is running.
• Deletes the rows inserted for the pending session ID from the ad_adop_sessions and ad_adop_session_patches tables.
VERY IMPORTANT: After running abort, a full cleanup must be performed. The cleanup command is: adop phase=cleanup cleanup_mode=full). This will remove any columns that were added by the patch but are no longer needed because of the abort. If they are not removed, they may cause problems in a later patching cycle.
Alternatively, you can run a combined command to abort the patching cycle and perform a full cleanup:
$ adop phase=abort,cleanup cleanup_mode=full
If any attempt was made to apply patches to the patch edition, after abort you must run the fs_clone phase (adop phase=fs_clone) to recreate the patch file system.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
B) FS_CLONE PHASE DETAILS
The fs_clone phase is a command (not related to adcfgclone.pl) that is used to synchronize the patch file system with the run file system. The fs_clone phase should only be run when mentioned as part of a specific documented procedure.
How to execute:
The fs_clone phase is run using the following command:
$ adop phase=fs_clone
What it will do:
This phase is useful if the APPL_TOPs have become very unsynchronized (meaning that there would be a large number of delta patches to apply). It is a heavyweight process, taking a backup of the entire current patch APPL_TOP and then cloning the run APPL_TOP to create a new patch APPL_TOP. As this method requires more time and disk space, it should only be used when the state of the patch file system is unknown. This command must be invoked from the run file system, before the next prepare phase is run.
Note: The patch file system requires at least 25 GB of free disk space to be available for adop operations, including fs_clone. If there is insufficient free space, the adop operation will fail.
If an fs_clone operation fails, you can rerun it with the option force=yes to restart it from the beginning (with the same session ID), or force=no to restart it from the point where it failed.
IMPORTANT POINTS REGARDING ONLINE PATCHING:
1. adop utility is put under $APPL_TOP_NE/ad/bin. It is a wrapper script which calls internally the perl script $AD_TOP/bin/adzdoptl.pl which does actual work of applying the patch.
2. adop will automatically set its environment as required, but it is the user’s responsibility to set the environment correctly for any other commands that may be run. Set the run edition environment whenever executing commands that you intend to affect the run edition.
For example:
$ . <EBS_ROOT>/EBSapps.env run
 $ adstrtal.sh
Set the patch edition environment whenever you intend to execute commands that affect the patch edition.
For example:
$ . <EBS_ROOT>/EBSapps.env patch
 $ sqlplus apps/apps @my_custom_patch_script.sql
3. All the phases need to be completed and you can’t skip any of these. For example, if you try to skip prepare phase, you may get error message like “Apply phase can only be run while in a patching cycle, i.e. after prepare phase.”
4. After an online patching cycle is started, you should not perform any configuration changes in the run edition file system. Any that are made will not be propagated and will therefore be lost after cutover is complete.
5. You should not attempt to clone an Oracle E-Business Suite system while an online patching cycle is in progress.
6. The prepare, apply, and fs_clone phases all require at least 10GB of free disk space. All other phases require 1GB of free space. A warning message will be displayed if less than the needed amount is available.
7. The directories where you extracted the patches applied in a given patching cycle must be retained, in the same location and with the same contents, until the next prepare phase completes. This is also a requirement for patches applied in a hotpatch session.
8. Maintenance Mode is not needed for online patching, and so Maintenance Mode is not available in Oracle E-Business Suite Release 12.2.
ADOP ON MULTI-NODE
In a multi-node environment, one application tier node will be designated as the primary node. This is the node where the Admin Server is located, and will usually also be the node that runs Oracle HTTP Server. All other application tier nodes are designated as secondary nodes.
adop commands are invoked by a user on the primary node. Internally, adop uses Secure Shell (ssh) to automatically execute required patching actions on all secondary nodes. You must set up passwordless ssh connectivity from the primary node to all secondary nodes.
If a node unexpectedly becomes inaccessible via ssh, it will be abandoned by adop, and the appropriate further actions taken. Consider a scenario where the adop phase=prepare command is run in a system with ten application tier nodes. The command is successful on nine nodes, but fails on the tenth. In such a case, adop will identify the services enabled on nodes 1-9. If they are sufficient for Oracle E-Business Suite to continue to run normally, adop will mark node 10 as abandoned and then proceed with its patching actions. If they are not sufficient, adop will proceed no further.

R12.2 adop patching and Cloning issue and solution

Prerequisites before starting patching activity.
1. Check Patch top size ,filesystem free space  , it should be minimum ~ 26.5- 27 GB approx. free space, otherwise Prepare phase will fail
2. Check context_file and remove all custom entry below CUSTOM TOP
3. Check inventory which should point correctly
4. In case of DMZ instance(APDROD & APDTST)  download all patch on both internal and external (DMZ) node under PATCH_TOP (Double check patch on both nodes before apply phase)
Issue:
1. If prepare/apply phase/Cutover fails, check log under $ADOP_LOG_HOME/current_session_id
Find the issue and fix then trigger the adop cycle prepare/apply/cutover etc.
2. If patch is applied in internal node but failing in external (DMZ) node, THEN Crosscheck patch is downloaded in external node under PATCH_TOP. Check log in external node also for any issue. Download patch on both node and triggered apply phase.
3. If node is abandon during adop_phase, please check log for issue
Run fs_clone and start the prepare/apply phase
4. If node is abandon during Cutover and filesystem got interchanged in primary node but not in another node, check log for issue.
This is absolutely not recommended and will lead to ADOP corruption if not handled based on the scenario.
Take backup of ad_adop_session table before updating ad_adop_session table (strongly recommended)
Manual cutover after mocking values in ADOP tables
• Update ad_adop_session table set abandon flag=NULL
• Run fs_clone with option force=yes
• Run cutover manually by option allnodes=no

                       Cloning issue and solution
1. ADcfgclone is failed while creating FMW Home
Error:
==============
/d02/app/APDT122/fs2START: Creating FMW Home.
Running /d02/app/APDT122/fs2/EBSapps/comn/clone/FMW/pasteBinary.sh -javaHome /d02/app/APDT122/fs2/EBSapps/comn/util/jdk64 -al /d02/app/APDT122/fs2/EBSapps/comn/clone/FMW/FMW_Home.jar -tl /d02/app/APDT122/fs2/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl /d02/app/APDT122/fs2/inst/apps/APDTST_apdbasrv02/admin/log/clone/fmwT2PApply -silent true -debug true -executeSysPrereqs false
Script Executed in 28420 milliseconds, returning status 255
Script failed, exit code 255
Solution:  Make sure APPLTMP location should have atleast 10GB free space
Export T2P_JAVA_OPTIONS="Djava.io.tmpdir=/d02/app/APDT122/fs_ne/inst/APDTST_apdbasrv02/temp"
and ran the adcfgclone.pl
2. Adcfgclone got complete but at the end it failed for autoconfig with the below error.
Error :
=====================
WARNING: [AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>
               [PROFILE PHASE]
                AutoConfig could not successfully execute the following scripts:
Directory: /d02/app/APDT122/fs2/inst/apps/APDTST_apdbasrv02/admin/install
      adadmprf.sh             INSTE8_PRF
Solution:  work around
1. Take backup of ad_timestamps, ad_appl_tops
2. Remove the rows which correspond to current node (on which the script is failing)
           SQL> create table ad_timestamps_bkp as select * from ad_timestamps;
                     Table created.
             SQL> create table  ad_appl_tops_bkp as select * from  ad_appl_tops;
                      Table created.
          SQL>  select distinct substr(attribute, 1, instr(attribute, '*') - 1)
                         from ad_timestamps
                          where type in ('INSTANTIATED_CURRENT_VIEW_SNAPSHOT',                           'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT');
          SQL> delete ad_timestamps where type in ('INSTANTIATED_CURRENT_VIEW_SNAPSHOT',
                     'BACKFILLED_BUGS_IN_CURRENT_VIEW_SNAPSHOT');
          SQL>  delete ad_appl_tops where APPLICATIONS_SYSTEM_NAME='APDROD';
3. Run the autoconfig , it will complete successfully.
4. DMZ URL is not working
Solution: please confirm the port in DMZ and post clone steps for DMZ
          ii. Modify the HTTP port in the $CONTEXT_FILE of the external node and run autoconfig
               iii . Check SSL setup in the $CONTEXT_FILE and run autoconfig
              Issue:
External URL not up. Due to http port using wrong port#8002.
Processes in Instance: EBS_web_APDTST_OHS2
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+-----
EBS_web_APDTST                   | OHS                |   18624 | Alive    | 1412243724 |  1870960 |   0:24:01 | https:4445,https:10001,http:8002
Solution:
Manually modified port#8001 in httpd.conf since it is not changed by autoconfig.
[apapdtst@apdbadmz01 EBS_web_APDTST]$ pwd
/d02/app/APDT122/fs1/FMW_Home/webtier/instances/EBS_web_APDTST_OHS2/config/OHS/EBS_web_APDTST
[apapdtst@apdbadmz01 EBS_web_APDTST]$ grep "8001" *
httpd.conf:Listen 8001
[apapdtst@apdbadmz01 EBS_web_APDTST]$
Database Restore Issue:  
5. Database restore was failing because of space issue and not having apdficient space for mapping also.
Solution: As some of the mount point in APDTST has not enough space to restore all the datafiles from APDROD’s .
Remap mount points from source to target accordingly ,if any mount point still lacking space after mapping and adjusting all datafiles then triggered restore and move restored datafile to free mount point.
   ii. Once restore complete rename the datafiles from old to new location as below.
Rename datafile (ALTER DATABASE RENAME FILE '/old/location' TO '/new/location'; )

R12.2 DMZ issue unable to login and Solution

Issue-1 
DMZ RUN file system config file having Patch file system entry which may be happened because of copy of patch file system config file to RUN fs config file.
Solution: Manually edited and modified RUN fs config file and made changes according to RUN fs
(For More clarification manually compared config file and setups to APDBAPROD DMZ setup config files)
Issue-2 
In CONTEXT_FILE of RUN fs webPort was set to 8002
Solution: corrected web port to 8001
Issue-3 
In CONTEXT_FILE of RUN fs httplistenparameter was set to 8002
Solution: corrected httplistenparameter to 8001
Issue-4
http port was reflecting wrong in http config file
Solution: corrected http port to 8001
Processes in Instance: EBS_web_APDBATST_OHS6
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
EBS_web_APDBATST                   | OHS                |   20517 | Alive    | 1843739326 |  1805484 |   0:34:30 | https:4445,https:10001,http:8001
Issue-5-
mod_wl_ohs.conf file  having Patch fs entry which corrected as below according to correct RUN file setup for DMZ.
Solution:
Setup WebLogicCluster entry for DMZ
Issue-6-
In apps.conf file of RUN fs having Patch fs entry of apps.conf. oacorecluster URL was reflecting patch file entry
Solution:
Manually modified apps.config of RUN fs and added below entry
(Compared apps.config entry of APDBAPROD with current RUN fs apps.config for corrected modification and entry)
http://apdbadmz01.APDBA.com:7203/OA_HTML/media
http://apdbasrv02.APDBA.com:7201/OA_HTML/media
Note: Run autoconfig to reflect changes all changes

R12.2 SSO issues and solution

R12.2 adop phases and parameters

ADOP PHASES
1) prepare  - Starts a new patching cycle.
          Usage:  adop phase=prepare
 2) apply - Used to apply a patch to the patch file system (online mode)
         Usage:  adop phase= apply  patches = <>
    Optional parameters during apply phase
                --> input file : adop accepts parameters in a input file
              adop phase=apply input_file=
             Input file can contain the following parameter:
             workers=
              patches=:.drv, :.drv ...
             adop phase=apply input_file=input_file 
             patches
             phase
             patchtop
             merge
             defaultsfile
             abandon
             restart
             workers
Note : Always specify the full path to the input file
        --> restart  --  used to resume a failed patch
           adop phase=apply patches=<> restart=yes
       --> abandon  -- starts the failed patch from scratch
           adop phase=apply patches=<>  abandon=yes
       --> apply_mode 
             adop phase=apply patches=<>  apply_mode=downtime
         Use apply_mode=downtime to apply the patch in downtime mode ( in this case,patch is applied on run file system)
      --> apply=(yes/no)
        To run the patch test mode, specify apply = no
      --> analytics 
     adop phase=apply analytics=yes
            Specifying this option will cause adop to run the following scripts and generate the associated output files (reports):
   ADZDCMPED.sql - This script is used to display the differences between the run and patch editions, including new and changed objects.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop////adzdcmped.out.
    ADZDSHOWED.sql - This script is used to display the editions in the system.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowed.out.
    ADZDSHOWOBJS.sql - This script is used to display the summary of editioned objects per edition.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowobjs.out
    ADZDSHOWSM.sql - This script is used to display the status report for the seed data manager.
   The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop///adzdshowsm.out
3) Finalize :  Performs any final steps required to make the system ready for cutover..     invalid objects are compiled in this phase
    Usage: adop phase=finalize
   finalize_mode=(full|quick)   
 4) Cutover  : A new run file system is prepared from the existing patch file system.
   adop phase=cutover
   Optional parameters during cutover phase:
          -->mtrestart - With this parameter, cutover will complete without restarting the application tier services
       adop phase=cutover mtrestart=no  
   -->cm_wait -  Can be used  to specify how long to wait for existing concurrent processes to finish running before shutting down the Internal Concurrent Manager.
           By default, adop will wait indefinitely for in-progress concurrent requests to finish. 
 5) Cleanup    
 cleanup_mode=(full|standard|quick)  [default: standard]
6) FS_CLONE  : This phase syncs the patch file system with the run file system.
    Note : Prepare phase internally runs fs_clone if it is not run in the previous patching cycle
    Optional parameters during fs_clone phase:
  i ) force - To start a failed fs_clone from scratch
 adop phase=fs_clone force=yes  [default: no]
    ii ) Patch File System Backup Count ==> s_fs_backup_count  [default: 0 : No backup taken]
 Denotes the number of backups of the patch file system that are to be preserved by adop. The variable is used during the fs_clone phase,
 where the existing patch file system is backed up before it is recreated from the run file system.
7) Abort - used to abort the current patching cylce.
   abort can be run only before the cutover phase
    adop phase=abort  

E-BUSINESS SUITE UPGRADE R11i TO R12.2.4

Contents
E-BUSINESS SUITE UPGRADE R11I TO R12.2.4 1
Document Control 2
Change Record 2
Reviewers 2
Distribution 2
Contents 3
Points to Remember: 6
Segregation of Tasks 7
First Step: - DBA Team 7
Second Step: - Apps DBA Team 7
Third Step: - Functional Team 7
Fourth Step: - Apps DBA Team 7
11i - Pre-Upgrade Patches 8
Interactive Patches - OPM Functional Team 14
Init.ora updates 16
Build Staging Area 18
Installation Screenshots 21
Upgrade File 34
11.2.0.4 DB Patches 36
Apply Lessons Learnt from Previous Iterations 44
Apply Resolution from Sandbox Iteration 44
Apply Localization Patches - After Functional Configs 46
R12.2.0 Upgrade Patches 55
DB Init. ora Parameter changes 63
Execute Auto Config on DB Tier 64
Execute Rapidwiz on the Apps Tier 66
Post R12.2.0 Techstack Patches 72
Apply R12.2.4 Pre-Upgrade TechStack Patches 72
ORACLE_HOME=10.1.2.3 72
ORACLE_HOME=FMW_Webtier 72
ORACLE_HOME=FMW_ORACLE_COMMON 73
WebLogic Server 73
Additional DB Patches 75
DB Additional Patches for Conflicts 79
Steps to upgrade Oracle Applications to R12.2.4 82
AD and TXK Delta 6 Patches 85
R12.2.4 Upgrade 87
Post R12.2.4 Steps 89
Post R12.2.4 Patches 90
New Products Enabled 91
Custom Tops 96
Create Custom Tops 96
Copy Files 97
Create softlinks 97
Copy concurrent request logs and out files from 11i to R12.2.4 98
Copy Java files 99
Custom sftp programs 100
Datafix 101
GL_CODE_COMBINATIONS fix (/patch/erp/SCALE) 101
Appendix A - DB Components 102
DB components in EBSREF1 (11i) 102
DB components in EBSSTR1 (R12.2.4) 102
Appendix B - Tablespace Size Before and After Upgrade 103
Table space size before upgrade in EBSREF1: 103
Table space size before upgrade in EBSSTR1: 104
Appendix C - Product Tops - R11i & R12.2.4 105
R11i Product Tops 105
R12.2.4 Product Tops 110
Appendix D - Patch Timings 114
Appendix E - Object Counts - All and Invalids 117
Appendix F - Context Files 118
Context Files from R11i  - EBSREF1 118
Context Files from R12.2.4 - EBSSTR1 118
References 119
Oracle Support Notes 119
Oracle Support Service Requests 119
Open and Closed Issues 120
Open Issues 120
Closed Issues 120
Points to Remember:
1. This document was created with the following as the environment:
 DB and Application servers are on OEL 6.6 on commodity servers (non Exa)
 Rapid wiz install version is 12.2.0.49
 DB character set is UTF8
 DB is non-RAC
 DB version is 11g R2 (11.2.0.4)
 Shared APPL_TOP is not configured as of 03/11/15. The steps will be tested in Patch and migrated to Development. Steps will be added to the document upon completion of the task
 Archive logging was not disabled
 Daily nightly backups were not stopped
 Gather schema statistics was not scheduled / completed after the DB upgrade
 Database character set conversion exception report is being reviewed by APDBA teams as of 03/11/15. Once the decision on loss data has been made, the database will be converted from UTF8 to AL32UTF8
 Please implement the recommendations at the end of each patch before applying the patches