Turn off Archive Logs in 10g RAC Database

In the RAC environment, all instances have to be in shutdown, before “alter database noarchivelog;” can be executed successfully in the mounted instance. Otherwise, you will get ORA-1126 error “database must be mounted exclusive and not open for this operation”.

Steps:

1. Make sure LOG_ARCHIVE_START: set to FALSE, CLUSTER_DATABASE: set to FALSE
2. Shut down all instances
3. Mount the database, but not open, by an exclusive instance
4. Enter “alter database noarchivelog;”
5. Set LOG_ARCHIVE_START=FALSE for each of the other instances
6. Shut down the exclusive instance and reset its CLUSTER_DATABASE to TRUE
7. Restart all instances using the modified parameters

CLUSTER_DATABASE is an identical setting in RAC and has to be restored back to TRUE so that there will not be problems starting the other RAC instances. “alter system set cluster_database=false scope=spfile” before shutdown will do it, if SPFILE shows up on “show parameter spfile;” command in Sql*Plus.

We spent hours in truning off archivelog mode because we did not learn there were guaranteed restore points in the database. If there are records in view v$restore_point, you need to run a DROP command to delete the guaranteed restore point. If you do not do that, you will hit error:
ORA-38781: cannot disable media recovery – have guaranteed restore points
or “ARCHIVELOG mode cannot be turned off because a guaranteed restore point exists.” in Grid Control.

Once archive log mode is turned off, all instances in the RAC will in noarchive log mode!

Enable Remote SYSDBA Login

The key to log onto Oracle as SYS or SYSDBA from a remote client is by setting init.ora parameter REMOTE_LOGIN_PASSWORDFILE .

Steps:

Step 1: Set the init parameter, and then bounce the database:
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

EXCLUSIVE forces the password file to be tied exclusively to a single instance. In 10gR2 release, the value EXCLUSIVE is supported for backward compatibility and now has the same behavior as the value SHARED (Reference B14237-04).

Step 2: Create a passwor file:
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxxx

ORAPWD utility has an option “entries=”, which is not mandatory. I do not know what is the default number. In most cases, just do not use this option.

Step 3: Bounce the DB or update spfile
SQL> alter system set remote_login_passwordfile=none scope=spfile; –> if using spfile else shutdown and start the database

Step 4: Verify below view returns at least one row (username SYS):
SQL> select * from v$pwfile_users;

If yes, then everything works and the new passwordfile is in use by the username on the list. Now, SYSDBA can logon to the database from a remote client.

Additional notes:
1. If “grant sysdba to user_A;” is run by SYS, the user_A will show up in view v$pwfile_users. And user_A can login as SYSDBA useing its own password (not the one in passwordfile).
2. Without Step 1, Step 2 really does not do anything.
3. The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect as SYSDBA or SYSOPER from a remote machine. This parameter was deprecated in 11g and should not be used, it is for ‘normal’ users that use OS authentication (Note 50507.1).
4. If REMOTE_OS_AUTHENT is set to FALSE, OS_AUTHENT_PREFIX does not take any effect.

OPMN is failing to start. OPMN must be started in order to correctly install and configure the patchset

Recently we were upgrading Oracle E-Business Suite from 12.0.4 to 12.1.2 for which Web home needs to be upgraded from 10.1.3.0 to 10.1.3.5.

While running patch 8626084 (PLACEHOLDER BUG TO UPLOAD AS 10.1.3.5 PATCH-SET ON ARU) on IBM-AIX Power 64 System we have encountered an error stating “OPMN is failing to start. OPMN must be started in order to correctly install and configure the patchset“.

I was able to stat the OPMN services from backend but the oui was unable to recognise whether the services are up or now.

Then finally we have followed below steps which fixed the issue and we were able to upgrade the patchset to 10.1.3.5

1. Please confirm that patch 7359933 – contains the latest template changes for 10.1.3.5

2. Verify that upgrade to JDK 1.6 as per “Step 6 Upgrade Oracle E-Business Suite Release 12
JDK to Java 6.0 latest update has been performed.
– Reference Step 7 Upgrade Oracle E-Business Suite Release 12 JDK to Java 6.0 latest update (Recommended)
Oracle recommends you to upgrade Oracle E-Business Suite Release 12 Java with the latest Java 6.0 update.
Of Note Note 454811.1

3. Verify that the inventory is correct on the target instance. If any questions or concerns,
follow steps outlined in Metalink Note 742477.1 :
How to create or update the Central Inventory for Applications R12?

