Oracle : Check Hidden Parameter Values

You can run the below query to find the value of the Hidden parameters set on the database

undef 1 
set lines 150 
column value for a40 
col Parameter for a50 
col "Default Value" for a13 
col "Session Value" for a13 
col "Instance Value" for a13 
col IS_SESSION_MODIFIABLE for a25 
col IS_SYSTEM_MODIFIABLE for a24 

SELECT distinct a.ksppinm "Parameter", b.KSPPSTDF "Default Value", 
b.ksppstvl "Session Value", 
c.ksppstvl "Instance Value", 
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE 
FROM x$ksppi a, 
x$ksppcv b, 
x$ksppsv c 
WHERE a.indx = b.indx 
AND a.indx = c.indx 
AND a.ksppinm LIKE '_rollback_segment_%' 
/ 



Parameter                                          Default Value Session Value Instance Valu IS_SESSION_MODIFIABLE     IS_SYSTEM_MODIFIABLE

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

_rollback_segment_count                            FALSE         4000          4000          FALSE                     IMMEDIATE


_rollback_segment_initial                          TRUE          1             1             FALSE                     FALSE

Oracle : RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The backups were failing with below error after a DB refresh was done

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 02/26/2019 18:31:13
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Fix : 

We had to register the database by connecting to the catalog

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 27 14:58:42 2019

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

connected to target database: ABSPD2 (DBID=3485384910)

RMAN> connect catalog rman_s2rom1d2/Apr_of_2016@p1nbu1d5.db.att.com;

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

This fixed the issue and the backups started running again. 

Oracle :Cross Platform DB migration; Checking Endian Format

Doing cross platform migration using RMAN

Starting from 10gR2, Oracle introduced the next magical feature of RMAN its ability to convert the database from one platform to a different one which shares the same endian format.  This feature is called Cross-Platform Database Migration.  

To convert the database from one platform to another, the endian format of both databases should be the same

To know if we can use RMAN for the migration, We cam query v$transportable_platform view which shows the endian format for almost all Oracle platforms: 

SQL> select * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
----------- ------------------------------ --------------
1           Solaris[tm] OE (32-bit)          Big
2           Solaris[tm] OE (64-bit)          Big
7           Microsoft Windows NT             Little
10          Linux IA (32-bit)                Little
6           AIX-Based Systems (64-bit)       Big
3           HP-UX (64-bit)                   Big
5           HP Tru64 UNIX                    Little
4           HP-UX IA (64-bit)                Big
11          Linux IA (64-bit)                Little
15          HP Open VMS                      Little
8           Microsoft Windows IA (64-bit)    Little
9           IBM zSeries Based Linux          Big
13          Linux 64-bit for AMD             Little
16          Apple Mac OS                     Big
12          Microsoft Windows 64-bit for AMD Little



The v$database data dictionary view also adds two columns, platform ID and platform name:


select  name, platform_id, platform_name from v$database;

NAME      PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------

P3OMS1D5           13 Linux x86 64-bit

To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).

The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian. Big endian means the most significant byte comes first, and little endian means the least significant byte comes first.

Oracle : Difference between .trc and .trm

Oracle *.trm *.trc

A new type of files with .trm extension are created in ADR_HOME/trace directory in Oracle 11g databases. The following are some facts:

• From 11g onwards, all the trace files are found in ADR_HOME/trace directory.
• There is usually one “.trm” file for each “.trc” file.
• The files located in the TRACE directory, with the “.trm” extensions, are called Trace
• Metadata files. The metadata in .trm files describe the trace records stored inside of .trc trace files.
• The Trace metadata allows tools, such as ADRCI, to post process the trace information.
• By deleting the .trm file, you make the .trc file unusable for post processing from ADR using ADRCI tool.

Oracle : ORA-01624: log 2 needed for crash recovery of instance

Issue while dropping redo logs

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance db12 (thread 2)
ORA-00312: online log 2 thread 2: '+DB1_REDO1/db/onlinelog/group_2.259.994893411'
ORA-00312: online log 2 thread 2: '+DB1_REDO2/db/onlinelog/group_2.259.994893411'

Check the status of the Redo Group if its ACTIVE or INACTIVE
If its CURRENT, please do some redo switches

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS
---------- ---------- ---------- ----------------
         2          2        100 CURRENT
         1         11       6000 INACTIVE
         1         12       6000 ACTIVE
         1         13       6000 ACTIVE
         1         14       6000 ACTIVE
         1         15       6000 CURRENT
         1         16       6000 UNUSED
         1         17       6000 UNUSED
         1         18       6000 UNUSED
         2         21       6000 UNUSED
         2         22       6000 UNUSED
         2         23       6000 UNUSED
         2         24       6000 UNUSED
         2         26       6000 UNUSED
         2         27       6000 UNUSED
         2         28       6000 UNUSED

