Tuesday, September 20, 2016

Enable or Register Oracle Database Vault

Enable Oracle Database Vault as follows:

UNIX: Run the following commands. The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.

 cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk dv_on lbac_on ioracle

 If you want to use the IPC protocol for Oracle Exadata storage, then use the following commands to enable Database Vault and Label Security.

 cd $ORACLE_HOME/rdbms/lib $ make –f ins_rdbms.mk dv_on lbac_on ipc_rds ioracle

Restart the database and listener once setup is done. 

Customize Oracle E-Business Suite R12 Login Page

A common request after Oracle Applications R12 implementation (or after the dust settles following an upgrade from 11i to R12) is to re-brand the Login page to include company's logo, and specific look and feel.

There are 5 areas on the login page that can be customized (see screen grab below)





To customize the above regions, login to Oracle Applications as sysadmin then assign 'Functional Administrator' responsibility.

Switch to Functional Administrator responsibility then navigate to "Personalization" tab, and in the "Document Path" field enter:
'/oracle/apps/fnd/sso/login/webui/MainLoginPG' then click "Go".  In the results table, click "Personalize Page" icon, ensure the "Site" checkbox is selected then Apply.  In the "Personalization Structure" page region, click "Expand All".  The page contains the list of objects marked in the screen grab above:
1. Image (Oracle logo) /OA_MEDIA/FNDSSCORP.gif
2. Image (people image) people.jpg
3. Image (above globe) topLines.gif
4. Image (globe) globalTop.jpg
5. Image (below globe) global.jpg

Above images are located in $OA_MEDIA directory.  To customize the login page, create own versions of the images, and save them in $OA_MEDIA directory with file names prefixed with client's initials, e.g. JAGFNDSSCORP.gif   To customize the regions, click the pencil icon in the corresponding region the "Personalize" column for the image.  Locate the "Image URI" attribute, then type the new image name in the "Site" column then Apply.  Repeat the process for the remaining images.

Wednesday, June 8, 2016

DB Upgrade from 11g to 12c(12.1.0.2) On R12.1.3

12c Upgrade from 11.1.0.7 on R12.1.3:


Interoperability Notes Oracle EBS Release 12.0 or 12.1 with Oracle Database 12c Release 1 (12.1.0.1) (Doc ID 1930134.1)—old note

Latest note:

Interoperability Notes
Oracle E-Business Suite Release 12.0 or 12.1
with Oracle Database 12c Release 1 (12.1.0)

February 2016

Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1 (Doc ID 1524398.1)
Section 1: Upgrading an R12 Database to Oracle Database 12c Release 1 (12.1.0)
Follow the instructions in this section if you plan to upgrade the database server and instance to the latest version of Oracle Database 12c Release 1 (12.1.0).
Before the Database Installation:
1.     Verify software versions
The following software component versions must exist in your environment.

oracle E-Business Suite
12.0.6 or 12.1.3
Application
If you are on E-Business Suite Release 12.0, apply
If you are on E-Business Suite Release 12.1, apply

2.Apply patch 6400501 (conditional)
If you are on a UNIX/Linux platform, apply the 10.1.0.5 version of Patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.

Note: For Linux x86-64 environments, download the Linux 32-bit version of the patch as the Applications tier Oracle home is 32-bit.
[tbdevmgr@tblnxapdev /apps/tbdevmgr/tech_st/10.1.2/OPatch/6400501]$ opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc

Inventory is good and does not have any dangling patches.


Updating inventory...

Verifying patch...
Backing up comps.xml ...


OPatch succeeded.


Install the 12.1.0 software:
Log in to the database server node as the owner of the Oracle RDBMS file system and database instance. Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create, and not for any existing Oracle homes on the database server node. Perform all the steps in Chapter 6, "Installing Oracle Database" of the Oracle Database Installation Guide 12c Release 1 (12.1) for your platform.
In the install windows, select the options to install the database software only. Select any languages other than American English that are used by your Applications database instance. Choose the Enterprise Edition installation type












Install example CD as well.

In



After install CD completed ,You have to follow the below steps to complete the Upgrade.


Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/nls/data/9idata directory.

tbdevora@tblnxdbdev TBDEV> /patch/raghus/12cupgrade_R12.1.3patches/new12sw]$ cd
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Directory /oracle/tbdev/orabin/11.1.0/nls/data/9idata already exist. Overwriting...
Copying files to /oracle/tbdev/orabin/11.1.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /oracle/tbdev/orabin/11.1.0/nls/data/9idata!
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ export ORACLE_HOME=/oracle/tbdev/orabin/12c
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ 11g to 12c 
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ export PATH=$ORACLE_HOME/bin:$PATH
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Creating directory /oracle/tbdev/orabin/12c/nls/data/9idata ...
Copying files to /oracle/tbdev/orabin/12c/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /oracle/tbdev/orabin/12c/nls/data/9idata!
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ export ORA_NLS10=/oracle/tbdev/orabin/12c/nls/data/9idata
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$
[tbdevora@tblnxdbdev TBDEV> /oracle/tbdev/orahome]$



