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"/>