4. Review Section 7: Known Issues -> In Note 454811.1
and Appendix A: Additional IBM AIX-Specific 10.1.3.5 Patch Set Installation Instructions
related to AIX
Implement the suggested changes to $IAS_ORACLE_HOME/opmn/bin/opmctl as documented in the
README of patch 8461753.

5. Please perform the following:
a. Edit the $IAS_ORACLE_HOME/install/oc4jnames.txt: it should contain:
forms
oacore
b. Source the 10.1.3 ORACLE_HOME
# cd $INST_TOP/ora/10.1.3
# . ./_.env
c. Execute the following
# cd $IAS_ORACLE_HOME/install
# opmnctl status -app >ascontrol_status
# chmod u-w ascontrol_status

Reference the following notes:
Note 849738.1 R12 : Installation Issue with AIX OPMN Patch 10.1.3.4 Using Note : 454811.1
Note.864500.1 Patch 8461753 fails with 0403-057 Syntax error:

Kindly mail us on consult.appsdba@gmail.com if this help in fixing your issue.

– Farhan Shaikh

How to Export and Import Statistics

How to Export and Import Statistics of tables?

procedure with a scenerio

Case Definition

A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment. It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application. The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.
The production database has several clones; we decide to export back statistics from one of these clones, to the production database.

Steps in Brief

1) Create a table to hold statistics on the source database
2) Generate a script that export table statistics on the clone database
3) Generate a script that import statistics on the clone database
4) Export statistics on clone database
5) Export table containing the exported statistics from clone database
6) Ftp export file with clone statistics table, and the script to import statistics from clone server to production server
7) Import table containing clone statistics into production database
8) Import statistics on production server using the script to import statisctics generated on the clone server

1. Create tables to hold statistics on the clone database

— On Cloned Database

SQL> execute BMS_STATS.create_stat_table(‘OM’,’OLD_STATS’);

PL/SQL procedure successfully completed.

SQL> grant SELECT,INSERT,UPDATE,DELETE on OM.OLD_STATS to public;

Grant succeeded.

2. Generate a script that export table statistics on the clone database

The purpose of this script is to generate one export statistics command per table, the export is directed into the table created on step 1.

Variables:

&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

—- script to generate export table stats start here ———-

