Database In Memory

Sunday, May 1, 2016

CRS-4402, CRS-2800, CRS-4000 : root.sh failed on second Node of RAC cluster

### Scenario:

/u01/app/11.2.0/grid/root.sh is failed on the second Node of the RAC cluster ##

[root@tnc2 ~]# /u01/app/11.2.0/grid/root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Creating trace directory

User ignored Prerequisites during installation

Installing Trace File Analyzer

OLR initialization - successful

Adding Clusterware entries to inittab

CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node tnc1, number 1, and is terminating

An active cluster was found during exclusive startup, restarting to join the cluster

Start of resource "ora.crsd" failed

CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'tnc2'

CRS-4000: Command Start failed, or completed with errors.

Failed to start Oracle Grid Infrastructure stack

Failed to start Cluster Ready Services at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 1353.

/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl execution failed

Node Alert log file shows as follows

[/u01/app/11.2.0/grid/bin/oraagent.bin(28566)]CRS-5019:All OCR locations are on ASM disk groups [GRID], and none of these disk groups are mounted. Details are at "(:CLSN00100:)" in "/u01/app/11.2.0/grid/log/tnc2/agent/ohasd/oraagent_oracle/oraagent_oracle.log".

2016-04-30 16:28:08.453:

2016-04-30 16:28:38.628:

2016-04-30 16:29:08.727:

2016-04-30 16:29:38.867:

error in ohasd logs as follows "/u01/app/11.2.0/grid/log/tnc2/agent/ohasd/oraagent_oracle/oraagent_oracle.log"

2016-04-30 16:53:26.893: [ora.asm][1075857728]{0:0:2} [check] checkCrsStat 2 CLSCRS_STAT ret: 184

2016-04-30 16:53:26.893: [ora.asm][1075857728]{0:0:2} [check] clsnUtils::error Exception type=2 string=

2016-04-30 16:53:26.893: [ora.asm][1075857728]{0:0:2} [check] AsmAgent::checkCbk: Exception UserErrorException

2016-04-30 16:53:26.893: [ora.asm][1075857728]{0:0:2} [check]

2016-04-30 16:53:26.893: [ora.asm][1075857728]{0:0:2} [check] InstAgent::check 1 prev clsagfw_res_status 4 current clsagfw_res_status 4

2016-04-30 16:53:27.896: [ora.asm][1077958976]{0:0:2} [check] AsmAgent::check ocrCheck 1 m_OcrOnline 0 m_OcrTimer 161

2016-04-30 16:53:27.896: [ora.asm][1077958976]{0:0:2} [check] CrsCmd::ClscrsCmdData::stat entity 5 statflag 32 useFilter 1

2016-04-30 16:53:27.897: [ COMMCRS][1133730112]clsc_connect: (0x1f159cb0) no listener at (ADDRESS=(PROTOCOL=IPC)(KEY=CRSD_UI_SOCKET))

2016-04-30 16:53:27.897: [ora.asm][1077958976]{0:0:2} [check] checkCrsStat 2 CLSCRS_STAT ret: 184

2016-04-30 16:53:27.897: [ora.asm][1077958976]{0:0:2} [check] clsnUtils::error Exception type=2 string=

2016-04-30 16:53:27.897: [ora.asm][1077958976]{0:0:2} [check] AsmAgent::checkCbk: Exception UserErrorException

2016-04-30 16:53:27.897: [ora.asm][1077958976]{0:0:2} [check]

2016-04-30 16:53:27.897: [ora.asm][1077958976]{0:0:2} [check] InstAgent::check 1 prev clsagfw_res_status 4 current clsagfw_res_status 4

2016-04-30 16:53:28.900: [ora.asm][1077958976]{0:0:2} [check] AsmAgent::check ocrCheck 1 m_OcrOnline 0 m_OcrTimer 162

2016-04-30 16:53:28.900: [ora.asm][1077958976]{0:0:2} [check] CrsCmd::ClscrsCmdData::stat entity 5 statflag 32 useFilter 1

