Oracle Newbie DBA / Apps DBA

noreply@blogger.com (Mohammed Taj) — Fri, 07 Nov 2014 06:59:00 +0000

What To Do If A Concurrent Request Is Inactive With No Manager Status?

APPLIES TO:

Oracle Concurrent Processing - Version 11.5.10.0 to 12.2 [Release 11.5 to 12.2]
Information in this document applies to any platform.
Checked for relevance on 18-FEB-2014

GOAL

What to do if a concurrent request is Inactive with No Manager status?

SOLUTION

A concurrent request has a life cycle consisting of the following phases: Pending, Running, and Completed. During each phase, a request has a specific status. Listed below are the possible status for each phase:

Pending Phase - Normal, Standby, Scheduled, Waiting
Running Phase - Normal, Paused, Resuming, Terminating
Completed Phase - Normal, Error, Warning, Cancelled, Terminated
Inactive Phase - Disabled, On Hold, No Manager

If a concurrent request is on hold or unable to run when there are no active manager processes that can run the request, the request is placed in an Inactive phase.

Review the following points when the concurrent request is in Inactive phase with No Manager status.

1. Verify that Internal Concurrent Manager(ICM) is up and running. Use any one navigation mentioned below to check the status details of Internal Manager.

i. Oracle Applications Manager(OAM) > Site Map > Monitoring > Availability > Internal Concurrent Manager > View Status.

OR
ii. System Administrator Responsibility > Concurrent > Manager > Administer

2. Verify that there is at least one active concurrent manager with/without specialization rules that allow the concurrent program to run.

i. Run the following query to check whether any specialization rule defined for any concurrent manager that includes/excludes the concurrent program in question. Query returns 'no rows selected' when there are no Include/Exclude specialization rules of Program type for the given concurrent program.

select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='';

Note: Program Short Name is visible when the program is queried in concurrent program definition form.

Example:
SQL> select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='OKCRAQE';

SPECIALIZATION_RULE_DETAILS
-----------------------------------------------------------------------------
Concurrent program OKCRAQE is included in Contracts Core Concurrent Manager
Concurrent program OKCRAQE is excluded from Standard Manager
From the sample output above, it shows that the OKCRAQE(Listener for Events Queue) concurrent program has been excluded from the Standard Manager and included in Contracts Core Concurrent Manager. That means the concurrent request OKCRAQE can be run only by the Contracts Core Concurrent Manager which should be up and running to run and complete the OKCRAQE concurrent request.
Make sure that Concurrent Manager whose specialization rule includes the concurrent program is up and running.
ii. Ensure that standard concurrent manager is up and running.

Follow the below step only when you have confirmed the previous points and the issue is still remaining as there may be an issue with concurrent request queue view.

3. Manually re-create the concurrent request queue view for concurrent managers by entering the following command as an applmgr user at operating system prompt.

FNDLIBR FND FNDCPBWV apps/ SYSADMIN 'System Administrator' SYSADMIN

Successful completion of the above command shows the message "View created successfully" in the generated log file.

The concurrent request queue view is used internally to map requests to managers. This view would be regenerated when concurrent managers are created, or specialization rules are altered.

noreply@blogger.com (Mohammed Taj) — Fri, 07 Nov 2014 06:05:00 +0000

Receivables Activity Error: Please Define A Party Tax Profile For Operating Unit & ORG_ID

APPLIES TO:

Oracle Receivables - Version 12.0.0 and later
Information in this document applies to any platform.
ARXSUMRT.fmb

SYMPTOMS

Attempting to navigate to the Receivables Activities form to complete the setup and receive the following error:
Responsibility: Receivables Manager
Navigation: Setup > Receivables > Receivable Activities

Error: Please define a party tax profile for operating unit &ORG_ID
No record in ZX_PARTY_TAX_PROFILE

CAUSE

The Operating Unit owning Tax Content Profile was incorrectly setup

BUG 5899659 RECEIVABLES ACTIVITY ERROR PLEASE DEFINE PARTY TAX PROFILE FOR &ORGID

SOLUTION

To implement the solution, please execute the following steps:

Responsibility: Tax Managers
Navigation: Parties > Party Tax > Profiles

Query:
Party Type: Operating Unit Owning Tax Content
Party Name: Populate your Operating Unit Name
Click on GO
Click on Create Tax Profile and then
Click only Apply button without doing anything

You should have a result to this query.
If you do not, make sure you create a Party Tax Profile for your operating Unit of the Type: First Party Legal Entity.

Verify the each component of location address associated with your organization is valid

Restarting dead background process QMNC recorded in the alert.log file

noreply@blogger.com (Mohammed Taj) — Thu, 30 Oct 2014 14:20:00 +0000

