Sunday, April 20, 2014

MRP: Background Media Recovery process shutdown or NO MRP0 EXISTS !

MRP: Background Media Recovery process shutdown 


The main problem was standby db not applying the archive redo received from production DB. The MRP0 process was missing. When we query the following SQL it returned no rows.

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
no rows returned.

Suspicious list of errors showing on Standby Server alert:

ORA-16401: archive log rejected by Remote File Server (RFS)
MRP: Background Media Recovery process shutdown 

To Drill down check the error message in dataguard status:

select severity,
       error_code,
       message,
       to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') "TIMESTAMP"
  from v$dataguard_status
 order by timestamp desc;

You will find an error like following:
MRP0: Detected orphaned datafiles! 

This could be for two reason.

1. There may me UNNAMED datafiles

To Check:


select file#,name from v$datafile where name like '%UNNAMED%';

if no. 1 returns to row then try no. 2.

2. The problem may be for mismatch in Database Incarnation between production and standby.

To check:

Issue this RMAN command in production to check production DB incarnation:

RMAN>  list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL 1771320492       PARENT  1          24-AUG-13
2       2       ORCL 1771320492       CURRENT 925702     23-MAR-14

and the production incarnation is:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL 1771320492       PARENT  1          24-AUG-13
2       2       ORCL 1771320492       PARENT 925702     23-MAR-14
3       3       ORCL 1771320492       CURRENT  1932062    02-APR-14

Now reset the standby incarnation to match production incurnation:

RMAN> reset database to incarnation 2;

Now check again:

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL 1771320492       PARENT  1          24-AUG-13
2       2       ORCL 1771320492       CURRENT 925702     23-MAR-14
3       3       ORCL 1771320492       ORPHAN  1932062    02-APR-14

So, its now same.

The next task is to recover the standby database:

RMAN> recover database;

After recovering the standby start the MRP0 process again:

SQL> alter database recover managed standby database disconnect;

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS
---------
MRP0


So the log apply process is now working again.

Tuesday, December 24, 2013

ORACLE FLASH RECOVERY AREA USAGE QUERY


FINDING ORACLE FLASH RECOVERY AREA USAGE

SELECT NAME,
       (SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,
         ((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,
       ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL
  FROM V$RECOVERY_FILE_DEST;

Tuesday, September 10, 2013

Unblock the agent in OEM 12C by performing an agent resync from the console

Unblock the agent in OEM 12by performing an agent resync from the console



I am using OEM 12c. Today my EM page was showing that one of the agent in unreachable. So, first of all I tried to see the status of the agent and it showed me the agent is alright.

[oracle@NODE4 bin]$ ./emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.2.0
OMS Version       : (unknown)
Protocol Version  : 12.1.0.1.0
Agent Home        : /home/dir/oemcc/agent_inst
Agent Binaries    : /home/oracle/dir/core/12.1.0.2.0
Agent Process ID  : 5159
Parent Process ID : 5044
-
-
-
-
---------------------------------------------------------------
Agent is Running and Ready


Then I tried to upload the agent and found the following error:

[oracle@NODE4bin]$ ./emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: The agent is blocked by the OMS. Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. (AGENT_BLOCKED)


Solution:

Go to agent home page from TARGET -> ALL TARGET.

Then from AGENT menu click Resynchronization.

OR

SETUP - > Manage Cloud Control -> AGENTS
Then from AGENT menu click Resynchronization.

After some time agent is reachable again.

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


Sunday, July 7, 2013

How to delete/remove Management Agent from Oracle Enterprise Manager 12C

 1. Before you deinstall a Management Agent, do the following:
    a. Stop the Agent using command from Management Agent home:

                cd /u01/oemcc_latest/core/12.1.0.2.0/bin/
                $ emctl stop agent

    b. Wait for the Management Agent to go to the unreachable state in the Cloud Control console.
    c. It is mandatory to delete the Management Agent and their monitored targets using any of the following methods:
Remove the Agent target manually from the console:
1. Login to 12C Cloud Control
2. Navigate to Setup => Manage Cloud Control => Agents
3. Go to the Home page of the Agent that you want to remove
4. Expand the drop-down menu near the "Agent"
5. Expand the "Target Setup" option
6. Select "Remove Target"
           In Cloud Control Release 12.1.0.2, the following dialog box will be displayed if the Agent is still monitoring targets. Click Continue. You can then remove all targets (using the "Select All" link) in the next page.
           You will then be able to remove the host/agent itself.
 Note: Ensure that all targets monitored by the Agent are removed including the host target; before proceeding


2. Deinstalling using Graphical Method from OEM:

a.) Invoke the installer from Management Agent home by running the following command:
                cd /u01/oemcc_latest/core/12.1.0.2.0/oui/bin

$<AGENT_HOME>/oui/bin/runInstaller -deinstall ORACLE_HOME=<absolute_path_to_agent_home> [-removeallfiles]
Example:

./runInstaller -deinstall ORACLE_HOME=/u01/oemcc_latest/core/12.1.0.2.0 -removeallfiles


b.) In the Installation wizard click on "Installed Products" button.
c.) On the Inventory screen, select the plug-in homes, and click "Remove" button.
d.) On the Inventory screen, select the sbin home, and click "Remove" button.
e.) On the Inventory screen, select the Management Agent, and click "Remove" button.
f.) Remove agent folder