[tbdevora@tblnxdbdev TBDEV> /patch/raghus/12cupgrade_R12.1.3patches]$ cd 19382851/
[tbdevora@tblnxdbdev TBDEV> /patch/raghus/12cupgrade_R12.1.3patches/19382851]$ ls
etc  files  README.html  README.txt
[tbdevora@tblnxdbdev TBDEV> /patch/raghus/12cupgrade_R12.1.3patches/19382851]$ vi README.txt
[tbdevora@tblnxdbdev TBDEV> /patch/raghus/12cupgrade_R12.1.3patches/19382851]$ opatch apply
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/tbdev/orabin/12c
Central Inventory : /oracle/tbdev/orabin/oraInventory
   from           : /oracle/tbdev/orabin/12c/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /oracle/tbdev/orabin/12c/cfgtoollogs/opatch/19382851_May_18_2016_14_03_49/apply2016-05-18_14-03-49PM_1.log

Applying interim patch '19382851' to OH '/oracle/tbdev/orabin/12c'
Verifying environment and performing prerequisite checks...
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/tbdev/orabin/12c')


Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 12.1.0.2.0...
Verifying the update...
Patch 19382851 successfully applied
Log file location: /oracle/tbdev/orabin/12c/cfgtoollogs/opatch/19382851_May_18_2016_14_03_49/apply2016-05-18_14-03-49PM_1.log

OPatch succeeded.

Shut down Applications server processes and database listener
On each application tier server node, shut down all server processes or services. On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home.

Note: The Applications will be unavailable to users until all remaining tasks in this section are completed.
Attention: Make sure that you do not have the LOCAL_LISTENER initialization parameter set to ensure that the database does not inadvertently point to a non-existent listener during upgrade.

Drop SYS.ENABLED$INDEXES (conditional)

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;


Remove the MGDSYS schema (conditional)

If you are upgrading from an RDBMS version prior to 12c, on the old database server node, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.
$ sqlplus “/ as sysdba” @?/md/admin/catnomgdidcode.sql

Prepare to upgrade:
SQL> @preupgrd.sql


Run DBUA from 12c ORACLE_HOME/bin:
Unset ORA_NLS10 and ORA_TZFILE to complete DBUA with out any error.else you may see the error like "ORA-01804 "failure to initialize timezone information".














DB Upgrade is completed.But You have to do some additional steps for ready the instance with your application.


Compile The Invalids:

sql> exec utl_recomp.recomp_parallel(25);

Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

$ sqlplus “/ as sysdba” @adgrants.sql (or adgrants_nt.sql)
[APPS schema name]

Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
    [SYSTEM password] CTXSYS

Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the following command:
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:
$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\
  [APPLSYS user] [APPS user]

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql 
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node.
Use SQL*Plus to connect to the database as APPS and run the following command:
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
Create appsutil.zip on application server node ,and copy that to new ORACLE_HOME which is 12c.

Unzip on the new ORACLE_HOME location.

Go to the ORACLE_HOME/bin
run perl adbldxml.pl for create the Context File for this new 12c DB Thena fter run autoconfig.

Create the new MGDSYS schema (conditional)
If you upgraded from an RDBMS version prior to 12c, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema.
$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

  1. Apply post-upgrade WMS patch (conditional)
    If you upgraded from an RDBMS version prior to 12c, apply Patch 14356466 for E-Business Suite Release 12.0 or Patch 18039691 for E-Business Suite Release 12.1
  2. Create Demantra privileges (conditional)
    If you are using Demantra, perform the steps in document 730883.1 on My Oracle Support.
  3. Re-create custom database links (conditional)
    If the Oracle Net listener in the 12.1.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:
    $ sqlplus apps/[apps password]
    SQL> select db_link from all_db_links;
    
    The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:
    $ sqlplus apps/[apps password]
    SQL> drop database link [custom database link];
    SQL> create database link [custom database link] connect to
         [user] identified by [password] using
         '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])
         (PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))';
    
    where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect the new Oracle Net listener for the database instance.
  4. Enable Database Vault (conditional)
    If you disabled Database Vault, enable it by performing step 7 of Part 2 of document 1091083.1 on My Oracle Support.
  5. Restart Applications server processes
    Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 12.1 Oracle home. Users may return to the system.
  6. Synchronize Workflow views
    Log on to Oracle E-Business Suite with the "System Administrator" responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:
    • Request Name = Workflow Directory Services User/Role Validation
    • Batch Size = 10000
    • Fix dangling users = Yes
    • Add missing user/role assignments = Yes
    • Update WHO columns in WF tables = No
    Click "OK" and "Submit".