Repeated : Restarting dead background process QMNC recorded in the alert.log file (Doc ID 395137.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.

SYMPTOMS

The following messages are recorded in the alert log every 5 minutes

Restarting dead background process QMNC

CHANGES

CAUSE

1. database startup :
The database has not opened for an undetermined reason, i.e there is no statement:
Completed: ALTER DATABASE OPEN
recorded in the alert.log file.
2. database shutdown :
A process may be preventing the database from shutting down
cleanly. As a consequence, this may be causing the qmnc process problems.

SOLUTION

This is expected behaviour.

The reason why the database has not opened or shutdown should be investigated.
These messages are a symptom of the problem and not the underlying cause.

noreply@blogger.com (Mohammed Taj) — Tue, 28 Oct 2014 15:03:00 +0000

R12 CE: Unable to view Legal Entities LoV’s in the bank account definition page

If you are experiencing difficulties in seeing all your required Legal Entities as a List of Values (LoV) within the bank account definition form in Oracle Cash Management, following these simple steps will rectify the issue and unlock the LoV. This process will also explain how to grant permissions to a specific role or responsibility based on bank account use and maintenance.

Login to Oracle Applications as sysadmin
Select the User Management Responsibility
Select the navigation path of ‘Roles and Role Inheritance’
Within the field ‘Type‘, select Roles and Responsibilities
Following step 4, a new field appears called ’Category‘, select ‘Miscellaneous’ as the value
Within the ‘Application‘ field, select ‘Cash Management‘. Note: Add the name of the role or responsibility if known
Click Go
Search for the role or responsibility that you intend to use to create bank accounts. Note: The permissions you assign during the rest of this procedure will be aligned to the choice you make here.
Click on the update icon against the desired role or responsibility
A new window (update Role) will be displayed. Click the ‘Security Wizards‘ button
Click Save and Proceed (if requested)
Click the ‘Run Wizard‘ icon against ‘CE UMX Security‘ row
Bank Account Security Management Window: In this window you can add all legal entities to which you wish to grant access and assign permissions based on the selected role or responsibility. Click the ‘Add Legal Entities‘ button and select any required legal entities that are not already listed.
Choose each permission that you wish to assign to the Legal Entity for the role or responsibility relating to bank accounts and further use within the applications. Choices include Use, Maintenance and Bank account Transfers. Note: Each permission is explained at the foot of this procedure.
Apply the changes and then verify in the Cash Management responsibility if you are now able to see the Legal Entities in bank account creation form.

Bank Account Grants: PermissionsUse: Allows the user to view the bank accounts for the selected legal entity in Cash Positioning, Cash Forecasting and Cash flows, as well as reconcile cash flows for the selected legal entity.
Bank Account Transfer: Allows the user to transfer funds from and to the bank accounts for the selected legal entity.
Maintenance: Allows the user to create and update bank accounts for the selected legal entity.

noreply@blogger.com (Mohammed Taj) — Tue, 28 Oct 2014 14:25:00 +0000

How to Prevent EXP-79 or EXP-80 Warnings (Data In Table Is Protected) During Export (Doc ID 277606.1)

APPLIES TO:

Enterprise Manager for Oracle Database - Version 8.1.7.4 and later
Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.4 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2013***

GOAL

The information in this article applies to:

Enterprise Manager for RDBMS - Version: 2.1 to 11g
Oracle Server - Enterprise Edition - Version: 8.1.7.0 and higher

Errors:

EXP-79
EXP-80

When exporting data from an Oracle8i, Oracle9i or Oracle10g database you see the following warning in the export log file (note that the table name is probably different):

About to export specified tables via Conventional Path ...
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only be exporting partial table.
. . exporting table TEST_TAB 1 rows exported
Export terminated successfully with warnings.

- OR -

About to export specified tables via Direct Path ...
EXP-00080: Data in table "TEST_TAB" is protected. Using conventional mode.
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only be exporting partial table.
. . exporting table TEST_TAB 1 rows exported
Export terminated successfully with warnings.

This document describes:

How to ensure that all table data has been exported
How to prevent these warnings during export.

SOLUTION

The warning is given because the table has a so called fine-grained access control policy (FGA) enabled. If fine-grained access control is enabled on a SELECT statement, then the Export may not export the entire table, because fine-grained access may rewrite the query.

Example (very simplified):

% sqlplus /nolog

SQL> CONNECT system/manager
SQL> GRANT execute_catalog_role, create procedure TO scott;

SQL> CONNECT scott/tiger
SQL> CREATE TABLE test_tab (first NUMBER);
SQL> INSERT INTO test_tab VALUES (1);
SQL> INSERT INTO test_tab VALUES (2);
SQL> COMMIT;
SQL> SELECT COUNT(*) FROM test_tab;

COUNT(*)
----------
            2

SQL> CREATE OR REPLACE FUNCTION test_func (schema_in VARCHAR2,
object_in VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'first = 1';
END;
/
SQL> EXECUTE DBMS_RLS.ADD_POLICY ('scott', 'test_tab', 'test_privs', -
'scott', 'test_func');

A select from table TEST_TAB will shows only one row:

SQL> SELECT COUNT(*) FROM test_tab;

COUNT(*)
----------
             1

Similar, a conventional path export (DIRECT=N or parameter DIRECT has not been specified), will export one row only. In addition, the following warning is given during export:

EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may
only be exporting partial table.

How to ensure that all table data has been exported?

Only the user SYS (all versions) or any user who has the EXEMPT ACCESS POLICY applied (Oracle9i and higher), can select all rows. To verify that the table has an enabled FGA Control policy:

SQL> CONNECT system/manager
SQL> SELECT enable,sel,object_owner||'.'||object_name "OBJECT_OWNER.NAME"
FROM dba_policies WHERE object_name='TEST_TAB';

ENA SEL OBJECT_OWNER.NAME
--- --- -----------------------
YES YES SCOTT.TEST_TAB

To verify that all table data has been exported, the following options are available.

1. Connect AS SYSDBA and check the number of rows in the table.

SQL> CONNECT / AS SYSDBA
SQL> SELECT COUNT(*) FROM scott.test_tab;

COUNT(*)
----------
             2

Compare this number with the number of exported rows in the export logfile. If different, not all rows have been exported due to the enabled FGA control policy. See below how to resolve this.

Or in Oracle9i and above:

2. Connect as a user who has the EXEMPT ACCESS POLICY privilege and check the number of rows in the table.

SQL> CONNECT system/manager
SQL> GRANT exempt access policy TO system;
SQL> SELECT COUNT(*) FROM scott.test_tab;

COUNT(*)
----------
             2

Compare this number with the number of exported rows in the export logfile. If different, not all rows have been exported due to the enabled FGA control policy. See below how to resolve this.

How to prevent the EXP-79 and EXP-80 warnings during an export?

1. Run a DIRECT path export (Oracle8i and above), while connected as a user who has the EXP_FULL_DATABASE role. Example:

SQL> SELECT grantee FROM dba_role_privs WHERE granted_role IN
(SELECT grantee FROM dba_role_privs
WHERE granted_role='EXP_FULL_DATABASE');

GRANTEE
----------------
SYS
SYSTEM
...

exp system/manager FILE=exp_tab.dmp LOG=exp_tab.log TABLES=scott.test_tab DIRECT=y

About to export specified tables via Direct Path ...
Current user changed to SCOTT
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully without warnings.

Note:
Rows in tables that contain objects and LOBs will be exported using conventional path method, even if direct path was specified. If this table has fine-grained access control policy (FGA) enabled, the export may still error with:

EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-28112: failed to execute policy function

In those cases, run the export while connected as a user who has the EXEMPT ACCESS POLICY enabled (only available in Oracle9i and above). See solution (3) mentioned below.

For details, see also:
"Parameter DIRECT: Conventional Path Export Versus Direct Path Export"

- OR -

2. Run the export while connected AS SYSDBA (Oracle8i and above).

Example:

exp '/ as sysdba' FILE=exp_tab.dmp LOG=exp_tab.log TABLES=scott.test_tab

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully without warnings.

For details, see:
"How to Connect AS SYSDBA when Using Export or Import"

- OR -
in Oracle9i and above:

3. Run the export while connected as a user who has the EXEMPT ACCESS POLICY (only available in Oracle9i and above).

Example:

SQL> CONNECT system/manager
SQL> GRANT exempt access policy TO scott;

exp scott/tiger FILE=exp_tab.dmp LOG=exp_tab.log TABLES=test_tab

About to export specified tables via Conventional Path ...
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only
be exporting partial table.
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully with warnings.

Note that although all rows have been exported, the EXP-79 warning is still given. This is caused by:
"Exempt Access Policy Privilege Not Properly Recognized By The Export Utility".

In this case, you can ignore the warning.

Remarks

Note that this document does not apply to the Oracle10g Export DataPump utility. An export with the new Oracle10g Export DataPump utility will not give any warning message.

For details, see:
"NO EXP-79 WARNING IF EXP WITH EXPORT DATAPUMP AND TABLE HAS FGA POLICY APPLIED"

References

"Exempt Access Policy Privilege Not Properly Recognized By The Export Utility"

"Parameter DIRECT: Conventional Path Export Versus Direct Path Export"
"All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS"
"How to Connect AS SYSDBA when Using Export or Import"

noreply@blogger.com (Mohammed Taj) — Sun, 26 Oct 2014 15:50:00 +0000

Receivables Activity Error: Please Define A Party Tax Profile For Operating Unit & ORG_ID

This is bug.: BUG 5899659 RECEIVABLES ACTIVITY ERROR PLEASE DEFINE PARTY TAX PROFILE FOR &ORGID

Responsibility: Tax Managers
Navigation: Parties > Party Tax > Profiles

Query:
Party Type: Operating Unit Owning Tax Content
Party Name: Populate your Operating Unit Name
Click on GO
Click on Create Tax Profile and then
Click only Apply button without doing anything

Data Guard Switchover Note

noreply@blogger.com (Mohammed Taj) — Sat, 18 Oct 2014 18:26:00 +0000

old primary site

SQL>select switchover_status from v$database;

quick procedure for switchover.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer;

old standby site

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

SQL>startup

old primary site

SQL>recover managed standby database disconnect

How to check GAP? (run at both side (primary/standby)

select status, gap_status

from v$archive_dest_status

where dest_id = 2;

how to check delay?

select delay_mins from v$archive_dest where dest_id = 2;

how to check switchover status from primary db?

select switchover_status from v$database;

how to verify active session at primary db?

select sid, process, program

from v$session where type = 'USER';

pre-req for switchover.

1. check gap between both side

2. check tempfile name should be same at both side

3. check delay between both side.

4. verify primary db can be switch to standby (switchover_status should return to standby)

5. safe side verify the active session at primary db

alter database commit to switchover to standby; (on primary db)

6.command to switchover (perform on standby db)

alter database commit to switchover to primary with session shutdown; ( acitve sesion is there)

alter database commit to switchover to primary; (without active session)

1. Verify the Physical Standby Database Is Performing Properly
Step 1 Identify the existing archived redo log files on strandby

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 2 Force a log switch to archive the current online redo log file on primary
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify that received redo has been applied on standby
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

The value of the APPLIED column for the most recently received log file will be
either IN-MEMORY or YES if that log file has been applied.

2. Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';

Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';

if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verify there are no large GAPS.
On primary
SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
On the standby the following query should be no more than 1-2 less than the primary query result
SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG val, V$DATABASE vdb
WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE#GROUP BY THREAD#;
If there is a gap .. fix the gap

Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
If there is no match then you will correct the temp tbs problem after switchover

Verify that there is no issue with V$LOG_HISTORY on the Standby (bug 6010833)
1. Get the Primary RESETLOGS_CHANGE#. On the primary execute:
SQL>select RESETLOGS_CHANGE# from V$DATABASE;

2. On the standby get the maximum sequence numbers for each thread from V$LOG_HISTORY:
SQL>select thread#, max(sequence#) from V$LOG_HISTORY where resetlogs_change#=1 group by thread#;

3. Get current sequence numbers on standby:
SQL>select thread#, max(sequence#) from v$archived_log alog, v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;

4. The last sequence# for each thread# from V$LOG_HISTORY should be close (the difference in log sequences < 3) to the last sequence# for each thread# from V$ARCHIVED_LOG.

5. If there is an issue with V$LOG_HISTORY then recreate the standby controlfile

Verify no old partial Standby Redo Logs on the Standby (bug 7159505)
1. Identify any active standby redo logs (SRL’s)
SQL>SELECT GROUP#, THREAD#, SEQUENCE# FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE' ORDER BY THREAD#,SEQUENCE#;

2.Identify maximum applied sequence number(s).
SQL> select thread#, max(sequence#)
from V$LOG_HISTORY
where resetlogs_change#=<>
group by thread#;

3.If there are any active SRL's that have a thread#/sequence# less than the thread#/sequence# returned from the V$LOG_HISTORY (meaning the recovery has progressed beyond the active SRL) query then clear them.
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE CLEAR LOGFILE GROUP ;

3 Switchover
Clear Potential Blocking Parameters & Jobs
1.Capture current job state on the primary
SQL>select * from dba_jobs_running;
SQL>select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';
SQL>show parameter job_queue_processes -- and capture the value

Block further job submission
SQL>alter system set job_queue_processes=0 scope=both;
SQL>execute dbms_scheduler.disable(job_name);

Disable any cron jobs that may interfere , rman backups
Shutdown all mid-tiers (e.g db console)
$ emctl stop dbconsole

Monitor Switchover
Turn on Data Guard tracing on primary and standby
Tracing is turned on to have diagnostic information available in case any issues arise.
SQL>show parameter log_archive_trace -- capture the value
Set Data Guard trace level to 8191
SQL>alter system set log_archive_trace=8191;
Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

Monitor alert log for both primary and standby

Finally Switchover
Verify that the primary database can be switched to the standby role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role

Switchover the primary to a standby database
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
in primary alert log you would see

Switchover: Complete - Database shutdown required (sfs_stby1)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN

Verify that the standby database can be switched to the primary role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Check if the standby has ever been open read-only
SQL> SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME='standby has been open';

If the standby was open read-only then restart the standby
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open the new primary database:
SQL> ALTER DATABASE OPEN;
Correct any tempfile mismatch

Restart the new standby
On the the new standby database (old production database), bring it to the mount state and start managed recovery.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post-Switchover Steps
For each database:
SQL>alter system set log_archive_trace=;
SQL>alter system set job_queue_processes= scope=both;
SQL>execute dbms_scheduler.enable();
Enable any cron jobs that were diabled in 3.1

Check that they are syncronized
Perfom log switch on new primary
Create cron job to backup new primary !!!

Reference
Metalink Note id : 751600.1

http://agstamy.blogspot.ae/2009/02/manually-switch-over-to-standby.html

Data Guard Switchover Notes

noreply@blogger.com (Mohammed Taj) — Sat, 18 Oct 2014 16:23:00 +0000

old primary site

SQL>select switchover_status from v$database;

quick procedure for switchover.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer;

old standby site

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

SQL>startup

old primary site

SQL>recover managed standby database disconnect

How to check GAP? (run at both side (primary/standby)

select status, gap_status

from v$archive_dest_status

where dest_id = 2;

how to check delay?

select delay_mins from v$archive_dest where dest_id = 2;

how to check switchover status from primary db?

select switchover_status from v$database;

how to verify active session at primary db?

select sid, process, program

from v$session where type = 'USER';

pre-req for switchover.

1. check gap between both side

2. check tempfile name should be same at both side

3. check delay between both side.

4. verify primary db can be switch to standby (switchover_status should return to standby)

5. safe side verify the active session at primary db

alter database commit to switchover to standby; (on primary db)

6.command to switchover (perform on standby db)

alter database commit to switchover to primary with session shutdown; ( acitve sesion is there)

alter database commit to switchover to primary; (without active session)

Manually switch over to the standby database

Manually switch over of dataguard configuration without dataguard broker

1. Verify the Physical Standby Database Is Performing Properly
Step 1 Identify the existing archived redo log files on strandby

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 2 Force a log switch to archive the current online redo log file on primary
SQL>ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify that received redo has been applied on standby
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

The value of the APPLIED column for the most recently received log file will be
either IN-MEMORY or YES if that log file has been applied.

2. Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';

Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';

if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verify there are no large GAPS.
On primary
SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
On the standby the following query should be no more than 1-2 less than the primary query result
SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$ARCHIVED_LOG val, V$DATABASE vdb
WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE#GROUP BY THREAD#;
If there is a gap .. fix the gap

Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
If there is no match then you will correct the temp tbs problem after switchover

Verify that there is no issue with V$LOG_HISTORY on the Standby (bug 6010833)
1. Get the Primary RESETLOGS_CHANGE#. On the primary execute:
SQL>select RESETLOGS_CHANGE# from V$DATABASE;

2. On the standby get the maximum sequence numbers for each thread from V$LOG_HISTORY:
SQL>select thread#, max(sequence#) from V$LOG_HISTORY where resetlogs_change#=1 group by thread#;

3. Get current sequence numbers on standby:
SQL>select thread#, max(sequence#) from v$archived_log alog, v$database db where alog.resetlogs_change#=db.resetlogs_change# group by thread#;

4. The last sequence# for each thread# from V$LOG_HISTORY should be close (the difference in log sequences < 3) to the last sequence# for each thread# from V$ARCHIVED_LOG.

5. If there is an issue with V$LOG_HISTORY then recreate the standby controlfile

Verify no old partial Standby Redo Logs on the Standby (bug 7159505)
1. Identify any active standby redo logs (SRL’s)
SQL>SELECT GROUP#, THREAD#, SEQUENCE# FROM V$STANDBY_LOG WHERE STATUS = 'ACTIVE' ORDER BY THREAD#,SEQUENCE#;

2.Identify maximum applied sequence number(s).
SQL> select thread#, max(sequence#)
from V$LOG_HISTORY
where resetlogs_change#=<>
group by thread#;

3.If there are any active SRL's that have a thread#/sequence# less than the thread#/sequence# returned from the V$LOG_HISTORY (meaning the recovery has progressed beyond the active SRL) query then clear them.
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE CLEAR LOGFILE GROUP ;

3 Switchover
Clear Potential Blocking Parameters & Jobs
1.Capture current job state on the primary
SQL>select * from dba_jobs_running;
SQL>select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';
SQL>show parameter job_queue_processes -- and capture the value

Block further job submission
SQL>alter system set job_queue_processes=0 scope=both;
SQL>execute dbms_scheduler.disable(job_name);

Disable any cron jobs that may interfere , rman backups
Shutdown all mid-tiers (e.g db console)
$ emctl stop dbconsole

Monitor Switchover
Turn on Data Guard tracing on primary and standby
Tracing is turned on to have diagnostic information available in case any issues arise.
SQL>show parameter log_archive_trace -- capture the value
Set Data Guard trace level to 8191
SQL>alter system set log_archive_trace=8191;
Trace output will appear under the destination pointed to by the database parameter BACKGROUND_DUMP_DEST with “mrp” in the file name.

Monitor alert log for both primary and standby

Finally Switchover
Verify that the primary database can be switched to the standby role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role

Switchover the primary to a standby database
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
in primary alert log you would see

Switchover: Complete - Database shutdown required (sfs_stby1)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN

Verify that the standby database can be switched to the primary role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Check if the standby has ever been open read-only
SQL> SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME='standby has been open';

If the standby was open read-only then restart the standby
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Open the new primary database:
SQL> ALTER DATABASE OPEN;
Correct any tempfile mismatch

Restart the new standby
On the the new standby database (old production database), bring it to the mount state and start managed recovery.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post-Switchover Steps
For each database:
SQL>alter system set log_archive_trace=;
SQL>alter system set job_queue_processes= scope=both;
SQL>execute dbms_scheduler.enable();
Enable any cron jobs that were diabled in 3.1

Check that they are syncronized
Perfom log switch on new primary
Create cron job to backup new primary !!!

Reference
Metalink Note id : 751600.1

Source: Google Search

Data Guard Switchover Notes

noreply@blogger.com (Mohammed Taj) — Sat, 18 Oct 2014 16:22:00 +0000

old primary site

SQL>select switchover_status from v$database;

quick procedure for switchover.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer;

old standby site

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

SQL>startup

old primary site

SQL>recover managed standby database disconnect

How to check GAP? (run at both side (primary/standby)

select status, gap_status

from v$archive_dest_status

where dest_id = 2;

how to check delay?

select delay_mins from v$archive_dest where dest_id = 2;

how to check switchover status from primary db?

select switchover_status from v$database;

how to verify active session at primary db?

select sid, process, program

from v$session where type = 'USER';

pre-req for switchover.

1. check gap between both side

2. check tempfile name should be same at both side

3. check delay between both side.

4. verify primary db can be switch to standby (switchover_status should return to standby)

5. safe side verify the active session at primary db

alter database commit to switchover to standby; (on primary db)

6.command to switchover (perform on standby db)

alter database commit to switchover to primary with session shutdown; ( acitve sesion is there)

alter database commit to switchover to primary; (without active session)

Manually switch over to the standby database

Source: Google Search

Data Guard Switchover Notes

noreply@blogger.com (Mohammed Taj) — Sat, 18 Oct 2014 16:22:00 +0000

old primary site

SQL>select switchover_status from v$database;

quick procedure for switchover.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL>alter system set log_archive_dest_state_2=defer;

old standby site

SQL>select switchover_status from v$database;

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate

SQL>startup

old primary site

SQL>recover managed standby database disconnect

How to check GAP? (run at both side (primary/standby)

select status, gap_status

from v$archive_dest_status

where dest_id = 2;

how to check delay?

select delay_mins from v$archive_dest where dest_id = 2;

how to check switchover status from primary db?

select switchover_status from v$database;

how to verify active session at primary db?

select sid, process, program

from v$session where type = 'USER';

pre-req for switchover.

1. check gap between both side

2. check tempfile name should be same at both side

3. check delay between both side.

4. verify primary db can be switch to standby (switchover_status should return to standby)

5. safe side verify the active session at primary db

alter database commit to switchover to standby; (on primary db)

6.command to switchover (perform on standby db)

alter database commit to switchover to primary with session shutdown; ( acitve sesion is there)

alter database commit to switchover to primary; (without active session)

Manually switch over to the standby database

Source: Google Search

warning message cpu jul-13

noreply@blogger.com (Mohammed Taj) — Sun, 21 Jul 2013 16:15:00 +0000

warning: overriding commands for target `nmosudo'
warning: ignoring old commands for target `nmosudo'
See the following document for additional information.

while applying critical patch update jul-13 on linux x86 11gr2 getting below error message:

warning: overriding commands for target `nmosudo'
warning: ignoring old commands for target `nmosudo'
See the following document for additional information.

As per oracle support the above error can be ignored. it is safe.

1562458.1 ---> this is internal notes so customer may not be find the same in support.oracle.com.

find . -name '*' | xargs rm -v

noreply@blogger.com (Mohammed Taj) — Mon, 29 Apr 2013 04:55:00 +0000

find . -name '*' | xargs rm -v

ORA-39181:Only Partial Table Data Exported Due To Fine Grain Access Control

noreply@blogger.com (Mohammed Taj) — Sun, 09 Dec 2012 08:21:00 +0000

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 20-SEP-2012***

Symptoms

ERROR:
ORA-39181 :only partial table data may be exported due to fine grain access control

This error appears trying to datapump export a table with FGAC policy enabled against it.

When Expdp runs as the schema owner, if fine grained policies are discovered on tables and if the exporting user has unrestricted access, then the entire table data is exported.

Changes

A table with FGAC policy enabled against it.

Scenario 1 (unprivileged user)
------------------------------
table: ABC_TAB
partition=yes
table_owner= X_USER
data= 500GB

when expdp X_USER/password parameter=exp_ABC_TAB.par
==>
Getting ORA-39181: Only partial table data may be exported due to fine grain access control

Scenario 2 (privileged user)
-----------------------------
table: ABC_TAB
partition=yes
table_owner= X_USER

when expdp oradba/password parameter=exp_ABC_TAB.par
==>
Getting exported "X_USER"."ABC_TAB":"ABC_TAB_AA"12340 KB 1000 rows
Master table "ORADBA"."ABC_TAB_AA" successfully loaded/unloaded

Cause

This is expected behavior.

ORA-39181 is caused by an unprivileged user who tries to export a table with a fine grain access control policiy applied. The table owner is subject to access control and may not be able to export all rows in the table. Only the rows that can be seen by that user are exported. In order to preserve integrity of the table, the user importing the table should have enough privilege to recreate the table with the security policies at import time.

Action: It is strongly recommended that the database administrator handles the export of this table.

This as an informational message.

VPD and Oracle Label Security are not enforced during DIRECT path export and similarly, database users granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from VPD enforcements. However, the following policy enforcement options remain in effect even when EXEMPT ACCESS POLICY is granted:

* INSERT_CONTROL, UPDATE_CONTROL, DELETE_CONTROL, WRITE_CONTROL, LABEL_UPDATE, and LABEL_DEFAULT

EXEMPT ACCESS POLICY is a strong privilege and must be granted with care. For example , grant it to the role exp_full_database role as this role is granted to admin users only.

Solution

To avoid this:

Grant the privilege EXEMPT ACCESS POLICY to the exporting user

-or-

Disable the VPD policy.

Service Request Numbers Are Not in Sequential Order

noreply@blogger.com (Mohammed Taj) — Mon, 03 Dec 2012 09:05:00 +0000

The Service Request automatic number generation is controlled by database sequence 'CS_INCIDENTS_NUMBER_S'.
That sequence comes seeded with an INCREMENT_BY value of 2 and a CACHE_SIZE value of 100.

That can be verified with the following query:

select sequence_name, increment_by, cache_size
from dba_sequences
where sequence_name in (
'CS_INCIDENTS_S',
'CS_INCIDENTS_NUMBER_S');

That will show the following:

SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE
------------------------------ ------------ ----------
CS_INCIDENTS_NUMBER_S               2           100
CS_INCIDENTS_S                      2           100
Due the reason that sequence numbers are cached, if for any reason the cache is flushed (like when the database is bounced) the cached numbers are lost. But the sequence itself is already advanced so, when the sequence numbers are cached again they will continue from the last number in the sequence and you will see a gap.
The gap will change depending on how many cached numbers were "lost" hence, that gap could be different and will look like the service request numbers are generated in random increments.

If you want to have a sequential order for Service Request numbers you can modify the sequence to have an INCREMENT_BY of 1 and no cache. That can be done with the following command:

alter sequence CS.CS_INCIDENTS_NUMBER_S INCREMENT BY 1 NOCACHE;

CS_INCIDENTS_NUMBER_S sequence will control the Service Request number.
If you want the INCIDENT_ID number in CS_INCIDENTS_ALL_B table to be also sequential then you need to modify CS_INCIDENTS_S. You can use the following command:

alter sequence CS.CS_INCIDENTS_S INCREMENT BY 1 NOCACHE;

java.sql.SQLException: Year out of range. when using database Audit Store

noreply@blogger.com (Mohammed Taj) — Wed, 28 Nov 2012 07:00:00 +0000

Applies to:

Oracle Access Manager - Version 11.1.1.5.0 and later
Information in this document applies to any platform.

Symptoms

When configured Audit log storage as database as per "http://docs.oracle.com/cd/E14571_01/core.1111/e10043/audpolicy.htm#" all data is going fine in table IAU_BASE but after some time errors like

[2012-04-10T14:07:54.279+01:00] [oam_server1] [ERROR] [] [] [tid: AuditLoaderRunner] [userId: ] [ecid: a9c385d0a9a28e67:2214479:136963cb7ee:-8000-0000000000000004,0] IAU:IAU-5047: Problem in transferring audit record from file:E:\Oracle\Middleware\user_projects\domains\base_domain\servers\oam_server1\logs\auditlogs\OAM\audit.log position:2,138,186 will retry in next cycle. caught exception oracle.security.audit.AuditException: java.sql.SQLException: Year out of range.[[
at oracle.security.audit.ajl.loader.OracleDatabaseRepository.write(OracleDatabaseRepository.java:360)
at oracle.security.audit.ajl.loader.JDBCLogWriter.write(JDBCLogWriter.java:101)
at oracle.security.audit.ajl.loader.AuditLoader.readMessages(AuditLoader.java:483)
at oracle.security.audit.service.AuditLoaderManager.readMessages(AuditLoaderManager.java:82)
at oracle.security.audit.service.AuditLoaderManager$Runner.run(AuditLoaderManager.java:151)
Caused by: java.sql.SQLException: Year out of range.
at oracle.jdbc.driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java:24253)
at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java:24633)
at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3269)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2466)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3747)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3887)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1508)
at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:172)
at oracle.security.audit.ajl.loader.OracleDatabaseRepository.write(OracleDatabaseRepository.java:321)
... 4 more

]]

appear.

If we delete the file OAM_Domain\servers\oam_server1\logs\auditlogs\OAM\audit.log manually, the server is working fine again but after some time it gives the same above error.

Cause

This issue is treated in OPSS Bug 13436847 - Audit data to database no longer works; audit data to file is working

Solution

The problem is fixed in patch# 13436847 available @ https://updates.oracle.com/download/13436847.html .

References

BUG:13436847 - AUDIT DATA TO DATABASE NO LONGER WORKS; AUDIT DATA TO FILE IS WORKING

inactive no manager concurrent request r12

noreply@blogger.com (Mohammed Taj) — Sat, 17 Nov 2012 12:57:00 +0000

Check output of ps -ef | grep FNDLIBR |wc -l

If its 1 then do,

- Stop CM
- Run cmclean.sql script — Concurrent Processing – CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables [ID 134007.1]
- Start CM

If its 0 then do,

A. run refer metalink id [134007.1] and run cmclean.sql , [ do a commit ]
B. Start the concurrent services , by running adcmctl.sh start apps/
C. Wait for few minutes , execute “ps -ef | grep FNDLIBR |grep -v grep|wc -l” and see whether the command output is greater than 0.

2. if the above does not work for you, then upload the latest concurrent manager log file

ls -ltr $APPLCSF/$APPLLOG/|grep -i $TWO_TASK|grep mgr

REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+

set verify off;
set head off;
set timing off
set pagesize 1000

column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'

WHENEVER SQLERROR EXIT ROLLBACK;

DOCUMENT

WARNING : Do not run this script without explicit instructions
from Oracle Support

*** Make sure that the managers are shut down ***
*** before running this script ***

*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***

#

accept answer prompt 'If you wish to continue type the word ''dual'': '

set feed off
select null from &answer;
set feed on

REM Update process status codes to TERMINATED

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;

set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');

REM Set all managers to 0 processes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

REM Reset control codes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;

REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;

set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';

REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin

select substr(release_name, 1, 2)
into vers
from fnd_product_groups;

if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;

statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/

prompt

prompt ------------------------------------------------------------------------

prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------

prompt

set feedback on

REM <= Last REM statment -----------------------------------------------------

Rc-50004: Fatal: Error Occurred In Applydatabase:Control File Creation Failed

noreply@blogger.com (Mohammed Taj) — Sat, 17 Nov 2012 12:17:00 +0000

Rc-50004: Fatal: Error Occurred In Applydatabase:Control File Creation Failed [ID 1350709.1]
Modified 06-OCT-2011 Type PROBLEM Status PUBLISHED
In this Document
Symptoms
Cause
Solution
References

Applies to:

Oracle Applications Manager - Version: 11.5.10.2 to 12.1.3 - Release: 11.5.10 to 12.1
Information in this document applies to any platform.
Symptoms

When attempting to clone the DB Tier , the following error occurs.

ERROR
-----------------------
Logfile Name : ApplyDBTier_08211054.log

RC-00118: Error occurred during creation of database
Raised by oracle.apps.ad.clone.ApplyDatabase

StackTrace:
java.lang.Exception: Control file creation failed
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:661)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:328)
at oracle.apps.ad.clone.ApplyDatabase.(ApplyDatabase.java:304)
at oracle.apps.ad.clone.ApplyDBTier.(ApplyDBTier.java:154)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:274)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:68)
at java.lang.Thread.run(Thread.java:534)

Also Log file addlnctl.txt showing the following error :

ERROR:
--------------

TNSLSNR for Linux IA64: Version 10.2.0.3.0 - Production
System parameter file is $RDBMS_ORACLE_HOME/network/admin/clone_dbclone/listener.ora
Log messages written to $RDBMS_ORACLE_HOME/network/admin/clone.log
Error listening on: (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCclone))
TNS-12555: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00525: Insufficient privilege for operation
Linux IA64 Error: 1: Operation not permitted

Cause

The contents of directory /var/tmp/.oracle have wrong permission
Solution

