Saturday, January 7, 2017

ORA-01950: no privileges on tablespace 'SYSAUX'

While taking a 12c database FULL export as sysdba and I got the below error

LINUX01:testdb:/opt/oracle/CB $ expdp directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile-exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y

Export: Release 12.1.0.2.0 - Production on Wed Dec 14 15:14:16 2016

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile=exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y
Startup took 4 seconds
Estimate in progress using BLOCKS method...
>>> ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.00.00'); END;
ORA-01950: no privileges on tablespace 'SYSAUX'
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA


Solution:

To fix this issue just grant below privilege to the SYSMAN user

LINUX01:: testdb:/export/testdb $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 14:54:30 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user SYSMAN quota unlimited on sysaux;
User altered.

Rerunning export job again resolved the issue and it is SUCCESS!!!


Refer:

Datapump Export Fails with ORA-1950: "no privileges on tablespace" (Doc ID 1633698.1)


ORA-39166: Object SYS.AUD$ was not found


When trying to backup the SYS.AUD$ table using datapump for my oracle 11g database, this is what i'm getting below

Oradev01:corpqa:/export/corpqa $ expdp directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 15:31:15 2017

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

Username: / as sysdba

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
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=EXPDR dumpfile=SYS_AUD_table.dmp logfile=exp_SYS_AUD_table.log tables=AUD$ exclude=statistics
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at 15:31:18

Cause:

According to Oracle there is a restriction on dataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode.


Solution:

Export the table SYS.AUD$ using the traditional export:

Oradev01: corpqa:/export/corpqa $ exp file=SYS_AUD_table.dmp log=exp_SYS_AUD_table.log tables=AUD$ statistics=none

Export: Release 11.2.0.3.0 - Production on Fri Jan 6 16:24:40 2017

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


Username: / as sysdba

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P15 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           AUD$  504728389 rows exported
Export terminated successfully without warnings.

Reference:
  
DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Doc ID 553402.1)
How to Export the AUD$ Table (Doc ID 745540.1)



Saturday, December 17, 2016

RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol

My oracle database: 9i  on HP-UX
My Catalog database: 12c on RHEL 6

Today while taking RMAN backup from backup tool we are getting below error

[Major] From: ob2rman@myoradec01.domain.com"prod"  Time: 10/15/16 17:03:34
                The database reported error while performing requested operation.

RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol


I wanted to try connecting from the server to the catalog database

Myoradev01:prod:/opt/oracle/product/9.2.0.8.64/network/admin $ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: PROD (DBID=176732808)

RMAN> connect catalog prod_user/xxxx@rcvc

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol

RMAN>


According to oracle 
 “Connections to a 12c database server can only be made from a 10.2 and later client.”


But if you make changes to sqlnet.ora on the catalog it will work, to do so go to catalog database and modify $ORACLE_HOME/network/admin/sqlnet.ora file as below

audbs1:rm:/opt/oracle/product/12.1.0.2.64/network/admin $ vi sqlnet.ora
AUTOMATIC_IPC=ON
SQLNET.INBOUND_CONNECT_TIMEOUT=0

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8  à ADDED this Line


 Try to connect to catalog database again from 9i database

Myoradev01:prod:/opt/oracle/product/9.2.0.8.64/network/admin $ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: PROD (DBID=176732808)

RMAN> connect catalog prod_user/xxxx@rcvc

connected to recovery catalog database


Now I can able to connect and my Issue is resolved.


Refer:
RMAN Catalog (12c) Connection Failed For 9.2.0.6.0 Database (Doc ID 1922190.1)


Thursday, December 15, 2016

BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT ORA-01950: no privileges on tablespace 'SYSAUX'

While taking a 12c database FULL export as sysdba and I got the below error

LINUX01:testdb:/opt/oracle/CB $ expdp directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile-exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y

Export: Release 12.1.0.2.0 - Production on Wed Dec 14 15:14:16 2016

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA directory=EMP_DIR dumpfile=testdb_FULL_dec14.dmp logfile-exp_testdb_FULL_dec14.log full=y exclude=statistics metrics=y
Startup took 4 seconds
Estimate in progress using BLOCKS method...
>>> ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.00.00'); END;
ORA-01950: no privileges on tablespace 'SYSAUX'
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

To fix this issue just grant below privilege to the SYSMAN user

LINUX01:: testdb:/export/testdb $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 14:54:30 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user SYSMAN quota unlimited on sysaux;
User altered.

Rerunning export job again resolved the issue and it is SUCCESS!!!


Datapump Export Fails with ORA-1950: "no privileges on tablespace" (Doc ID 1633698.1)



Saturday, December 3, 2016

ORA-01031: insufficient privileges


Server: HP-UX
Oracle DB: 10g

While connecting as sysdba user to one of my 10g database it gives me an error

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:27:01 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01031: insufficient privileges


myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 11:58:09 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> select * from v$pwfile_users;

no rows selected    à Seems to be an issue now

SQL>  grant sysdba to sys;
 grant sysdba to sys
*
ERROR at line 1:
ORA-01999: password file mode has changed from 'exclusive' to 'shared' 

SQL> sho parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/10.1.0.5.64/dbs/spfileMYCUSTDB.ora                               
                                                                                                                                                                                                  
myoraserver:MYCUSTDB:/opt/oracle/admin/MYCUSTDB/bdump $ cd $ORACLE_HOME/dbs

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ ls -lr orapw*

