Monday, May 14, 2018

Oracle GoldenGate : OGG-02022 Logmining server does not exist on this Oracle database


While starting the new extract processes, the extracts were getting abended with below error


2018-05-10 17:32:00  ERROR   OGG-02022  Logmining server does not exist on this Oracle database.

2018-05-10 17:32:00  ERROR   OGG-01668  PROCESS ABENDING.

Solution :

   There is an easy solution for this. You need to login to the database through GGSCI prompt and register the extract. This should get your extracts started

Example : 


GGSCI > dblogin userid guser password LKJFSDKLJFLASDJLKSDJF
GGSCI > register extract extract1, database 
GGSCI > start extract1


Hope this resolves your issue.

Thursday, May 3, 2018

Oracle RAC : Change VIP status from INTERMEDIATE state back to ONLINE state

Check current VIP status:

$ crsctl status resource ora.serv2.vip
NAME=ora.serv2.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=INTERMEDIATE on serv1

Stop the VIP resource:

$ crsctl stop resource ora.serv2.vip
CRS-2673: Attempting to stop 'ora.serv2.vip' on 'serv1'
CRS-2677: Stop of 'ora.serv2.vip' on 'serv1' succeeded

Start the VIP resource:

$ crsctl start resource ora.serv2.vip
CRS-2672: Attempting to start 'ora.serv2.vip' on 'serv2'
CRS-2676: Start of 'ora.serv2.vip' on 'serv2' succeeded

Check current VIP status:

$ crsctl status resource ora.serv2.vip
NAME=ora.serv2.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=ONLINE on serv2

Monday, February 5, 2018

Oracle RAC : ASM instance startup failing with "terminating the instance due to error 482" in alert log

ASM instance startup failing with "terminating the instance due to error 482" in alert log

ASM instance alert log shows below error while starting the ASM instance on second node

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1994] [PC:0x43EFF99, kjbmprlst()+1369] [flags: 0x0, count: 1]
Errors in file /opt/app/oragrid/orabase/diag/asm/+asm/+ASM2/trace/+ASM2_lmd0_39620.trc  (incident=224081):
ORA-07445: exception encountered: core dump [kjbmprlst()+1369] [SIGSEGV] [ADDR:0x1994] [PC:0x43EFF99] [Address not mapped to object] []
Incident details in: /opt/app/oragrid/orabase/diag/asm/+asm/+ASM2/incident/incdir_224081/+ASM2_lmd0_39620_i224081.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20180131051633], requested by (instance=2, osid=39620 (LMD0)), summary=[incident=224081].
PMON (ospid: 39577): terminating the instance due to error 482

Fix :

The cluster_database parameter was changed to FALSE which was resulting in the ASM startup failures

Please run the below from the running cluster node making change to the ASM instance

SQL> alter system set cluster_database=TRUE scope=spfile sid='+ASM2';

System altered.

This should start your ASM instance now. I hope this resolves your issue. 

Saturday, December 30, 2017

Oracle : Spatial is INVALID In DBA_REGISTRY after applying patches

Spatial is INVALID In DBA_REGISTRY after applying patches



Issue : 

COMP_ID        COMP_NAME                            VERSION      STATUS
-------------- ------------------------------------ ------------ --------
OWB            OWB                                  11.2.0.4.0   VALID
APEX           Oracle Application Express           3.2.1.00.12  VALID
EM             Oracle Enterprise Manager            11.2.0.4.0   VALID
AMD            OLAP Catalog                         11.2.0.4.0   VALID
SDO            Spatial                              11.2.0.4.0   INVALID  <--- Invalid
ORDIM          Oracle Multimedia                    11.2.0.4.0   VALID
XDB            Oracle XML Database                  11.2.0.4.0   VALID
CONTEXT        Oracle Text                          11.2.0.4.0   VALID
EXF            Oracle Expression Filter             11.2.0.4.0   VALID
RUL            Oracle Rules Manager                 11.2.0.4.0   VALID
OWM            Oracle Workspace Manager             11.2.0.4.0   VALID
CATALOG        Oracle Database Catalog Views        11.2.0.4.0   VALID
CATPROC        Oracle Database Packages and Types   11.2.0.4.0   VALID
JAVAVM         JServer JAVA Virtual Machine         11.2.0.4.0   VALID
XML            Oracle XDK                           11.2.0.4.0   VALID
CATJAVA        Oracle Database Java Packages        11.2.0.4.0   VALID
APS            OLAP Analytic Workspace              11.2.0.4.0   VALID
XOQ            Oracle OLAP API                      11.2.0.4.0   VALID
RAC            Oracle Real Application Clusters     11.2.0.4.0   VALID

19 rows selected.

Solution :


connect / as sysdba
alter session set current_schema="MDSYS";
@?/md/admin/sdogr.sql
@?/md/admin/prvtgr.plb 

alter session set current_schema="SYS";
set serveroutput on
exec utl_recomp.recomp_serial('MDSYS');
exec sys.VALIDATE_SDO();
select comp_name, version, status from dba_registry where comp_id='SDO';
COMP_NAME  VERSION                 STATUS
---------  ----------------------  ------------------
Spatial    11.2.0.4.0              VALID

Hope this solution works for you.. 

Sunday, October 8, 2017

Oracle : ORA-10485: Real-Time Query cannot be enabled while applying migration redo