Please apply the following solution:

1- change permission /var/tmp/ . oracle

[root@fakoradb tmp]# chmod -R 777 .oracle/

How to change IP address on oracle biz suite r12

noreply@blogger.com (Mohammed Taj) — Thu, 25 Oct 2012 10:59:00 +0000

Symptoms

Users are not able to Log into the E-Business Suite Instance. Trying to access the E-Biz Instance, statis page redirection to /OA_HTML/ApssLogin is resulting in "page can't find"

Cause

The IP address of the Instance is changed but DBA followed incorrect method.

Hence new IP address wasn't propogated to FND_NODES resulting in error.

Solution

E-Business Suite Instance recognise host name of the node not IP address, but it does store the IP address in FND_NODES under SERVER_ADDRESS column. Follow the correct method of IP Address Change in E-Business Suite Instance.

Middle Tier

1. Change the IP address on the servers. Remember to check changes at all places depending on the Operating System e.g. /etcc/hosts in Linux.

2. Ensure to change the entry for the hostname in the DNS lookup table i.e. specify the new IP address at the DNS server. Check it with 'nslookup'.

3. Stop the Middle Tier Services.

4. Run the following command to remove the old IP address from the Oracle Applications tables:

> perl $AD_TOP/bin/adgentns.pl appspass=apps contextfile=$APPL_TOP/admin/_hostname.xml -removeserverNote: Replace _hostname.xml for the context file name under the $APPL_TOP/admin directory. Both must be entered in upper case.

In R12 context file location is $INST_TOP/appl/admin/_.xml.

5. Connect to SQL*Plus as apps user and run:

begin

FND_NET_SERVICES.remove_server('', '');

end;

/

commit;

/Note: Replace by the SID of the environment and by the hostname in the environment.

Both must be entered in upper case.

6. Run AutoConfig on the Middle Tier to populate the FND_NODES.

7. Use following script to check if the change is reflected in FND_NODES table:

spool fnd_nodes

set pagesize 50

col node_name format a15

col server_id format a8

col server_address format a15

col platform_code format a4

col webhost format a12

col domain format a20

col virtual_ip format a12

set linesize 132

select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F,

support_web W, node_name, server_id, server_address, domain, webhost, virtual_ip

from fnd_nodes

order by node_id;

Now the FND_NODES should have correct IP Address value.

8. For R12, follow Note 555214.1 OACORE Processes Won't Start After Increasing Their Number In Oracle Applications Release 12 In Context.xml to purge the lock files.

9. Start the Middle Tier Services.

Database Layer

1. Look for the hardcoded old IP Address in the following files:

a) listener.ora

b) tnsnames.ora (Local Naming) or check if any other naming method is used to resolve address.

Check if any of the parameter using the old IP Address.

2) Check if the parameter LOCAL_LISTENER and REMOTE_LISTENER parameter been set in Database Initialization file.

3) If its a Production Instance then its recommended to take the backup of the Database.

4) Please refer the Note 274476.1 and Note 363609.1 to have a detailed view to change the IP Address of the Database Server.

5) As the change is made at the OS (Operating System) level.Please ensure that the IP address gets reflected in the DNS server and also in the local host file.

How To Retrieve Rows From Table Or Synonym For An ORG_ID In E-Business Suite 12

noreply@blogger.com (Mohammed Taj) — Tue, 25 Sep 2012 05:22:00 +0000

In 11.5.10.2, Org Specific Views are used to filter rows with a specific ORG_ID.

For example

In SQL*Plus:

exec dbms_application_info.set_client_info('&ORG_ID');

In SQL Developer or Toad:

Begin

dbms_application_info.set_client_info('&ORG_ID');

End;

In R12, Views are no longer used. Instead, Synonyms together with Security Policy are used to return rows for a single operating unit.

Solution

The following provide the steps to retrieve Org Specific rows from a Synonym. In R12, mo_global.set_policy_context is used instead of dbms_application_info.set_client_info which is used in 11.5.10.2

To filter rows from synonym using org_id:

SQL*Plus

exec mo_global.set_policy_context('S', &org_id);

Toad and SQL Developer

Begin

mo_global.set_policy_context('S', &org_id);

End;

Example in SQL*Plus

As an example, run the following first to see how many org_ids are being used in Incentive Compensation

select org_id, count(*)

from cn_commission_headers_all

group by org_id;

Results in:

ORG_ID COUNT(*)

---------- ----------

1733 6

204 2340

So we can see that two row are returned. Let's take org_id 204 and run:

exec mo_global.set_policy_context('S',204);

This sets org_id as the organization we will be looking into. Run the following:

select org_id, count(*)

from cn_commission_headers

group by org_id;

Results in:

ORG_ID COUNT(*)

---------- ----------

204 2340

How to Schedule a Concurrent Request to Run Periodically Only on Certain Days and Only During Certain Hours of the Day

noreply@blogger.com (Mohammed Taj) — Tue, 25 Sep 2012 05:05:00 +0000

Checked for relevance on 31-May-2012

