RSS

Category Archives: Database

Data Objects in a data file in Oracle

How to know, find out data objects “Segments” in a given data file??

SELECT <distinct> a.segment_name,
 a.SEGMENT_TYPE,
 a.TABLESPACE_NAME,
 a.file_id,
 b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id
 AND b.file_id = <data file id>;

 

For the opposite purpose where you want to know in with data file a data object stored, you can use the following query:

SELECT a.segment_name, a.file_id, b.file_name Datafile_name
 FROM dba_extents a, dba_data_files b
 WHERE a.file_id = b.file_id AND a.segment_name = '<object_name>';
 
 

Tags: , , ,

Gaps Resolving in DataGuard Physical Standby Using RMAN Incremental Backup

1) defer log transfer from Primary Database
SQL>alter system set log_archive_dest_state_2=’DEFER’ scope=MEMORY;

2) record last sequence from Primary and Standby
SQL> archive log list;
or use sql:
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

3) record last SCN
SQL> select to_char(current_scn) from v$database;
e.g.:
Primary==> 5973814619607
Standby==> 5973624753925

you can also know when the gap happens by:
SQL>select scn_to_timestamp(5973624753925) from dual;

4) stop log apply on Standby
SQL>alter database recover managed standby database cancel;

5)Perform Rman Incremental backup from Primary database to backup the gap starting from Standby SCN
RMAN>run {
allocate channel c1 type disk format ‘/tmp/gapresolve/%U.rmb’;
backup incremental from scn 5973624753925 database;
}

6) Take a buckup from the current Primary Control file
SQL>alter database create standby controlfile as ‘/tmp/gapresolve_standby.ctl’;
7) transfer the backup spices and control file to Standby machine
$scp file user@machine:/directory
$scp /tmp/gapresolve/*.rmb oracle@sdbypc:/tmp/gapresolve/

8)start Standby db
SQL>startup nomount

9) allocate Standby database control file
SQL>show parameter control_files
==> /oracle_home/dbs/stdbycontrol.dbf

10)replace it with Primary backed up control file
$cp /tmp/gapresolve_standby.ctl /oracle_home/dbs/stdbycontrol.dbf
10) mount Standy database in standby state
SQL>alter database mount standby database;

11)start rman on standby
$rman target=/

12)catalog backup spices
RMAN>catalog start with ‘/tmp/gapresolve’;

13) recover the database
RMAN>recover database;

14) in some new created datafile need to be restored first, so a error massage came:
RMAN-03002: failure of recover command at 08/23/2014 12:41:19
RMAN-06094: datafile 61 must be restored
so just restore that file by:
RMAN>restore datafile 61;

15) rman recovery ends with and error that requesting current log file, we can ignore this error

16) on Standby database, check the current SCN
SQL> select to_char(current_scn) from v$database;

17) on Standby database, resume log apply
SQL> alter database recover managed standby database disconnect from session;

18) on Primary, resume log transfer
alter system set log_archive_dest_state_2=’ENABLE’ scope=MEMORY;

19)frequently check the scn
SQL> select to_char(current_scn) from v$database;

 
 

Tags: , , , , ,

Installing OBIEE 11.1.1.7.0 on MS windows 7

1         Main steps:

  • Install database “or connect exist certified database”.
  • Install “Create” Oracle Fusion Middleware Repertory.
  • Install OBIEE 11.1.1.7.0

 

2         Listed steps

2.1        Install loopback adaptor, to overcome any connectivity issue.

  1. Open Device Manager from Control Panel.
  2.  From Action menu choose “Add legacy hardware”
  3.  From “Add Hardware” wizard choose “Install the hardware that I manually select from a list”
  4. A list of common hardware choose ”Network adaptors”
  5.  Select “Microsoft” from Manufacturer list then choose “Microsoft Loopback Adaptor”
  6. Press Next to start driver installation, then press Finish to complete the installation.
  7.  Now, give an IP to the net adapter connection, from Control Panel > Network and Sharing Center, click on “Change adapter setting” link from the top left window side.
  8. Right-click on the  “Local Area Connection 2”, Choose “Prosperities” from the menu
  9.  Double-click on “Internet Protocol Version 4 (TCP/IPv4), supply the shown IP (10.10.10.10) and subnet mask (255.255.255.0) in proper fields, Then press “OK”, then “OK”
  10. To edit “hosts” file, Open “Notepad” program as administrator
  11. Locate “hosts” file in directory (C:\Windows\System32\drivers\etc)
  12. Uncomment localhost line, Add the highlighted line

“Assigned IP to loopback adapter <tab> computer name”

 

2.2        Intall Oracle Database 11.2.0.1 to be oracle fusion middleware repository database.

  1. Start the installation from “Setup.exe” file, in “Configure Security Updates”uncheck “I wish to receive…..”, Press Next.
  2. In “Select Installation Option”, choose “ create and configure a database”, press Next
  3.  In “System Class” choose “Server Class” and press Next.
  4.  In “Grid Installation Option” choose “Single instance database installation”, press Next
  5.  In “Select Installation Type” choose “Advanced Install”, Press Next.
  6.  Add “Arabic” language from “Available Languages” in “Select Product Languages” then press Next
  7.  In “Select Database edition” choose “Enterprise Edition”, Press Next.
  8.  In “Specify Installation Location” choose an empty directory e.g. (C:\oracle\db) to be the base directory where all related installation files will be under it.
  9.  In “Select Configuration Type” choose “General Purpose” <as we will use this database to be the fusion middleware repository so general purpose database will be sufficient>, Press Next.
  10. In “Specify Database Identifier” give a name for the database obtain “obidb” for Global database name and  SID, then Press Next
  11.  In “Specify Configuration options” specify the following;
  12.                   i.   In “Memory” tab check “Enable Automatic Memory Management” and reduce the amount to 2048 MB
  13.                 ii.   In “Character Sets” Tab,  Choose “Use Unicode (AL32UTF8)” option
  14.                iii.   In “Security” tab, uncheck “Assert all new security Settings”
  15.               iv.   In “Sample Schemas”, Check “Create database with sample schemas”
  16. In “Specify Management Options”, just press Next.
  17. In “Specify Database Storage Options”, just Press Next.
  18. In “Specify Recovery Options”, just Press Next.
  19. In “Specify Schema Password”, choose “Use the same password for all accounts, supply an alphabetic password of 8 character length: welcome1
  20. A warning message appears if you inter a weak password, press “Yes” to continue with your password anyway.
  21.  A prerequisite check will run, after pass it “Summary”  window will appear, Press “Finish” to start installation
  22. Before finishing the installation a “database Configuration Assistant” window appears to change schema passwords <if you want> just press OK. In ”Finish” window press Close.

2.3        Install RCU

  1. Start Repository Creation Utility from command line, start from directory <rcuHome>\BINà rcu.bat
  2. In “Welcome” window, press Next to start the installation
  3. In “Create Repository”, Choose “Create” then press Next.
  4. In “Database Connection Details”, first choose database type as (Oracle database), supply database hostname, port, service name and username and password of sys, Press Next.
  5. A checking prerequisites window appears, When finish press OK
  6. In “Select Component”, supply a prefix name for all component you will choose (DEV), Then choose the following component:Oracle AS Repository Componentsà AS Common SchemasàMetadata Services; the schema owner will be (DEV_MDS)
  7. Oracle AS Repository ComponentsàOracle Business Intelligenceà Business Intelligence Platform; the schema owner will be (DEV_BIPLATFORM)
  8. A checking prerequisites window appears, When finish press OK
  9. In “Schema Password”, Supply a password (welcome1) in “Use same password for all schemas” option, Then press Next
  10. In “Map Tablespaces” accept all given values and press Next
  11. In “Summary” window review all values and press Create.
  12. After creation completion press Close

2.4        Install OBIEE

  1. Start the installation wizard from setup.exe under bishiphome\Disk1 directory, A welcome screen appears, Press Next to start installation
  2. In “Install Software Updataes”, Choose “Skip Software Update” and press Next
  3. In “Select Installation Type”, select “Enterprise Install” option with install ORACLE_HOME and configure INSTANCE_HOME
  4. In “Create Or Scale Out”, Choose “Create New BI System”, Supply a password(welcome1) for weblogic user, accept other values
  5. In “Specify Installation Location”, Supply “Oracle Middleware Installation Location” which is equivalent to ORACLE_BASE as C:\oracle\MW, Then press Next
  6. If you use a FAT partition a warning message will appears, Press Yes to continue with installation
  7.  In “Configure Component” Choose all available component, then press Next
  8. In “BIPLATFORM Schema” give connection information to repository database, choose (Oracle Database) in Database type, write the connection string as hostname:prot:service_name, supply BIPLATFORM Schema username and password.
  9. In “MDS Schema” as pervious window choose (Oracle Database) in Database type, write the connection string as hostname:prot:service_name, supply MDS Schema username and password
  10. In “Configure Ports”, Choose “Auto Port Configuration” option then press Next
  11. In “Specify Security Update” uncheck “I wish to receive……” then press next.
  12. A confirmation message appears press Yes to continue.
  13. A summary window appears, review it and press Install
  14. While installation “Installation Progress” and “Configuration Progress” windows appears, which takes 20~30 minutes
  15. “Complete” window shows status.
 

Tags: , ,

Step by Step Installing and Configuring APEX on ORACLE Enterprise Edition (EE)

Step by Step Installing and Configuring APEX on ORACLE Enterprise Edition (EE)

 

Tags: , , ,

ORACLE-BASE – Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 5

ORACLE-BASE – Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 5.

 
Leave a comment

Posted by on May 29, 2013 in Action_Plan, Database

 

Tags: ,

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Main “steps” documents id:

362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1: Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07

Other doc_id: 881505.1, 1367654.1

Troubleshooting:

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

Contents

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1

Before the Database Installation and Upgrade. 3

I.       Interoperability “Pre”: 3

Database Upgrade: 4

II.      Pre-Export the database: 4

III.         Pre-Import the database: 6

IV-         Export the database: 9

V-     Import the database: 10

IV-         Post-Import and database configuration: 11

Before the Database Installation and Upgrade

       I.            Interoperability “Pre”:

From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)

1)   Verify S/W and system’s versions:

a)      Ebs 11.5.10.2

b)      ATG Rollup Patch 6

c)       Developer 6i Patch set 18

d)      AutoConfig Latest version with 9835302

2)   Nothing to do

3)   Nothing to do

4)   Deregister the current database server

To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:

$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl

appspass=[APPS db user password]

contextfile=$CONTEXT_FILE -removeserver

5)   Update application tier context file with new database listener port number 

If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.

s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL

6)   Upgrade the database instance

362205.1:   10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1:   Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2)  part no. E23633-07

According to previous documents the upgrade will be using import/export method.

Database Upgrade:

    II.            Pre-Export the database:

From doc_id: 362205.1 -> Section1: Prepare the source system

From doc_id: 557738.1 -> Section1: Prepare the source system

7)   Apply the Applications consolidated export/import utility patch

Apply both patches for exporting 10 (4872830) and 11g (1235353).

8)   Apply latest Applications database preparation scripts patch 

Apply Patch 7225862 to every application tier server node in the source system.

9)   Create a working directory on source database node

Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

10)       Generate target database instance creation script adcrdb.sql (10g),  aucrdb.sql (11g)

11g $sqlplus system/manager  @$AU_TOP/patch/115/sql/auclondb.sql 11

10g $sqlplus system/manager  @$AU_TOP/patch/115/sql/adclondb.sql

Then edit this script to reflect the new environment.

“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.

Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.

11)       Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql in the current directory.

12)       Remove rebuild index parameter in spatial indexes 

SQL> select * from dba_indexes where index_type=’DOMAIN’ and

upper (parameters) like ‘%REBUILD%’;

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters (‘parameters’)

Where [parameters] is the original parameter set without the rebuild_index parameter.

13)       Synchronize Text indexes

$ sqlplus ‘/ as sysdba’

SQL> select pnd_index_owner,pnd_index_name,count(*)

from ctxsys.ctx_pending

group by pnd_index_owner,pnd_index_name;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]’);

14)       Drop SYS.ENABLED$INDEXES (conditional) 

SQL> drop table sys.enabled$indexes;

15)       Shut down Applications server processes and database listener 

 III.            Pre-Import the database:

16)       Prepare to create the 11.2.0 Oracle home 

The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home

a) Finish OS prerequisites for new Oracle home “new host or exist host”.

b) Oracle OS owner profile option and environment variables.

17)       Install the base 11.2.0 software: install  software only

18)       Install Oracle Database 11g Products from the 11g Examples CD 

19)       Create nls/data/9idata directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

20)       Apply additional 11.2.0.1 RDBMS patches  “9 patches using opatch”

Append Opatch tool to PATH,

$export PATH=$PATH:$ORACLE_HOME/OPatch

and run the following patches:

8570322

8685327

8761974

8771297

12672969-> instead of 8772028

8796511

8897784

8964142

8405205

21)       Create working directory on target database server.

Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

22)       Copy scripts needed

Copy the following from Source environment “database or application servers” to the working directory on target database:

  1.         I.            Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
  2.       II.            Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
  3.     III.            Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
  4.     IV.            Copy auque2.sql generated file on step 18 from source database server working directory.
  5.       V.            Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
  6.     VI.            Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
  7.   VII.            Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
  8. Create appsutil.zip file

perl <AD_TOP>/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

Then copy it to new ORACLE_HOME then

unzip -o appsutil.zip

23)       Create the target database instance

Use modified adcrdb.sql script, generated from step 17

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool adcrdb.log;

SQL> startup nomount;

SQL> @adcrdb_db.sql

SQL> @adcrdb_tbs.sql

SQL> @ adpostcrdb.sql

SQL> exit;

24)     Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.

$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql

25)     Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system

$ sqlplus system/manager @/u01/expimp/ausy1120.sql

26)     Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.

$ sqlplus system/manager @/u01/expimp/aujv1120.sql

27)     Install other required components 

the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.

$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

28)     Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

29)     Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

30)       Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.

IV-             Export the database:

31)       Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

filesize=1G

full=y

logfile=full_expdpapps.log

32)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

33)       Shut down Applications server processes

34)       Grant privilege to source system schema

$ sqlplus / as sysdba

SQL> grant EXEMPT ACCESS POLICY to system;

35)            Export the Applications database instance

If the source database is 10.2.0 or 11.1.0, use the following command:

$ expdp system/[system password]

parfile=[export parameter file name]

If the source database is 11.2.0, use the following command:

$ expdp “‘/ as sysdba'” parfile=[export parameter file name]

36)       Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

V-                  Import the database:

37)       Create the import parameter file

Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:

  • Remove the exclude parameters.
  • Remove the filesize parameter.
  • Change the name of the log file.
  • Uncomment the transform parameter.

It will be like this:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

full=y

transform=oid:n

logfile=impdpapps.log

38)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

39)       Copy the export dump files from source to target

40)       Set Oracle Text parameter

if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);

41)            Import the Applications database instance

Start the import session on the target database server node using the customized import parameter file.

If the target database is 11.1.0, use the following command:

$ impdp system/manager parfile=[import parameter file name]

If the target database is 11.2.0, use the following command:

$ impdp “‘/ as sysdba'” parfile=[import parameter file name]

42)       Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> revoke EXEMPT ACCESS POLICY from system;

IV-             Post-Import and database configuration:

From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]

And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]

43)       Reset Advanced Queues 

Run file created in step:11.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

44)       Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

45)       Run adgrants.sql

$ sqlplus “/ as sysdba” @adgrants.sql APPS

46)       Grant create procedure privilege on CTXSYS

$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS

47)       Set CTXSYS parameter

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

48)       Deregister the current database server

$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

49)       Some errors will be shown in the autoconfig

  • To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
  • To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

  • enable applsys.wf_java_defered queue
  • some of grants may be lost so run the following list:
  • Compile the invalid objects.

$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql

50)       Generate your Database Context File

Ensure that shell reflect the oracle_home and bin.

From extracted appsutil.zip

cd <RDBMS ORACLE_HOME>/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps

answer the question to generate XML file.

51)       Generate and Apply AutoConfig Configuration files in DB tier.

Execute the following commands:

cd <RDBMS ORACLE_HOME>/appsutil/bin
adconfig.sh contextfile=XML file created in step 50

52)       Re-create custom database links
if the Oracle Net listener in the 11.2.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 dba_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.

Drop database link “PRODUCTION.HQ.ASQ.COM”;

create database link “PRODUCTION.HQ.ASQ.COM”

using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;

Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;

create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;

create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;

create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;

create database link “APPS_TO_APPS.HQ.ASQ.COM” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;

create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.LOCAL”;

create database link “APPS_TO_APPS.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.LOCAL”;

create database link “EDW_APPS_TO_WH.LOCAL” using ‘192.168.1.245:1521/UPGRD’;

53)       Generate and Apply AutoConfig Configuration files in APPS tier.

54)       Apply Oracle Human Resources (HRMS) patch 7721754

55)       Use adadmin to:

  • Compile invalid object
  • Re-create grants and synonyms using adadmin
  • Compile flexfield
 

Tags: , , , , , , ,

SQL to get profile option values list using specific criteria