Issue :

After patching when starting the Standby database, MRP process fails with the below error. Observed the below err messages in alert log of standby.

All non-current ORLs have been archived.
Media Recovery Log +PRODDB1_FRA/prodDB1/archivelog/2017_10_08/thread_1_seq_68958.4014.956804163
MRP0: Background Media Recovery terminated with error 10485
Errors in file /opt/app/prodDB1/oracle/diag/rdbms/prodDB1/prodDB1/trace/prodDB1_pr00_44779.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Sun Oct 08 03:07:15 2017
Completed: alter database recover managed standby database using current logfile disconnect
Recovery interrupted!
Block change tracking service stopping.
Stopping background process CTWR
Sun Oct 08 03:07:17 2017
MRP0: Background Media Recovery process shutdown (prodDB1)


Cause :

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.

The Real-Time Query feature was enabled when an attempt was made to recover through migration redo generated during primary upgrades or downgrades

Solution

Start the standby database in mount stage and start applying logs till all the logs are applied, once the logs are applied, you can start the database in read only mode and enable Real Time Apply

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

Total System Global Area 2.1379E+11 bytes
Fixed Size                  2265864 bytes
Variable Size            5.6371E+10 bytes
Database Buffers         1.5730E+11 bytes
Redo Buffers              112762880 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session;

Database altered.


Alert Log Shows
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:10:56 2017
MRP0 started with pid=84, OS id=233034 
MRP0: Background Managed Standby Recovery process started (prodDB1)



Check if all logs are applied and there is no GAP between the Primary database and the Standby


    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1             68965            68965
         2             69547            69547

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


Alert Log Shows

alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:14:38 2017
MRP0 started with pid=84, OS id=33618 
MRP0: Background Managed Standby Recovery process started (prodDB1)
 started logmerger process
Sun Oct 08 03:14:43 2017
Managed Standby Recovery starting Real Time Apply
Only allocated 127 recovery slaves (requested 128)
Parallel Media Recovery started with 127 slaves


I hope this helps you.. 

Tuesday, June 27, 2017

Oracle : Downgrade 12c Databases to 11g Using Flashback Restore Point

Downgrading Database Without Executing catdwgrd.sql 


My Database was upgraded to 12.1.0.2 but due to performance issues, application team requested us to downgrade the database to 11.2.0.4 (previous version). 

The easiest way I could think of was to flashback the database using the restore point I created before upgrading the database to 12c


Below statements should hold true to flashback the database to the earlier version.
1) We have previous version binaries still present
2) A guarantee restore point created on earlier version before the upgrade is done
3) The compatible parameter is not changed. 


Steps to downgrade the database from 12.1.0.2 to 11.2.0.4


From 12.1.0.2 binaries

Check the restore point if exists

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                     SCN                          TIME
--------------------------------------------  ---------------------
BEFORE_UPGRADE       1.5096E+13                22-JUN-17 01.55.58.000000000 PM


Crosscheck if the compatible parameter is not changed

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE


Mount the database

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

Database mounted.

Flashback database to the restore point created

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE;

Flashback complete.


Alert log shows below

Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Jun 26 10:59:03 2017
FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE
Mon Jun 26 10:59:05 2017
Flashback Restore Start
Mon Jun 26 10:59:40 2017
Flashback Restore Complete
Flashback Media Recovery Start
 Started logmerger process
Mon Jun 26 10:59:41 2017
Parallel Media Recovery started with 12 slaves
Flashback Media Recovery Log /opt/app/prod1/oraflsh01/PROD1/archivelog/2017_06_22/o1_mf_1_1236_dnrds518_.arc
Mon Jun 26 10:59:42 2017
Incomplete Recovery applied until change 15095662683591 time 06/22/2017 13:56:00
Mon Jun 26 10:59:42 2017
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE

Once flashback completes shutdown the database

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


Now mount the database from older binaries, [in our case 11.2.0.4], set your environment to point to 11.2.0.4 binaries 

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3221227904 bytes
Database Buffers         1.8119E+10 bytes
Redo Buffers               36073472 bytes
Database mounted.

Open Resetlogs

SQL> alter database open resetlogs;

Database altered.

Now check the DB version, it should show  11.2.0.4

SQL> select comp_name, version, status from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle XML Database
11.2.0.4.0                     VALID

Oracle Expression Filter
11.2.0.4.0                     VALID

Oracle Rules Manager
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Workspace Manager
11.2.0.4.0                     VALID

Oracle Database Catalog Views
11.2.0.4.0                     VALID

Oracle Database Packages and Types
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
JServer JAVA Virtual Machine
11.2.0.4.0                     VALID

Oracle XDK
11.2.0.4.0                     VALID

Oracle Database Java Packages
11.2.0.4.0                     VALID


I hope these steps help you.. 


Saturday, June 10, 2017

ORA-01624 + ORA-00312 : While dropping Online Redo Logs

I had to resize my Online Redo Logs, but to do that I had to drop old Redo Logs after adding new ones


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance testDB (thread 1)
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo01/testDB/redo_1a.log'
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo02/testDB/redo_1b.log'

I was not able to drop the old Redo Logs as all were showing up as Active, log switches didnt help

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.


To mark them inactive, do a checkpoint on the database

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.

Now we can drop the old redo logs

SQL> alter database drop logfile group 1;

Database altered.