RUN AUTO CONFIG ON Both Application Tier node.Your Application is ready with 12c DB.

Thursday, May 12, 2016

nid DBNAME change steps after RMAN recovery on R12.1.3 instance with 11g DB

DBNAME change using nid utility:

Once RMAN recovery is completed and DB has been open with PROD db name.now here we want to change to TARGET instance name.As I faced small confusion while using this utility.I would like to share those steps.

Here My TARGET DB is Up and Running as PROD name after using RMAN clone steps.

go to $ORACLE_HOME/dbs run " relink all".It will resolve any libray issues while executing "nid".

oracle/xxxxx/orabin/11.1.0/dbs]$ nid target=sys dbname=XXXRATE

DBNEWID: Release 11.1.0.7.0 - Production on Wed May 11 21:09:15 2016

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

Password:
Connected to database xxxxx (DBID=2579751613)

Connected to server version 11.1.0

Control Files in database:
    /oracle/xxxxx/oradata/cntrl01.dbf
    /oracle/xxxxx/oradata/cntrl02.dbf
    /oracle/xxxxx/oradata/cntrl03.dbf

Change database ID and database name xxxxx to XXXRATE? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2579751613 to 140640575
Changing database name from xxxxx to NJVCRATE
    Control File /oracle/xxxxx/oradata/cntrl01.dbf - modified
    Control File /oracle/xxxxx/oradata/cntrl02.dbf - modified
    Control File /oracle/xxxxx/oradata/cntrl03.dbf - modified
    Datafile /oracle/xxxxx/oradata/system01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/ctxd01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/owad01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/odm.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/olap.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apps_ts_tools01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/sysaux04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/undo11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/ctxd02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/odm1.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_summ02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_nolog02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_archive02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/njvcusers01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/undo12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/xxnjvc17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data15.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind12.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media18.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind13.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data16.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexebs01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexfile01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/apexuser01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind14.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data17.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system10.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system06.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/portal01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system07.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system09.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system08.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/system11.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_int01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_summ01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_nolog01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_archive01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_queue01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_media01.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_data03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind03.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind04.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_txn_ind05.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/a_ref02.db - dbid changed, wrote new name
    Datafile /oracle/xxxxx/oradata/temp12.db - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl01.dbf - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl02.dbf - dbid changed, wrote new name
    Control File /oracle/xxxxx/oradata/cntrl03.dbf - dbid changed, wrote new name
    Instance shut down

Database name changed to XXXRATE.
Modify parameter file and generate a new password file before restarting.
Database ID for database XXXRATE changed to 140640575.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.


2)Open pfile and change the dbname parameter from prod to target name and start the database.now the db has been started with target dbname but still datafile locations are not changed .

3)Take the control file backup on open Db which is target instance.

SQL> alter database backup control file to trace as '/tmp/control.sql';

Database altered.

Shutdown the Instance and take the the control files backup ,as we are going to recreate the control file again for change the data file locations.

4) Control File creation:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2166536 bytes
Variable Size             427819256 bytes
Database Buffers          624951296 bytes
Redo Buffers               14000128 bytes

SQL> @/tmp/control.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Now start the lsnrctl on target instance.Now your DB Instance is ready.
Clean the FND_NODEs and run auto config on DB node with production apps password.Then after Your DB is ready .

RMAN corrupt data file recovery

As we have situation like our production db files are corrupted more than 90%.Initially we thought of to go full recovery but then after we decided to go as table space level.

RMAN: Recovering from corrupteddatafile

Any kind of recovery needs a few very basic steps:
1) Checking the availability of valid backup
2) Deciding upon the recovery procedure
3) Actual recovery

When I know the table space name and datfiles are corrupted ,then check as below:

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

RMAN> restore datafile 4 preview;

RMAN> restore datafile 4 preview;