2016-04-30 16:53:28.901: [ COMMCRS][1133730112]clsc_connect: (0x1f159cb0) no listener at (ADDRESS=(PROTOCOL=IPC)(KEY=CRSD_UI_SOCKET))

2016-04-30 16:53:28.901: [ora.asm][1077958976]{0:0:2} [check] checkCrsStat 2 CLSCRS_STAT ret: 184

2016-04-30 16:53:28.901: [ora.asm][1077958976]{0:0:2} [check] clsnUtils::error Exception type=2 string=2016-04-30 16:53:2

logged into ASM instance on second Node and query the disk status, it showed diskgroups is Dismounted. It clearly shows that DISKGROUP is not mounted on second node of the Cluster

######

SQL> select GROUP_NUMBER,NAME,OFFLINE_DISKS, state from v$asm_diskgroup;

GROUP_NUMBER NAME OFFLINE_DISKS STATE

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

0 GRID 0 DISMOUNTED

[root@tnc2 bin]# ./crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 357fa62799a74fcdbf518b0f0d6d9a29 (/dev/raw/raw1) [GRID]

2. OFFLINE 216d1cb038d14fc1bf8ccc05b0af6793 () []

3. ONLINE 061664871d014ff7bfac218182629a7d (/dev/raw/raw3) [GRID]

Located 3 voting disk(s).

Continued to install & Successfully Installed the GRID cluster : CVU is failed at end of grid installation, I found that there is no permission on disks on second node of the cluster

Later worked with SA by setting Udev Rules on the disks attached to Server

stopped crs force on node of the cluster and After restarted the crs on both nodes of the cluster ##

[root@tnc2 ~]# crsctl stop crs -f

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'tnc2'

CRS-2673: Attempting to stop 'ora.mdnsd' on 'tnc2'

CRS-2673: Attempting to stop 'ora.crf' on 'tnc2'

CRS-2673: Attempting to stop 'ora.ctssd' on 'tnc2'

CRS-2673: Attempting to stop 'ora.evmd' on 'tnc2'

CRS-2673: Attempting to stop 'ora.asm' on 'tnc2'

CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'tnc2'

CRS-2677: Stop of 'ora.evmd' on 'tnc2' succeeded

CRS-2677: Stop of 'ora.mdnsd' on 'tnc2' succeeded

CRS-2677: Stop of 'ora.crf' on 'tnc2' succeeded

CRS-2677: Stop of 'ora.asm' on 'tnc2' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'tnc2'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'tnc2' succeeded

CRS-2677: Stop of 'ora.drivers.acfs' on 'tnc2' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'tnc2' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'tnc2'

CRS-2677: Stop of 'ora.cssd' on 'tnc2' succeeded

CRS-2673: Attempting to stop 'ora.gipcd' on 'tnc2'

CRS-2677: Stop of 'ora.gipcd' on 'tnc2' succeeded

CRS-2673: Attempting to stop 'ora.gpnpd' on 'tnc2'

CRS-2677: Stop of 'ora.gpnpd' on 'tnc2' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'tnc2' has completed

CRS-4133: Oracle High Availability Services has been stopped.

# After #
# crsctl start crs

## Issue is resolved ##

--Nikhil Tatineni--

--RAC --

Tuesday, April 12, 2016

ERROR OGG-01705 Input checkpoint position 119195676...

Replicat Abended with following Error
ERROR OGG-01705 Input checkpoint position 119195676 for input trail file '/opt/app/ggs/trails/af000047' is greater than the size of the file (119194872). Please consult Oracle Knowledge Management Doc ID 1138409.1. for instructions.

Rootcause: Checkpoint is struck inside the trail and RBA keep on increasing and make Replicat Abended

Potential Fix: In-order to run fix replicat and avoid duplicate processing of records follow the steps as follows
From report File
A) 119195676 ( Check point Position on Trail )
B) 119194872 ( Size of the Trail )