SQL> alter system switch logfile;

After performing some redo switch, the status of the REDO GROUP changed to ACTIVE, still preventing me from dropping the REDO GROUP

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS
---------- ---------- ---------- ----------------
         2          2        100 ACTIVE
         1         11       6000 CURRENT
         1         12       6000 INACTIVE
         1         13       6000 INACTIVE
         1         14       6000 INACTIVE
         1         15       6000 INACTIVE
         1         16       6000 INACTIVE
         1         17       6000 INACTIVE
         1         18       6000 INACTIVE
         2         21       6000 ACTIVE
         2         22       6000 ACTIVE
         2         23       6000 ACTIVE
         2         24       6000 CURRENT
         2         26       6000 UNUSED
         2         27       6000 UNUSED
         2         28       6000 UNUSED

Solution to do this is perform checkpoint

SQL> alter system checkpoint;

System altered.

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS

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

         2          2        100 INACTIVE

         1         11       6000 CURRENT

         1         12       6000 INACTIVE

         1         13       6000 INACTIVE

         1         14       6000 INACTIVE

         1         15       6000 INACTIVE

         1         16       6000 INACTIVE

         1         17       6000 INACTIVE

         1         18       6000 INACTIVE

         2         21       6000 INACTIVE

         2         22       6000 INACTIVE

         2         23       6000 INACTIVE

         2         24       6000 CURRENT

         2         26       6000 UNUSED

         2         27       6000 UNUSED

         2         28       6000 UNUSED

16 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

Hope this resolves your issue.. 

Oracle GoldenGate : Find Source of Replicat Process

There are many ways to find the source of the replicat process by looking at the trail files, the easiest way would be to read the details from the trail file directly. 

For example, lets find the source for the Replicat Process REPLICAT1

GGSCI (sev1) 4> info REPLICAT1

REPLICAT   REPLICAT1  Last Started 2018-11-10 23:36   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint  File /usr/app/db2/ggs/trails/a1036633
                     2018-12-06 09:42:42.013255  RBA 136840037


Use the trail file given above, and open it using strings  

GGSCI (sev1) 5> sh strings /usr/app/db2/ggs/trails/a1036633 | head -20

!uri:sev2::opt:app:db1:ggs5  <<----- Source Server Name (sev2) and DB name (DB1) 
!uri:sev2::opt:app:db1:ggs6
%/usr/app/db2/ggs/trails/a10366337
15610950306357
Linux1
sev22  <<-------- Source Server Name
2.6.32-696.10.3.el6.x86_643
##1 SMP Thu Sep 21 12:12:50 EDT 20174
x86_642
db12
db13
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
11.2.0.4.09
EXTRACT11 <------- Source Extract Name
HVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO4
15610950306357

Hope this helps you finding the source of Replicat process and troubleshoot the issues easily. 

GoldenGate : ADD SCHEMATRANDATA failing with OGG-01790 + ORA-06550

Tried to enable supplemental logging at schema level on 11.2.0.4 database triggered with following error  

GGSCI (sev1) 1> dblogin USERID g_user, PASSWORD oracle
Successfully logged into database.

GGSCI (sev1) 2> add schematrandata SCOTT

2018-06-11 09:54:07  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_CAPTURE_ADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.

The grants are missing for the GoldenGate user. Please run below to open up grants 

SQL> exec dbms_streams_auth.grant_admin_privilege('G_USER');

PL/SQL procedure successfully completed.

It failed again because of missing parameter enable_goldengate_replication, we need this set to TRUE to support GoldenGate Replication

GGSCI (sev1) 3> add schematrandata SCOTT

2018-06-11 10:51:21  ERROR   OGG-01790  Failed to ADD SCHEMATRANDATA on schema SCOTT because of the following SQL error: ORA-26947: Oracle GoldenGate replication is not enabled.
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1577
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 1086
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 722
ORA-06512: at line 1 SQL BEGIN sys.dbms_capture_adm.PREPARE_SCHEMA_INSTANTIATION('SCOTT','ALLKEYS_ON'); END;.

Change the parameter on DB level

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

Supplemental Logging got enabled now. 

GGSCI (sev1) 4> add schematrandata SCOTT

2018-06-11 10:52:35  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SCOTT.

To check if its enabled, please run below

GGSCI (sev1) 5> info schematrandata SCOTT

2018-06-11 10:57:23  INFO    OGG-01785  Schema level supplemental logging is enabled on schema SCOTT.

Hope this resolves your issue.