For UNIX platforms:
$ rm -rf <absolute_path_to_agent_base_dir>
Example:
$ rm -rf /u01/app/oracle/product/agent12c

references: 
Note-ID 1368088.1
Note-ID 1380846.1

Monday, January 7, 2013

How to Create INCIDENT Package

How to Create INCIDENT Package


You can create a logical package based on an incident number, a problem number, a problem key, or a time interval.
Create a logical package such that it will be most useful to diagnose the error of your concern.
Following are the method using which an incident package can be created :

Creating package based on incident.
Select correct incident if there are many incidents.


adrci > SHOW INCIDENT
adrci > IPS CREATE PACKAGE INCIDENT incident_number

If there are multiple incident then add each of them using following command:

adrci > IPS ADD INCIDENT incident_number PACKAGE package_number

Once you have created a logical package using any methods, next step is to generate a physical package.

adrci > IPS GENERATE PACKAGE package_number IN path

Here path is the Hard disk location where the ZIP file will be generated.

Note: Please see the metalink note 411.1 for further details.


Sunday, January 6, 2013

ADRCI error - DIA-48448: This command does not support multiple ADR homes


ADRCI error - DIA-48448: This command does not support multiple ADR homes


adrci> show control
DIA-48448: This command does not support multiple ADR homes

This error occurs due to multiple ADR home location. Solution is to set the ADRCI home for the instance you want to work.


adrci> show homes
ADR Homes: 
diag/rdbms/DB1/DB1
diag/rdbms/DBISL/DBISL
diag/rdbms/ultisl/ULTISL
diag/tnslsnr/DCDBLIVE-3/listenerc
diag/tnslsnr/DCDBLIVE-3/listeneri


adrci> SET HOME diag/rdbms/DB1/DB1

_________________________________________________________________________________

Monday, December 31, 2012

UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle


UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle



While importing a dumpfile to a test server, the following error were raised. Through a tough time searching the solution, I discovered the solution from a post.

My datapump command was like this:

-bash-4.1$ impdp system/sys123 schemas=scott directory=db_pump dumpfile=BEF_EOD.dmp

Import: Release 11.2.0.3.0 - Production on Mon Dec 31 03:30:01 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

The problem was the streams_pool_size was 0. So, according to the post I changed the value to 40M.

SQL> show parameter stream

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0


SQL> alter system set streams_pool_size=40M scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup
ORACLE instance started.


...and it was OK.

_________________________________________________________________________________