In order to start Replicat We have to follow Formulae
Replicat RBA = (checkpoint position on Trail)-(Size of the Trail) + (RBA of the First record on next trail after the restart abended)

Procedure to get info about " (RBA of the First record on next trail after the restart abended)"
GGSCI (tnc63) 39> sh ls -ltr /opt/app/oval007p/ggs/trails/af*
-rw-r----- 1 ggs01 ggsoper 119194872 Apr 09 14:51 /opt/app/ggs/trails/af000047
-rw-r----- 1 ggs01 ggsoper 1980582 Apr 09 16:46 /opt/app/ggs/trails/af000048

./logdump
logdump> open /opt/app/ggs/trails/af000048
logdump> ghdr on
logdump> detail data
logdump> pos 0
logdump> n
2016/04/09 14:51:34.054.664 RestartAbend Len 0 RBA 1128
Name:
After Image: Partition 0 G s

Logdump>n
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 345 (x0159) IO Time : 2016/04/09 13:50:29.987.473
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 173237 AuditPos : 165583888
Continued : N (x00) RecCount : 1 (x01)
2016/04/09 13:50:29.987.473 Insert Len 345 RBA 1190
Name:

####
RBA= 119195676 - 119194872 + 1190 = 1994

Now Alter replicat with n+1 seq number and RBA
ggsci> alter repone, extseqno 48 extrba 1994
ggsci> start repone

Regards,
Nikhil Tatineni,
GoldenGate.

Tuesday, April 5, 2016

ERROR OGG-01755 Cannot register or unregister EXTRACT

Encountered following Error while registering integrated extract with database

ERROR OGG-01755 Cannot register or unregister EXTRACT extcbn because of the following SQL error: OCI Error retrieving bind info for query (status = 100). See Extract user privileges in the Oracle GoldenGate for Oracle Installation and Setup Guide

Root Cause as follows

Server crash, rebooted, OGG or database is not shutdown cleanly, the Integrated Extract is left in an indeterminate state

Unregister Extract Manually from database as follows

SQL> exec DBMS_XSTREAM_GG_ADM.STOP_OUTBOUND('OGG$MYEXTRACT',true);
SQL> exec DBMS_XSTREAM_ADM.DROP_OUTBOUND('OGG$MYEXTRACT');
SQL> exec DBMS_STREAMS_ADM.REMOVE_QUEUE(queue_name => 'GGMINING.OGG$Q_MYEXTRACT', cascade => true, drop_unused_queue_table => true);

If the above works correctly, execute these selects to check.

SQL> select * from DBA_XSTREAM_OUTBOUND;
SQL> select * from SYS.XSTREAM$_SERVER;
Make sure you don't see anything to do with this extract

I got this whole information from METALINK, we have to replace "MYEXTRACT" with your Extract Name

---Nikhil Tatineni---

---Oracle In Memory---

Thursday, March 24, 2016

Plan Hash Values for a Given SQLID Over Given Period & Historical Plans from the AWR

Plan Hash Values for a Given SQLID Over Given Period

SET PAGESIZE 60

SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value

FROM dba_hist_sqlstat q,

(

SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap

FROM dba_hist_snapshot ss

WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE

) s

WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap

AND q.sql_id IN ( '&SQLID')

Get Historical Plans from the AWR Using SQLID

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SELECT * FROM TABLE(dbms_xplan.display_awr('&SQL_ID'))

---Nikhil Tatineni—

Wednesday, March 23, 2016

ERROR OGG-00730 No minimum supplemental logging is enabled

Scenario where, changed primary keys for table / dropped supplemental logging & re-enabled supplemental logging with new keycolumns on table. Extract Abended with "ERROR OGG-00730 "& report as follows

