How to grant select on v$session

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
------------------------------ -------------------------------------------------------------------------------- -------------------
SYS                            V_$SESSION                                                                       VIEW

SQL> grant select on v_$session to test;
Grant succeeded

EXPDP error ORA-39001: invalid argument value and ORA-01775: looping chain of synonyms

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 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 no 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

Else, If no. 1 returns row then try below steps.

I have checked below query outputs:

a.) select file#, error from v$recover_file;

70 FILE MISSING
b.) select file#, name, status from v$datafile;

70 /ora....oracle/product/11.2.0/db_1/dbs/UNNAMED00070 RECOVER

Steps:

1. Backup related file at primary

On primary:
——————–
$ rman target /

RMAN> backup datafile 70 format '/orasoft/cbs_rman/oracle-backup/st1/backdata.bk'  tag 'PRIMARY_70';

Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc

3. At the standby site, catalog the backuppiece and confirm it’s available for use:

On standby:
——————–
$ rman target /

RMAN> catalog backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backuppiece '/orasoft/oracle-backup/st1/backdata.bk';
RMAN> list backup of datafile 70;

Stop redo apply on the physical standby database

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

In My case redo apply has been already stopped.

On the standby site restore the datafile:

run below command for restoring datafile 70:

RUN {
SET NEWNAME FOR DATAFILE 70 to '/orasoft/oracle-backup/UNTOTBS00001.dbf';
RESTORE DATAFILE 70;
SWITCH DATAFILE 70;
}

Check status of files:Restart redo apply on the physical standby database

On standby:
——————–
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;

On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

So the log apply process is now working again.

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;

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

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) 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.

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

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

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.