How to Remove OID References

Steps to Remove OID References

cd $FND_TOP/bin

$ perl txkrun.pl -script=SetSSOReg -removereferences=yes

You are removing all SSO-OID related preferences.

Enter Oracle E-Business apps database user password ?

*** Log File = /data/inst/apps/clone_test/logs/appl/rgf/TX

Beginning input parameter validation for Removing Preferences.

Input parameter validation for Removing Preferences completed.

BEGIN REMOVING PREFERENCES:

Oracle Home Instance preferences removed successfully

Updated APPS_SSO profile with value SSWA successfully.

SSO Preferences Removed successfully.

Removing OID References Started.

Removing OID References Completed Successfully.

Custom DIT Preferences Removed Successfully.

REMOVING PREFERENCES COMPLETED.

End of /data/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkSetSSOR

DBMS_SQLPA object is Invalid

Issue:
DBMS_SQLPA object is Invalid.

Findings:
DBMS_SQLPA is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column.
That is why we get an error as SYS.plan_table does not have this column.

Solution:
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

Note: It is suggested to do this process in Odd hours.

RAC Server Rebooted Abnormally

There are many reasons of Server reboot.You need to analyze the log files to diagnose the root cause.

Issue
The Server test1(10.10.10.1) was abnormally rebooted on 26-02-2011.

Fact
Node 1 was evicted by node 2 because connections could not be made from node 2 to node 1.


Node 2 cssd.log
[ CSSD]2011-02-26 03:04:01.745 [10] >TRACE: clssgmClientConnectMsg: Connect from con(6000000000048870) proc(6000000000103df0) pid() proto(10:2:1:1)
[ CSSD]2011-02-26 03:04:32.370 [5] >TRACE: clssnm_skgxncheck: CSS daemon failed on node 0
[ CSSD]2011-02-26 03:04:32.371 [5] >TRACE: clssnmDiscHelper: node test1 (0) connection failed ========>>>>>>>>>>>> cannot connect to node test1
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmDoSyncUpdate: Initiating sync 13
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmDoSyncUpdate: diskTimeout set to (597000)ms
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmSetupAckWait: Ack message type (11)
[ CSSD]2011-02-26 03:04:32.707 [16] >TRACE: clssnmSetupAckWait: node(0) is ALIVE
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmSetupAckWait: node(1) is ALIVE
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmSendSync: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmWaitForAcks: Ack message type(11), ackCount(2)
[ CSSD]2011-02-26 03:04:32.708 [9] >TRACE: clssnmHandleSync: Acknowledging sync: src[1] srcName[poiddb02] seq[1] sync[13]
[ CSSD]2011-02-26 03:04:32.708 [16] >TRACE: clssnmWaitForAcks: node(0) is expiring, msg type(11)
[ CSSD]2011-02-26 03:04:32.708 [9] >TRACE: clssnmHandleSync: diskTimeout set to (597000)ms
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmWaitForAcks: done, msg type(11)
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmDoSyncUpdate: Terminating node 0, test1, misstime(21531) state(3)
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSetupAckWait: Ack message type (13)
[ CSSD]2011-02-26 03:04:32.709 [1] >USER: NMEVENT_SUSPEND [00][00][00][03]
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSetupAckWait: node(1) is ACTIVE
[ CSSD]2011-02-26 03:04:32.709 [16] >TRACE: clssnmSendVote: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.710 [16] >TRACE: clssnmWaitForAcks: Ack message type(13), ackCount(1)
[ CSSD]2011-02-26 03:04:32.710 [9] >TRACE: clssnmSendVoteInfo: node(1) syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.711 [16] >TRACE: clssnmWaitForAcks: done, msg type(13)
[ CSSD]2011-02-26 03:04:32.711 [16] >TRACE: clssnmCheckDskInfo: Checking disk info...
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmEvict: Start =====================>>>>>>>>>>>>>>>>>>>> Node 2 evicts node 1
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmWaitOnEvictions: Start
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmWaitOnEvictions: Node(0) down, LATS(2088370148),timeout(21643)
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSetupAckWait: Ack message type (15)
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSetupAckWait: node(1) is ACTIVE
[ CSSD]2011-02-26 03:04:32.712 [16] >TRACE: clssnmSendUpdate: syncSeqNo(13)
[ CSSD]2011-02-26 03:04:32.713 [16] >TRACE: clssnmWaitForAcks: Ack message type(15), ackCount(1)
[ CSSD]2011-02-26 03:04:32.713 [9] >TRACE: clssnmUpdateNodeState: node 0, state (0/0) unique (1293953635/1293953635) prevConuni(1293953635) birth (10/0) (old/new)
[ CSSD]2011-02-26 03:04:32.713 [9] >TRACE: clssnmDeactivateNode: node 0 (test1) left cluster


Findings
When two systems have access to the shared storage, integrity of the data depends on the systems communication through "HEARTBEATS" using the private interconnects. When the PRIVATE LINKS are LOST and FAILED,each system thinks the other system has exited the cluster, then it tries to become the master or form a sub-cluster and claim exclusive access to the shared storage.
To avoid such a tricky and undesirable situation,the basic approach is STOMITH(Shoot the Other Machine in the Head) fencing. In STOMITH systems, the errant cluster node is simply reset and forced to reboot.


Solution
Please check with System Admin for Network errors around the incident time.

ORA-00604 ORA-01555 ORA-06512

Error While Converting Dictionary Managed Tablespace to Locally Managed Tablespace

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME');

BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

Solution:
ORA-604 & ORA-1555 Rollback Segment 0 With Name "System" Too small (Doc ID 862469.1)
The SYSTEM rollback segment has no undo retention policy, and is usually not configured to be very large,it is possible to encounter a situation where we could run out of space and hence raise an ORA-1555 error.

set echo on
set feedback on
alter system set "_smu_debug_mode"=4;
drop table helper_for_1555;
create table helper_for_1555 (col1 varchar2(4000));

insert into helper_for_1555 values (rpad('a', 3999));
declare
a number;
begin
for a in 1..18 loop
insert /*+ APPEND +*/ into helper_for_1555 select * from helper_for_1555;
commit;
end loop;
end;
/

rem alter SYSTEM rbseg's storage params
alter rollback segment system storage (next 256M);
alter rollback segment system storage (optimal 1024M);
select segment_name, blocks, bytes, extents from  dba_segments where segment_type='ROLLBACK';

rem bloat the segment
alter system set "_in_memory_undo"=false;
set transaction use rollback segment SYSTEM;
delete from helper_for_1555;
rollback;

select segment_name, blocks, bytes, extents from dba_segments where segment_type='ROLLBACK';

alter system set "_smu_debug_mode"=0;
alter system set "_in_memory_undo"=true;

Now again try to convert the tablespace
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME');