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.

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

1. Query:
   Party Type: Operating Unit Owning Tax Content
   Party Name: Populate your Operating Unit Name 
2. Click on GO
3. Click on Create Tax Profile and then
4. 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

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

To Bottom

---

In this Document Symptoms Changes Cause Solution 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.

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.

1. Login to Oracle Applications as sysadmin
2. Select the User Management Responsibility
3. Select the navigation path of ‘Roles and Role Inheritance’
4. Within the field ‘Type‘, select Roles and Responsibilities
5. Following step 4, a new field appears called ’Category‘, select ‘Miscellaneous’ as the value
6. Within the ‘Application‘ field, select ‘Cash Management‘. Note: Add the name of the role or responsibility if known
7. Click Go
   
8. 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.
9. Click on the update icon against the desired role or responsibility
10. A new window (update Role) will be displayed. Click the ‘Security Wizards‘ button
11. Click Save and Proceed (if requested)
12. Click the ‘Run Wizard‘ icon against ‘CE UMX Security‘ row
13. 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.
14. 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.
15. 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.

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"

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

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

Data Guard Switchover Note

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

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

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