mzh

How to grant select on v$session
SQL> grant select on v$session to test;
grant select on v$session to test
ORA-02030: can only select from fixed tables/views

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$SESSION';
OWNER                          OBJECT_NAME                                                                      OBJECT_TYPE
------------------------------ -------------------------------------------------------------------------------- -------------------
PUBLIC                         V$SESSION                                                                        SYNONYM

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';
OWNER                          OBJECT_NAME                                                                      OBJECT_TYPE
------------------------------ -------------------------------------------------------------------------------- -------------------
SY…

EXPDP error ORA-39001: invalid argument value  and  ORA-01775: looping chain of synonyms
After issuing expdp command the following errors were occured:
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms


The solution is so simple in this case.
You have to find the synonym which was creating problem.
Issue the following command:
Select owner, object_name, object_type, status   from dba_objects  where object_name like '%SYS_EXPORT_%';
Drop the synonym or all the synonym found in this query.
drop public synonym SYS_EXPORT_SCHEMA_01;
Now run the expdp command again.
======================================================= Happy to help !!!

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 no row then try no. 2.
2. The problem may…

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;

Unblock the agent in OEM 12C by 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) 1…

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 wi…