Migrating a MS SQL Server Database to Oracle Database 11g

Click here to see the steps for Migrating a Microsoft SQL Server Database to Oracle Database 11g

Oracle EBS Missing Responsibilities

Here are some of the MOS Notes which as further details about this issue.

How To Fix Missing Responsibilities For A User (Doc ID 429852.1 )
Missing Responsibilities after 12.1.3 Upgrade (Doc ID 1309852.1 )
Missing responsibilities in R12.1.3 (Doc ID 1423432.1 )
Missing Responsibilities of direct/indirect Responsibilities Assignment After Upgrade To 12.1.3 From 11i (Doc ID 1581955.1 )

Cleaning up the FND_NODES Table

Reference:

Note: 260887.1 – How to Clean Nonexistent Nodes or IP Addresses From FND_NODES

Why we need to clean FND_NODES table post cloning?

Because this table contains the IP Addresses of the source servers (apps and db) and when you clone your target servers have different IP Addresses, and this need to be populated in FND_NODES table.

Select the current value


set linesize 160
col NODE_NAME for a30
col SERVER_ADDRESS for a16
select SERVER_ADDRESS, node_name, node_mode, support_cp, support_web, support_admin,support_forms from FND_NODES;

From sqlplus execute the following command:


SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;


This deletes data from a few FND tables such as FND_NODES but after AutoConfig has been executed they will contain the correct values.

Run AutoConfig on Database and Application Servers.

Force all Oracle EBS users to change their password

Reference: Note: 414976.1 – How To Force All Applications Users To Change Their Password? We might have the requirement to force all EBS users to change their passwords due to some requirement. run Sql Script like below or same you can achieve by submitting concurrent request.

sqlplus -s APPS/apps @AFCPEXPIRE.sql

Force all Oracle EBS users to change their password

Reference:

Note: 414976.1 – How To Force All Applications Users To Change Their Password?

We might have the requirement to force all EBS users to change their passwords due to some requirement.

run sql Script like below or same you can achieve by submitting concurrent request

sqlplus -s APPS/apps; @AFCPEXPIRE.sql
or
Submit concurrent request: CP SQL*Plus Expire FND_USER Passwords


Above script sets the fnd_user.password_date to null for all users which causes all user passwords to expire. It can also be run as a SQL*Plus concurrent program.  The user will need to create a new password upon the next login.

Oracle DB Parameter remove

Hi DBA,

Below is the simple method to remove the parameter from spfile

-- Check parameter
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
*.optimizer_dynamic_sampling=2
orasmk@ebssitap:~$

-- remove parameter
alter system reset "optimizer_dynamic_sampling" scope=spfile sid='*';
alter system reset "__db_cache_size" scope=spfile sid='EBSSMK';


-- check in spfile
orasmk@ebssitap:~$ strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | grep optimizer_dynamic_sampling
orasmk@ebssitap:~$


Below is the grep command to check the parameters in spfile

strings $ORACLE_HOME/dbs/spfileEBSSMK.ora | egrep '_always_anti_join|_always_semi_join|_complex_view_merging|_index_join_enabled|_kks_use_mutex_pin|_new_initial_join_orders|_optimizer_cost_based_transformation|_optimizer_cost_model|_optimizer_mode_force|_optimizer_undo_changes|_or_expand_nvl_predicate|_ordered_nested_loop|_push_join_predicate|_push_join_union_view|_shared_pool_reserved_min_alloc|_sortmerge_inequality_join_off|_sqlexec_progression_cost|_table_scan_cost_plus_one|_unnest_subquery|_use_column_stats_for_function|always_anti_join|always_semi_join|background_dump_dest|core_dump_dest|db_block_buffers|db_cache_size|db_file_multiblock_read_count|DRS_START|enqueue_resources|event|hash_area_size|java_pool_size|job_queue_interval|large_pool_size|max_enabled_roles|nls_language|optimizer_dynamic_sampling|optimizer_features_enable|optimizer_index_caching|optimizer_index_cost_adj|optimizer_max_permutations|optimizer_mode|optimizer_percent_parallel|parallel_instance_group|instance_groups|plsql_compiler_flags|plsql_native_library_dir|plsql_native_library_subdir_count|plsql_optimize_level|query_rewrite_enabled|rollback_segments|row_locking|sort_area_size|sql_trace|SQL_VERSION|timed_statistics|undo_retention|undo_suppress_errors|user_dump_dest'