uln_register "This system profile has already been registered"

Whenever cloned or previously registered machine would like to be registered again via uln_register to linux support , "This system profile has already been registered" may occur. These two possibilites of error can be avoided regenerate system uuid .

From ULN web interface related system information can be deleted from System tab. If you could not find any related system on list , following steps can be done.

With "uuidgen -r" command random id is generated ,with "uuidgen -t" generated id is based on system MAC address.For several versions of linux support , following actions can be done.

For Oracle Linux 4 and 5

Output of uuidgen -t is replaced the existing value of rhnuuid parameter of /etc/sysconfig/rhn/up2date-uuid file. up2date is run again.

For Oracle Linux 6 

Following command writes generated uuid into the related file which is "/etc/sysconfig/rhn/up2date"

echo "uuid=`uuidgen -t`" >> /etc/sysconfig/rhn/up2date

uln_register is run again.

ora-00942 resolution with errorstack trace

Whenever developer warned me that application took ORA-00942 -  table or view does not exist error , i have asked which sql . From one reason related sql could not be captured , so i have decided to open tracing for specific error on level 3.

For ora-00942 -  table or view does not exist error , i have executed ,

alter system set events '942 trace name errorstack level 3';

Error entries were in the alert log file , and it was showing related trace files. But more trace files occured in dump directory that i expect.Most of them were indicating 1 table , but this table was not the cause of error . I have used "grep" command to eliminate irrelevant files in order to detect actual table.

In every trace files there was a entry which is "about to signal 942" helped me to query all related files and "Name:" entry at the end of file showing table name.

grep "about to signal 942" * | grep "^Name:" * | grep -v "TABLE2"

Output ,

testdb_ora_23427.trc:Name: TABLEX

testdb_ora_2626.trc:Name: TABLEX

testdb_ora_2692.trc:Name: TABLEX

testdb_ora_2708.trc:Name: TABLEX

testdb_ora_29230.trc:Name: TABLEX

testdb_ora_31956.trc:Name: TABLEX

Table2 was irrelevant table and with "-v" parameter command only executed for other tables.As a result relevant table has been determined.

After resolution , you can disable errostack tracing,

alter system set events '942 trace name errorstack off';

lots of audit files under ASM home

Yesterday, I had as issue which was standby log shipping giving below error ,

PING[ARC2]: Heartbeat failed to connect to standby '<stbyDb>' Error is 1033.

On Standby server , db side was giving below error ,

NOTE: Deferred communication with ASM instance

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 28: No space left on device

ASM side was giving also,

OS Audit file could not be created; failing after 6 retries

Oracle support has a note about this issue which is "Large Number of Audit Files Generated by Oracle Restart or Grid Infrastructure (Doc ID 1378881.1)"  . Oracle restart deamons is checking availability of registered services within  a CHECK_INTERVAL parameter duration.Default it is 1 second. 

crsctl status res ora.asm -p command shows that checking frequency of asm service availability is 1 second. Modifying value of parameter can avoid execessive generation of audit files. 

crsctl modify res ora.asm -attr "CHECK_INTERVAL=60"

But this change will provide checking service availability every minute and unavailability will be detected one minute later in worst case.If it is acceptable , parameter can be changed. I have not changed parameter. I have solved issue by adding script into crontab which work everyhour and deletes ASM audit files

1 * * * * rm -rf /oracle/app/grid/product/12.1.0/grid/rdbms/audit/*.aud

Data Guard Physical Standby Creation

After setting up new database with ASM , customer also would like to have a physical standby database data guard configured.New version 12c may also have new properties for standby database creation , but i have used traditional methods. Following steps contain these methods ,

Primary Server  : PNODE

Standby Server : SNODE
Db Name : test

I assume that all servers have necessary Oracle binaries installed for ASM and Oracle database . Also ASM diskgroup(s) are ready as same as primary.

1. Primary database must be configured as force logging mode.This step is necessary for ensuring that all changes made in the database will be captured.

2.While primary db is online full backup is taken with following script

rman target /

run {
  allocate channel t1 DEVICE TYPE DISK;
  allocate channel t2 DEVICE TYPE DISK;
  allocate channel t3 DEVICE TYPE DISK;
  allocate channel t4 DEVICE TYPE DISK;
  backup
    as compressed backupset
    full
    format '/dump/stbyBackup/bkpstby-%s-%T-%U'
    (database);
  release channel t1;
  release channel t2;
  release channel t3;
  release channel t4;
}

Locations and directories should be changed according to your enviroments.

3. After backup is completed successfully, standby database controlfile is created on primary ,

alter database create standby controlfile as '/dump/stbyBackup/stbyCtl.ctl';

4. All backup files,standby controlfile,primary password file , primary spfile is copied to standby server into related directories.

5. Create initfile from spfile on standby server and change following parameters ,

sqlplus / as sysdba
create pfile='inittest.ora' from spfile='spfiletest.ora';

## Change controlfiles parameter showing that new standby controlfile (stbyCtl.ctl)

- control_files ='home/oracle/stbyCtl.ctl' ## This parameter should be changed for your locations.
- DB_UNIQUE_NAME=teststby
- LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,teststby)'
-log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=teststby'
- log_archive_dest_state_1='ENABLE'
- FAL_SERVER=test
- FAL_CLIENT=teststby
- STANDBY_FILE_MANAGEMENT =AUTO

6. You can recreate spfile from initfile

sqlplus / as sysdba
create spfile='spfiletest.ora' from pfile='inittest.ora';

7.Startup database in mount mode.

sqlplus / as sysdba
startup mount

8.Begin restore process ,

rman target /

run {
  allocate channel t1 DEVICE TYPE DISK;
  allocate channel t2 DEVICE TYPE DISK;
  allocate channel t3 DEVICE TYPE DISK;
  allocate channel t4 DEVICE TYPE DISK;
  restore database;
  release channel t1;
  release channel t2;
  release channel t3;
  release channel t4;
}

9. Create stanby redo log files on standby server. 

When you have created standby controlfile following warnings occured to create standby redolog files ,

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 524288000;

Size attribute depends on current redolog file size on primary server.

10. Add tnsnames entires into tnsnames.ora on standby server 

test = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=PNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))
            )

teststby= (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=SNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))

            )

11. For listener there should be already configured listener from grid home.

Until now, we have created standby database,configured tns and parameters.From this point, primary database parameters will be set and redo apply process will be started.

12. For primary database following parameters are set.

-   log_archive_config='DG_CONFIG=(test,teststby)'
- log_archive_dest_2='SERVICE=teststby LGWR async=20840 reopen=15 max_failure=10 optional net_timeout=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=teststby'
-  standby_file_management='AUTO'

13. Add teststby tnsnames entry into tnsnames.ora on primary server

teststby= (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=SNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))

            )

14. By tnsping from both servers connection is controlled.

tnsping test
tnsping teststby

Both commands must return "OK" ,

tnsping teststby >
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=teststby)(PORT=1521)) (CONNECT_DATA=(SID=test)))
OK (10 msec)

15. startup standby database on stanby server and log 

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; 

recovery operation starts and redo apply services apply logs to the standby database.

16. Enable log shipping on primary 

alter system set log_archive_dest_state=ENABLE;

Oracle Reports REP-0069: REP-57054: Error

When any concurrent requests with the execution method Oracle Reports are submitted, temp files are created under the path of 

$INST_TOP/logs/ora/10.1.2/reports/cache

These files are not purged with the "Purge Concurrent Request and/or Manager Data" program.These files can be deleted manually.

Sometimes, concurrent programs fail with following error message

"REP-0069: Internal error

REP-57054: In-process job terminated:Finished successfully but output is voided"

As workaround , setting cachesize parameter to value bigger than 0 can be used. But this workaround disables Cache cleanup functionality which is deletion of temp report file after Concurrent request completed successfully.So manual cleanup of cache will be needed.

According to Metalink Note : 1237834.1 permanent fix is as follow,

1. Apply Patch 14374587

2. Add the following property names and values into $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf

<property name="cacheSize" value="0"/>

<property name="noVoidedOutputError" value="yes"/>

With patch and setting these properties , accumulation of temp files are prevented.

3. But Autoconfig will overwrite above these changes when executed.To prevent this ,

a. Create custom directory under $FND_TOP/admin/template

    mkdir $FND_TOP/admin/template/custom

b. Copy $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to this new directory

    cp $FND_TOP/admin/template/rwbuilder_conf_1012.tmp $FND_TOP/admin/template/custom

c. Add same entries into rwbuilder_conf_1012.tmp as above

   <property name="cacheSize" value="0"/>

   <property name="noVoidedOutputError" value="yes"/>

Oracle EBS SOA Gateway testquery does not return response "TEST RESPONSE"

When i would like to detect that SOA gateway working properly, following URL is called and supposed to return "TEST RESPONSE" . If it is not returned there can be any error about ASADMIN user.Following check list can be checked.

http://server_name:port/webservices/SOAProvider/?testquery

1.Ensure that ASADMIN user is active and its password is set in $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/config/system-jazn-data.xml .

When i have seen this problem, i have checked file and reset password with "!" (exclamation mark) as clear text 

<credentials>!welcome</credentials>

After restarting oafm by adoafmctl.sh stop and start , my error has gone.

Also step 2 can be checked .

2. Following queries 

select role_name from wf_user_roles where user_name='ASADMIN'

select role_name from wf_user_role_assignments where user_name='ASADMIN'

should return following results

role_name

-----------

ASADMIN

UMX|APPS_SCHEMA_CONNECT

if any rows are missing 404 error could appear

For solution "Workflow Directory Services User/Role Validation" (FNDWFDSURV) concurrent program.From system administrator responsibility relate program can be executed with following parameters,

Batch Size : 10000

User Name : ASADMIN

Fix dangling users : Yes

Add missing user/role assignment : Yes

Update WHO Columns in WF Tables : No

registering Oracle linux to Unbreakable Linux Network and installing ASM

In order to install asm related packages for OEL 5.7  I have registered my system to  Unbreakable Linux Network (ULN). For OEL 5  up2date --register command is used. If you are using OEL 6 uln_register is used.

# up2date --register (OEL 5)

# uln_register (OEL 6)

Do not forget , you must have your server has access to internet to register itself on ULN and of course valid ULN account.

After typing up2date --register  you will be asked to enter your ULN uid , pwd and CSI 

The up2date will collect your system information  and this information will be used for system profile.

Then up2date will collect installed packages and associate then with related system.

On last screen collected information is sent to ULN to register.

Asmlib RPM is part of “Oracle Software for Oracle Linux” channel.System must be subscribed to related channel.After login  into ULN web site (linux.oracle.com) go to systems tab and select the system you will subscribe.  By using manage subscriptions you can select “Oracle Software for Oracle linux 5 (x86_64)” channel to save .Now you can download all asmlib software directly from ULN.

To install asm related softwares following command is executed for Oracle Linux 5 and older.

# up2date -i oracleasm-support oracleasmlib oracleasm-`uname -r`

if you have Oracle Linux 6

# yum install oracleasm-support oracleasmlib oracleasm-`uname -r`