SQL> select * from v$log;
The Procedure Recovery can be complete or incomplete. A complete recovery has no data loss whereas an incomplete recovery has some data loss. An incomplete recovery is accompanied by RESETLOGS. An incomplete recovery is necessary when some or all of the data is unavailable to recover the object from its restore point to the point of dataloss. An object here could be a datafile, tablespace, or database. Restore point here is the point until where the DB has been restored to using the backup, and to make the DB current until the point of object loss we will need recovery to be performed. Recovery requires availability of archived and online redo logs. If the archived logs or redo logs are missing, then incomplete recovery is the only option. We may have to use RECOVER…UNTIL TIME option for point-in-time recovery of the missing object. (more on the point-in-time recovery in future posts).
In our case, since we have all the required archives we will perform a complete recovery. There is no data loss in this recovery procedure. The DB can remain in MOUNT or OPEN state during this procedure. Only the datafile will be made offline during the restore/recovery operation if the DB is in OPEN state.
The Recovery Itself Since all the backups are available, the actual recovery procedure is quite simple.
Connect to RMAN and issue the below statements:
RUN { SQL ‘ALTER TABLESPACE users OFFLINE IMMEDIATE’; RESTORE TABLESPACE users; RECOVER TABLESPACE users; SQL ‘ALTER TABLESPACE users ONLINE’; }
The snapshot of the same, in my DB, is below. In my snapshot, the ALTER TABLESPACE users ONLINE statement is missing. I wanted to confirm that the RECOVER TABLESPACE command runs successfully before bringing the tablespace online. I ran the ONLINE tablespace command later at the end. Also note that this RMAN command OFFLINE’s and ONLINE’s the entire tablespace. Since in our case this tablespace had only one datafile (which was missing), it wont much matter OFFLINE’ing the tablespace or datafile. If we had more than one datafile in this tablespace and one among those datafiles was missing or corrupt, we would OFFLINE only that datafile instead of the entire tablespace. The command is ‘ALTER DATABASE DATAFILE OFFLINE’.
Once you recover all the Tablespace has been recovered.System table space we can't take offline when the DB is in online.
Take the DB is Offline mode and startup in nomount state.
RUN {
RESTORE TABLESPACE SYSTEM;
RECOVER TABLESPACE  SYSTEM;
}
Once SYSTEM TS recovery is done.start the DB as "alter database open".
Note: Please make sure RMAN backup location and archive logs need to be accessible all the time where you want to recovery.

Monday, April 11, 2016

ADOP Concepts on R12.2 and above

Oracle Application 12.2 ADOP concepts





ADOP stands for AD Online Patching. From Release 12.2+ patching can only be performed by using adop.
You have to use adop instead of adpatch unlike previous release of EBS.

From 12.2+ onward each application node of apps will contain two file systems.

RUN file system: This is the actual file system where all the live applications run.

PATCH file system: Here is the actual concept of online patching coming in to picture with this file system.

Oracle recommend to download all application patches to PATCH_TOP ($APPL_TOP_NE/../patch) before you proceed with the actual patching activity.

adop is designed to support online patching activity with minimal downtime. To support that it is been divided into multiple phases. You can pass following phase with adop.

adop phase=prepare/apply/finalize/cutover/cleanup/actualize_all/abort

Prepare - This phase is used to sync run and patch file systems.

Usage: adop phase=prepare

Steps it will do - 1. Prepare the patch file system to ready for the patching
                          2. Create Patch Edition
                          3. Submit ADZDPATCH concurrent request to stop any conflict requests to submit from                                 patch edition.
                          4. Create session ID in ad_adop _sessions table with prepare_status as 'Y' at the end of                                     prepare.
Apply - Actual patching activity

Usage: adop phase=apply

Steps it will do - 1. Apply the specified patch to Patch File system.
                          2. Update the session ID created by prepare with apply_status as 'P' at the end of apply.
                          3. Insert entry in ad_adop_session_patches table with bug_number as the patch number.
Finalize - To prepare DB and APPS for cutover

Usage: adop phase=finalize

Cutover - This is where you need actual minimal down time where application services get stooped from RUN file system and start it from patch file system.

Usage: adop phase=cutover

Steps it will do - 1. Change the file system from PATCH to RUN
                          2. Change the Edition from PATCH to current
                          3. Do finalize if not run explicitly
                          4. Stop the ADZDPATCH concurrent request submitted during prepare
                          5. This will have the following status for cutover_status in ad_adop_sessions
                              'N' - 'NOT STARTED',
                               'R' - 'RUNNING',
                      'F' - 'FAILED',
                      'C' - 'COMPLETED',
                      'P' - 'ACTIVE',
                      'Y' - 'COMPLETED',
                      'X' - 'NOT APPLICABLE',
                      '0' - 'FORCE_SHUTDOWN_BEGIN',
                      '1' - 'FORCE_SHUTDOWN_COMPLETED_DB_CUTOVER_BEGIN',
                      '3' - 'DB_CUTOVER_COMPLETED_FLIP_SNAPSHOTS_BEGIN',
                      'D' - 'FLIP_SNAPSHOTS_COMPLETED_FS_CUTOVER_BEGIN',
                      '4' - 'FS_CUTOVER_COMPLETED_ADMIN_STARTUP_BEGIN',
                      '5' - 'ADMIN_STARTUP_COMPLETED_FORCE_STARTUP_BEGIN',
                      '6' - 'FORCE_STARTUP_COMPLETED'

Cleanup - Cleanup of the online patching stale objects that are created to support.

Usage: adop phase=cleanup

Actualize_all - Actualize all the uncovered objects (non active editioned objects).

Usage: adop phase=actualize_all

Abort - Delete the Patch Edition created in Prepare Phase

Usage: adop phase=abort