i can see my passwordfile, lets backup existing password file and recreate new with same password

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ mv orapwMYCUSTDB orapwMYCUSTDB.Nov03
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $  orapwd file=orapwMYCUSTDB password=xxxxxx


myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:02:07 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-01031: insufficient privileges


Enter user-name:


Still same error when connecting as sys user…………

Myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:24:09 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER SYSTEM SET remote_login_passwordfile=shared scope=SPFILE;

System altered.


*********     Need to Bounce the database    *********

SQL> select name from v$database;

NAME
---------
MYCUSTDB

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2046820352 bytes
Fixed Size                  1299208 bytes
Variable Size            1179397368 bytes
Database Buffers          855638016 bytes
Redo Buffers               10485760 bytes
Database mounted.
Database opened.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE   TRUE

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:24:58 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      SHARED


Now i can able to connect without any issues.............Now revert back to EXCLUSIVE

SQL> ALTER SYSTEM SET remote_login_passwordfile=EXCLUSIVE scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2046820352 bytes
Fixed Size                  1299208 bytes
Variable Size            1179397368 bytes
Database Buffers          855638016 bytes
Redo Buffers               10485760 bytes
Database mounted.
Database opened.
SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:28:42 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE


Reference:
ORA-1999: When Granting SYSDBA to User (Doc ID 103576.1)



RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured


Primary database: PRIMDB
Standby db unique name: PRIMDB_SB4
OS: RHEL6
Oracle database: 12.1.0.2
Catalog database:

Primary:
oradev8:primdb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Nov 2 11:08:31 2016

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

connected to target database: PRIMDB (DBID=1183475863)

RMAN> connect catalog primdb_rman/primdbpr2010@rcv

connected to recovery catalog database

RMAN> configure db_unique_name 'PRIMDB_SB4' connect identifier 'PRIMDB_SB4';

new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER  'PRIMDB_SB4';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIMDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER  'PRIMDB_SB4';
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default


Standby:

orastandby8:primdb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Nov 2 11:07:26 2016

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

connected to target database: PRIMDB (DBID=1183475863)

RMAN> connect catalog primdb_rman/primdbpr2010@rcv

connected to recovery catalog database

RMAN> resync catalog;

starting partial resync of recovery catalog
partial resync complete

RMAN> show all for db_unique_name all;


RMAN configuration parameters for database with db_unique_name PRIMDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER  'PRIMDB_SB4';
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default

RMAN configuration parameters for database with db_unique_name PRIMDB_SB4 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER  'PRIMDB_SB4';
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default



UNREGISTER db unique name:


If you want to remove DB_UNIQUE_NAME from show all, Connect to target and catalog and use below command

RMAN> unregister DB_UNIQUE_NAME 'db_unique_name';

database db_unique_name is "xxxx", db_name is "xxxx" and DBID is 771234705

Want to unregister the database with target db_unique_name (enter YES or NO)? yes

database with db_unique_name xxxxxx unregistered from the recovery catalog


Reference:
RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1)


Monday, November 7, 2016

Recovery Writer (RVWR) is stuck until more space is available in the recovery area.


Today I got an alert for My OMS is down and when I checked the oem url I see OMS down message.

[oracle@OMS_SERVER bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Down
JVMD Engine is Down
BI Publisher Server is Up

Checked OMS repository database(EMREPO) and i see database and listener is up and running fine but found below error in alert log…………

*************************************************************
Unable to allocate flashback log of 6377 blocks from
current recovery area of size 32212254720 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Mon Nov 07 10:51:46 2016
*************************************************************


SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
        30 29.9878693

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",
NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;
Type                                                                      % Used  % Reclaim    # Files
--------------------------------------------------------------------- ---------- ---------- ----------
CONTROL FILE                                                                   0          0          0
REDO LOG                                                                       0          0          0
ARCHIVED LOG                                                                   0          0          0
BACKUP PIECE                                                                 .06        .06          1
IMAGE COPY                                                                     0          0          0
FLASHBACK LOG                                                               99.9          0        154
FOREIGN ARCHIVED LOG                                                           0          0          0
AUXILIARY DATAFILE COPY                                                        0          0          0

8 rows selected.

SQL> show parameter db_recovery

NAME                                       TYPE                                                                                               VALUE
------------------------------------ -----------------------------------------------------------------------------   ------
db_recovery_file_dest                string                                                                                           /opt/oem/u20
db_recovery_file_dest_size           big integer                                                                                      30G



SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

 NAME                                     SCN TIME                                         DATABASE_INCARNATION# GUARANTEE STORAGE_SIZE
--------------------------------- ---------- -------------------------------------------- --------------------- --------- ------------
EMREPO_BEFORE_UPGRADE13C          3.1001E+11 27-OCT-16 11.04.22.000000000 AM                                  2 YES         3.2181E+10

Now, I remember it is because of the Grantee Restore point which I have created during my recent OMS upgrade and now I can drop this since it’s been so many days and everything looks good.

SQL> DROP RESTORE POINT EMREPO_BEFORE_UPGRADE13C;

Restore point dropped.

SQL>
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
---------- ----------
        30 .651931763


                               
[oracle@OMS_SERVER bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Already Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Already Started
BI Publisher Server is Up


Now I see my OEM url is working fine…………….


Check here