goal: How to schedule a concurrent request to run periodically only on

certain days and only during certain hours of the day

fact: Oracle Application Object Library

fact: FNDRSRUN - Run Reports

fix:

When scheduling a concurrent request these are the available options for running the

job:

1. As soon as possible

2. Once

3. Periodically

4. On specific days

Only one of these choices can be selected. It is not possible to combine any

of these choices. Thus, selecting "Periodically" and specifying &

quot;every two minutes" as the time interval, will result in a request

running every two minutes 24 hrs per day.

Selecting "On specific days" will result in a request running only

once on the exact days specified.

A workaround might be to create a specialized manager with a work shift from 6am

to 7pm. Then assign this concurrent request to that specialized manager (add

an INCLUDE rule to the specialized manager that includes this particular

concurrent request). This specialized manager will work only 6am to 7pm.

However, if the specialized manager is not available (7 pm to 6am) then any

standard manager that exists in the Apps instance will try to run the job. To

stop them from running the job it will be necessary to add an exclude rule for

those standard managers which explicitly tells them that they are not allowed

to run this job. However, between 7pm and 6am, these jobs will have a

phase/status of inactive/no manager until 6:00 am when this specialized

manager wakes up and resumes its work shift.

Another workaround might be to write a cron job at the OS level which uses the

CONCSUB utility to run the concurrent requests every two minutes.

How to change OBIEE respository users password (reset)

noreply@blogger.com (Mohammed Taj) — Sun, 22 Jul 2012 05:33:00 +0000

This article demonstrate how to change / reset OBIEE 11g respository user password.

Step:

1. You need to change first database schema password

SQL> alter user PROD_MDS identified by ORACLE;

SQL> alter user PROD_BIPLATFORM identified by ORACLE;

NOTE: only above two user password need to reset , this procedure also applicable if you forget respository username password.

2. Login to enterprise manager portal of BI and change JDBC data source password

URL : http://hostname:7001/em

username: weblogic (default)

password: weblogic (as set by user during installation)

3. go to weblogic domain --> right click on bifoundation_domain --> jdbc data source (click on it)

4. change password of bip*, EMPS* , rtd* and mds* datasource.

5. go to business intelligence category --> click on coreapplication --> click on deployment tab --> click on scheduler tab and change password

6. reboot machine and start BI services ( opmnctl and weblogic services)

7. check log file (em portal) for any error.

Input Not Supported Msg on LCD

noreply@blogger.com (Mohammed Taj) — Wed, 04 Jul 2012 15:36:00 +0000

INput Not supported msg on LCD. coming while login to linux server.

unable to find login screen and desktop.

CTRL+ALT+F1 to change GUI to TEXT MODE

CTRL + ALT + F7 to change TEXT MODE to GUI mode.

how to set login resolution to view login screen on linux machine.

login in single user mood (text mode)

root]#system-config-display --reconfig

the above command will open resolution windows (GUI MODE)

reset the resolution and save.

after that reboot linux machine.

Thanks

ORACLE.EXE Virtual Memory Keeps Increasing on x64 Windows

noreply@blogger.com (Mohammed Taj) — Mon, 02 Jul 2012 08:04:00 +0000

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5.0 [Release: 10.2 to 10.2]
Microsoft Windows x64 (64-bit)
NOTICE that this is a PLATFORM SPECIFIC problem for 10gR2 on x64 Windows systems

Symptoms

ORACLE.EXE continuous increases the amount of virtual memory used, and you see zombie thread accumulation on 64-bit MS Windows environment.

No ORA-4030 seen, but instance needs be restarted frequently to bring down the amount of virtual memory consumed by the oracle.exe process. Eventually, if all virtual memory has been consumed, ORA-4030 will occur.

Using Perfmon or TaskManager you see that the virtual size of oracle.exe is constantly growing (up to several 100's MB per day).

Using the VMMap tool to analyze the oracle.exe process, you see that the process stack accumulates zombie threads that holds around 3072 KB per zombie thread, accounting for the excess virtual memory consumption.

VMMap is a process virtual and physical memory analysis utility (available for free) (technet.microsoft.com/sysinternals).

Cause

10.2 was the first release on x64 Windows and it shipped with the 1.5 JDK / 1.4 in the database. There are some memory management issues with this release which are only fixed in 11.1+, so customers should use the workaround or upgrade to 11.1+.

The problem can occur when running JVM related applications.

When a session ends, the thread is not cleaned up at OS level, but remains as a zombie thread, still occupying 3072 KB of thread stack memory.

Solution

Workaround the problem by setting the following parameter, and restart the instance :

_kill_java_threads_on_eoc=TRUE

OR

Migrate to 11gR1 or 11gR2, where this problem does not occur.

Database Crashes With ORA-221, ORA-206, ORA-202, ORA-27072

noreply@blogger.com (Mohammed Taj) — Sun, 06 May 2012 04:59:00 +0000

Database Crashes With ORA-221, ORA-206, ORA-202, ORA-27072

One of my TEST database crashed with the above error.

Reason: it is due to BUG.

Solution: Apply the following patch 4401119

Solution

Workaround:

None. However the default server settings for AIX are:

MINIMUM number of servers [1]

MAXIMUM number of servers per cpu [100]

Maximum number of REQUESTS [4096]

This problem is improved slightly by changing the above values to something like:

MINIMUM number of servers (minservers) [64]

MAXIMUM number of servers per cpu(maxservers) [256]

Maximum number of REQUESTS (maxreqs) [8192]

Solution:

Apply patch 4401119 for the applicable Oracle version and Operating System

The issue is fixed in

10.1.0.5 (Server Patch Set)

10.2.0.2 (Server Patch Set)

11.1.0.6 (Base Release)

Please refer to

Note 4401119.8 - Bug 4401119 - AIX: Crash possible on AIX (ORA-206)

Metalink NOTE: [ID 332867.1]

Critical Patch January 2012 Applied

noreply@blogger.com (Mohammed Taj) — Fri, 09 Mar 2012 14:00:00 +0000

Hi,

Today i applied Critical Patch January 2012 on our all Production database successfully.

without any error :)

http://www.oracle.com/technetwork/topics/security/cpujan2012-366304.html