set linesize 130 pagesize 0
spool exportstats.sql
select ‘exec dbms_stats.export_table_stats(‘||chr(39)||owner||chr(39)||’,’||chr(39)||table_name||chr(39)||’,null,’||chr(39)||’&tabname’
||chr(39)||’,null,true,’||chr(39)||’INV’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off

—- script to generate export table stats end here ———-

Note: you may also use instead of the script this command:
exec DBMS_STATS.export_schema_stats(‘&usrname’,’&tabname’)
This syntax will run in 10g. It may fail on 8i – 9i databases with some objects. That’s why I prefer the script on these versions.

3. Generate a script that import statistics on the clone database
The purpose of this script is to generate one import statistics command per table, the source is the table created on step 1.
&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

—- script to generate import table stats start here ———-

set linesize 130 pagesize 0
spool importstats.sql
select ‘exec dbms_stats.import_table_stats(‘||chr(39)||owner||chr(39)||’,’||chr(39)||table_name||chr(39)||’,null,’||chr(
39)||’&tabname’||chr(39)||’,null,true,’||chr(39)||’&usrname’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off

—- script to generate import table stats end here ———-

Execute this script to generate impstats.sql that will import the statistics on the production database.

4. Export statistics on clone database

Using the script expstat.sql; generated on step 2, export statistics into the statistics table created on step 1.

5. Export table containing the exported statistics from clone database

vmractest:/oradisk/av/expstats>exp avargas file=exp_stats_from_clone tables=avr.old_stats feedback=1000
Export: Release 9.2.0.5.0 – Production on Tue Feb 20 11:57:02 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to AVR
. . exporting table OLD_STATS
….
4115 rows exported
Export terminated successfully without warnings.

6. Ftp export file with clone statistics table from clone server to production server and script to import statistics from clone server to production server
Execute FTP session from target server, get both the table that contains the exported statistics and the script to import them, generated on step :
proddb > ftp vmractest
Connected to vmractest
220 vmractest FTP server (SunOS 5.8) ready.
Name (vmractest:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> cd /oradisk/av/expstats
250 CWD command successful.
ftp> get exp_stats_from_clone.dmp
200 PORT command successful.
150 ASCII data connection for exp_stats_from_clone.dmp (10.5.180.72,64082) (473088 bytes).
226 ASCII Transfer complete.
local: exp_stats_from_clone.dmp remote: exp_stats_from_clone.dmp
478390 bytes received in 0.17 seconds (2680.69 Kbytes/s)
ftp> get impstats.sql
200 PORT command successful.
150 ASCII data connection for impstats.sql (10.5.180.72,64776) (31461 bytes).
226 ASCII Transfer complete.
local: impstats.sql remote: impstats.sql
31704 bytes received in 0.033 seconds (947.63 Kbytes/s)
ftp> bye
221 Goodbye.

7. Import table containing clone statistics into production database

On the production database import the table that contains the exported statistics.
proddb >imp avargas file= exp_stats_from_clone.dmp full =y
Import: Release 9.2.0.5.0 – Production on Tue Feb 20 12:19:11 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
export client uses IW8ISO8859P8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing AVARGAS’s objects into AVARGAS
. importing AVR’s objects into AVR
. . importing table “OLD_STATS” 4115 rows imported
Import terminated successfully without warnings.

8. Import statistics on production server using the script to import statistics generated on the clone server

Using the script impstats.sql; generated on step 3, import statistics into the production database.

Why should Apps & Applsys passwords always be the same?

Why should Apps & Applsys passwords always be the same?

The need to have the same password for Apps and Applsys is because when you sign on to apps, intially it connects to a public schema called APPLSYSPUB. This validates AOL username and password that we enter (operations/welcome using guest user account. Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to APPS schema.

Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login. Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc.). Applsys schema has applications technology layer products like FND and AD etc.

Oracle Database Upgrade from 9.2.0.x to 10.2.04

Hello,

In my today’s post we will  have discuss steps that i have followed to upgrade oracle database from 9.2.0.x to 10.2.0.4.0 for Oracle Application 11.5.10.2

Upgrade 9i Rel 9.2.0.x – 11.5.10.2 Oracle Applications database to 10gR2 10.2.0.4

Steps for upgrade

_ Check for pre requisites

_ Apply patches to current Oracle Applications instance

_ Install 10.2.0 base release in a new Oracle Home directory

_ Install Oracle Database 10g products from 10g Companion directory

_ Apply Oracle database patch 10.2.0.4

_ Create nls/data/9idata directory

_ Apply database patches to 10.2.0.4 home

_ Prepare for database upgrade

_ Upgrade database

_ Post Upgrade

_ Issues encountered during upgrade process

Check for pre- requisites

Edit /etc/sysctl.conf by adding below lines for kernel tuning

# Parameters as mentioned during 10gR2 Installation/Upgrade

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

# sysctl -p

Apply patches to current Oracle Applications instance

Upgrade the developer version to Developer 6i Patch Set 17 or higher

Apply patch 4653225 – 11.5.10 Interoperability patch for 10g release 4 to current application

Install 10.2.0 base release in a new Oracle Home directory

Download oracle 10.2.0 base release from

http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201linuxsoft.html

Select Enterprise Edition & uncheck create database and hit next

Hit next

Click on yes and hit next

Click on install

.

login to another session and execute root.sh under mentioned location

Click on exit and continue

Hit yes and proceed

Install Oracle Database 10g products from 10g Companion directory

Click on next

Select Oracle database 10g Products 10.2.0.1.0 and hit next

Verify the location and hit next

Click on next

Click on Install

Click on exit

Hit on yes

Apply Oracle database patch 10.2.0.4 p6810189_10204_Linux-x86.zip

Click on next

Verify the path on OH and hit next

Click on Next

Fill in proper info and hit Next

Click on Install

login to another session and execute root.sh under mentioned location

Click on Exit

Hit on yes … and 10.2.0.4 software upgrade is completed

Create nls/data/9idata directory

From 10.2.0 $ORACLE_HOME/nls/data/old directory, run perl cr9idata.pl

Modify oracle environment file so that the environment variable ORA_NLS10 points to $ORACLE_HOME/nls/data/9idata

directory.

[oradev@testapps old]$ perl cr9idata.pl

Creating directory /oradev/devdb/10.2.0/nls/data/9idata

Copying files to /oradev/devdb/10.2.0/nls/data/9idata…

Copy finished.

Please reset environment variable ORA_NLS10 to /oradev/devdb/10.2.0/nls/data/9idata!

Apply database patches to 10.2.0.4 home

Patch 5386204

Patch 5868257

Please refer the metalink note 555579.1 for issues during upgrade. (10.2.0.4 Patch Set – Availability and known Issues)

Please check this note#565600.1 (Very Important).

Prepare for database upgrade

Shutdown all application services

Shutdown database listener

Verify that 9i database is defined in /etc/oratab file

Upgrade database

During this stage the 9.2.0 database should be up and running but database listener should be down and

$ORACLE_HOME should point to 10.2.0; make sure /etc/oratab has proper entry pointing to 9.2.0 ORACLE_HOME.

# cd $ORACLE_HOME/bin

# ./dbua

Hit Next

Select the correct database to upgrade and click on next

Hit yes and continue

Hit Next

Make the changes as seen in the screenshot and continue

Hit next

Choose NOT to backup your database and hit next

Hit Next

Tick the check box if flash recovery needs to be enabled and hit next

Enter system password and hit next

Confirm database upgrade summary and hit finish to start the actual migration

Monitor the migration and make sure there are no errors

Hit on OK and continue

Hit on Close …. And verify Enterprise Manager Console

Post Upgrade

1 .Install Oracle Data Mining and OLAP

Verify that Oracle Data Mining and OLAP are installed in your database by using SQL*Plus to connect to the database

as SYSDBA and running the following command:

SQL> select comp_id from dba_registry where comp_id=’ODM’ or comp_id=’AMD’;

If the query does not return ODM, then you do not have Oracle Data Mining installed. To install Data Mining, use

SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return AMD, then you do not have OLAP installed. To install OLAP, use SQL*Plus to connect to

the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

2. Add 10gR2 release specific parameters to the init.ora file and remove obsolete parameters follow the doc 216205.1

Convert to System Managed Undo if not already configured.

3. Run changeperm.sh from $ORACLE_HOME/install.

4. Select comp_name,version,status from dba_registry;

In the out put ,status of all components should be Valid. If there is any invalid check the note 565600.1

5. Fix Korean lexers

Sqlplus “/ as sysdba” @$ORACLE_HOME/ctx/sample/script/drkorean.sql

6. If AD.I has been applied, then copy adgrants.sql script for $APPL_TOP/admin directory to

$ORACLE_HOME/appsutil/admin directory and run in sqlplus connected as sysdba.

# sqlplus “/as sysdba” @adgrants.sql applsys

7. Copy $AD_TOP/patch/115/sql/adctxprv.sql to $ORACLE_HOME/appsutil/admin directory and run in sqlplus as APPS

user.

# sqlplus apps/<pwd> @adctxprv.sql <system password> CTXSYS

8. Implement and run autoconfig.

Update the RDBMS ORACLE HOME file system with the AutoConfig files by performing the following steps:

On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

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

On the Database Tier (as the ORACLE user):Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

Run AutoConfig by executing: <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/

Note: If you are using an E-Business Suite release prior to 11.5.10 on a Unix platform and do not find adautocfg.sh at

the location specified above, then run adconfig.sh from <RDBMS ORACLE_HOME>/appsutil/bin to generate the

adautocfg.sh script.

9. Copy $APPL_TOP/admin/adstats.sql to $ORACLE_HOME/appsutil/admin directory. Shutdown database and startup in

restricted mode and run adstats.sql as sysdba. Then shutdown database again and startup in normal mode.(make sure

we have atleast 1.5GB)

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

10. Start the new database listener.

11. Run adadmin and run program “recreate grants and synonyms”.

12. Apply Oracle Receivables Patch 5753621

13. Startup application services and test login.

Issues Encountered During Upgrade Process

A. Autoconfig failed with below error on Admin Node

[APPLY PHASE]

AutoConfig could not successfully execute the following scripts:

Directory: /appldev/devora/iAS/Apache/perl/bin/perl -I /appldev/devora/iAS/Apache/perl/lib/5.00503 -I

/appldev/devora/iAS/Apache/perl/lib/site_perl/5.005 -I /appldev/devappl/au/11.5.0/perl

/appldev/devcomn/admin/install/DEV_testapps

adadmat.pl INSTE8_APPLY 256

txkGenExtSecConf.pl INSTE8_APPLY 1

AutoConfig is exiting with status 35

Fix:(372714.1)

1. Manually run jtfiappr.sql

2. Run jtfiaibu.sql

3. Run jtffmdel.sql

The required parameters are as follows:

1. jtfiappr.sql – Required parameters in order are — apps

2. jtfiaibu.sql – Requred parameters parameters in order are — jtf, jtf, apps

3. jtffmdel.sql – Does not require any parameters

4. Re-run Autoconfig.