2016-03-21 08:52:37 INFO OGG-01517 Position of first record processed Sequence 15315, RBA 187571728, SCN 3378.225080410, Mar 21, 2016 6:37:42 AM.
Source Context :
SourceModule : [er.redo.ora.out]
SourceID : [/scratch/aime1/adestore/views/aime1_staxj04/oggcore/OpenSys/src/app/er/re
do/oracle/redooraout.c]
SourceFunction : [get_next_complete_record(mempool_t *, memtran_t *, ora_xid_t *, int32_t,
log_context_t *, chkpt_context_t *, BOOL, BOOL *, tran_item_hdr_t *, file_def **, row_info_t *, row_in
fo_t *, row_info_t *, BOOL *, BOOL *, BOOL *, char *)]
SourceLine : [2916]
2016-03-21 08:54:21 ERROR OGG-00730 No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece.

RootCause:
checked for Minimal supplemental logging for database. But in my scenario, where Minimal Supplemental Logging is enabled for database.If it is NO, please Enable Minimal supplemental logging for database & start extract with Begin now :). Sync table after ..

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_MIN

YES

From METALINK I FOUND FOLLOWING INFORMATION ( Doc ID 1571665.1 )

This is due to a regression bug( handled via Bug 16857778)

The ogg v11.2.1.0 4 and lower could process the same archive log without any issues.

This bug is fixed after v11.2.1.0.9, currently I am using OGG Version v11.2.1.0.6

Use the undocumented parameter "TRANLOGOPTIONS DISABLESUPPLOGCHECK". Once it bypass the problematic archive log file remove the same.

Note:

We have seen the extract hanging in one case when using DISABLESUPPLOGCHECK and so please use it with caution.

Resolution:
Depend on your scenario, work around
1) alter extract with begin now
(or)
2) Add parameter ""TRANLOGOPTIONS DISABLESUPPLOGCHECK" to extract, restart the Extract :)

--Nikhil Tatineni--
--GoldenGate 12c--

Tuesday, March 22, 2016

GoldenGate ERROR OGG-00665 OCI Error describe for query (status = 4031-ORA-04031

Problem Summary & Problem Description
Developers started to perform DML operation on Huge Volume tables,
Oracle Goldengate Extract Abended with Error OGG-00665.

Errors in Report File as follows
2016-03-21 09:44:31 ERROR OGG-00665 OCI Error describe for query (status = 4031-ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","SELECT banner FROM v$version","sga heap(1,0)
","kglsim heap")), SQL<Not available>.
2016-03-21 09:44:31 ERROR OGG-01668 PROCESS ABENDING.

Resolution:
we increased memory structure "SGA" for database, issue is resolved

--Nikhil Tatineni--
--Oracle GoldenGate--

Procedure to flush SQL_ID from Shared Pool : Oracle 12c

In order to change the plan we have flush the current cursor in the library cache.

We can find existing cursor information with the help of SQL_ID :)

Steps as follows
Find out sql_id of the existing sql in memory using v$sqlarea;

Using v$sqlarea we can find out "sql_id" as follows
select sql_id, force_matching_signature, sql_text from v$sqlarea where SQL_TEXT like '%Select max%';

using DBMS_XPLAN.DISPLAY_CURSOR we can find "sql_id" stored in memory

SQL>select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));

Flush the cache from memory and using dbms_shared_pool.purge

SQL>exec dbms_shared_pool.purge('address','old_hash_value','C');

--Nikhil Tatineni--

--Oracle Database 12c --

12c: Upgrading OPatch Version
http://oracle-in-memory.blogspot.com/2016/02/12c-upgrading-opatch-version.html

12c: Upgrading Oracle Multitenant database

http://oracle-in-memory.blogspot.com/2016/02/12c-upgrading-oracle-multitenant.html

12c: New Notation for Oracle Patching Number
http://oracle-in-memory.blogspot.com/2016/02/12c-new-notation-for-patching-number.html
12c: In Memory Column Store

http://oracle-in-memory.blogspot.com/2015/11/12c-in-memory-column-store.html

12c: Heat Map and Automatic Data Optimization (ADO).
http://oracle-in-memory.blogspot.com/2015/11/12c-heat-map-and-automatic-data.html