DG Broker ORA-16766: Redo Apply is stopped after a Database Restart

So, the other day I was engaged to check a dataguard, while checking its status with the dg broker I found this error

Error: ORA-16766: Redo Apply is stopped

Checking for it on dg broker:

DGMGRL> show configuration verbose

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Error: ORA-16766: Redo Apply is stopped

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

GMGRL> show database verbose STANDBY

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 3 seconds ago)
Apply Lag: 1 hour(s) 50 minutes 6 seconds (computed 0 seconds ago)
Apply Rate: 828.00 KByte/s
Real Time Query: OFF
Instance(s):
STANDBY

Database Error(s):
ORA-16766: Redo Apply is stopped

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
ERROR

The standby state was APPLY-ON but when checked the standby if the MRP process was running I found none!

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 4 17:05:12 2019

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

17:05:12 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS WRITING

8 rows selected.

Elapsed: 00:00:00.01

Checking further I found that the database was bounced while the MRP was running and that was the only explanation that I could see to see a difference between the dg broker also I could not find any error in the dg broker log $ORACLE_HOME/rdbms/log/drc*.log

After setting the state to apply-off and them to apply-on the issue was resolved.

DGMGRL> edit database STANDBY set state='apply-off';
Succeeded.
DGMGRL> edit database STANDBY set state='apply-on';
Succeeded.
DGMGRL> show database verbose STANDBY;

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
STANDBY

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

sqlplus / as sysdba
17:17:54 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG

9 rows selected.

Elapsed: 00:00:00.00

Hope it helps, unti the next one.

Elisson Almeida

 

Oracle memory usage on Linux / Unix

Hi all,

So one of the most important things that we need to do when setting up a new server or checking the capacity of the server is to see how much memory Oracle is using.

When checking the capacity there are some practical things that always help me to get a fast glimpse of the system:

  • When opening topas and hitting M you will see this below
Topas Monitor for host: SERVER1 Interval: 2 Sat Dec 8 03:39:59 2019
================================================================================
REF1 SRAD TOTALMEM INUSE FREE FILECACHE HOMETHRDS CPUS
--------------------------------------------------------------------------------
0 0 59.8G 59.6G 212.3 16.3G 528 0-15
1 1 61.4G 61.2G 188.8 15.7G 536 16-31

On the memory session you will see 3 categories, INUSE, FREE and FILECACHE. There you may see what is being using for what but there is not much granularity there.

  • When using top you have this summary below
top - 11:48:08 up 119 days, 10:18, 1 user, load average: 26.76, 26.16, 25.95
Tasks: 1936 total, 38 running, 1898 sleeping, 0 stopped, 0 zombie
Cpu(s): 79.3%us, 1.1%sy, 0.0%ni, 15.1%id, 4.3%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 263750172k total, 219075656k used, 44674516k free, 797476k buffers
Swap: 16773116k total, 505760k used, 16267356k free, 88055108k cached

Same you have a high level usage. So here comes the question:

How are you to prove that you have a memory shortage?

I often use vmstat on Linux looking on the columns si and so equals to 0 (swap in and swap out) and when the free command, the free column you will also have no or very low swap being used

/home/oracle> vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
15 3 505760 44896608 797480 88062288 0 0 7037 1304 0 0 29 2 61 8 0
16 1 505760 44922964 797480 88062320 0 0 432272 144314 38784 31348 41 2 52 5 0
14 2 505760 44943072 797480 88062320 0 0 468904 155424 32676 27522 34 1 60 5 0
15 2 505760 44943032 797480 88062328 0 0 431032 144275 32596 27469 34 1 60 5 0
15 2 505760 44920136 797480 88062352 0 0 396232 145052 30772 26657 32 1 62 6 0
19 1 505760 44928576 797480 88062360 0 0 429360 160158 33640 28012 36 1 58 5 0
15 3 505760 44935340 797480 88062368 0 0 477232 161849 28393 21423 41 1 53 5 0
17 1 505760 44924744 797480 88062368 0 0 515265 160212 27478 20578 40 1 54 5 0
16 1 505760 44921596 797480 88062368 0 0 495408 159304 25458 19548 37 1 58 5 0
18 1 505760 44918144 797480 88062384 0 0 552880 168895 28203 22774 38 1 56 5 0
15 2 505760 44922344 797480 88062392 0 0 546920 160463 25321 19151 37 1 58 5 0
16 4 505760 44921544 797480 88062400 0 0 571544 153810 25429 20011 36 1 58 5 0
16 1 505760 44919620 797480 88062400 0 0 577552 160004 27132 20111 40 1 54 5 0
19 2 505760 44360240 797480 88062400 0 0 584969 155553 29467 22145 41 2 52 5 0
/home/oracle> free
total used free shared buffers cached
Mem: 263750172 219060896 44689276 91608 797480 88062464
-/+ buffers/cache: 130200952 133549220
Swap: 16773116 505760 16267356

To check a process specific memory usage (RSS) I often use ps along with other commands to calculate the process memory for a specific process id as below:

/home/oracle> ps -eo rss,pid,euser,lstart,args:100 --sort %mem | grep -v grep | grep 35796 | awk '{printf $1/1024 "MB"; $1=""; print }'| sort
19.6016MB 35796 oracle Sat Sep 8 02:43:54 2018 ora_lg00_ORC1
34.957MB 32340 oracle Sat Jan 5 11:50:09 2019 oracleORC1 (LOCAL=NO)

RSS is resident memory, but when comes to shared memory like the Oracle SGA the methods above could be miss leading – not to say wrong – but as Oracle memory is shared we may see double counting on the results. I sometimes use pmap to check a process memory as well when available

/home/oracle> pmap 35796
35796: ora_lg00_ORC1
total 0K

But, still when checking a server wide scope, do you want to keep doing manual work and lots of math? I don’t think so. 🙂

That’s why when I came across SMEM made my life a lot easier. It is a python script which gives you a nice breakdown of the memory usage and without the miss leading double counting.

You can see the commands and processes and their memory:

[root@srv01 smem-1.4]# ./smem -trk | head
PID User Command Swap USS PSS RSS
4829 root /opt/stackdriver/collectd/s 444.0K 4.0G 4.0G 4.0G
5647 oracle asm_gen0_+ASM 50.1M 424.4M 425.0M 437.8M
16512 oracle rman software/product/11.2. 0 172.9M 173.7M 177.8M
85107 oracle ora_n001_db01 42.3M 147.8M 147.8M 185.8M
85103 oracle ora_n000_db01 42.4M 146.5M 146.6M 184.6M
85109 oracle ora_n002_db01 42.2M 145.6M 145.6M 183.5M
85111 oracle ora_n003_db01 42.1M 145.1M 145.2M 183.1M
7287 oracle ora_dia0_db01 1.6M 68.6M 68.8M 107.8M

As well the overall server per user:

root@srv01 smem-1.4]# ./smem -turk 
User Count Swap USS PSS RSS oracle 1358 4.8G 7.8G 8.0G 76.6G 
root 43 12.0M 4.1G 4.1G 4.2G user1 10 0 321.0M 328.0M 369.2M 
nobody 2 96.0K 2.1M 2.3M 6.0M user2 2 0 684.0K 1.7M 7.7M 
user4 2 0 632.0K 1.7M 7.9M user4 1 72.0K 536.0K 540.0K 2.1M 
ntp 1 424.0K 332.0K 368.0K 2.4M 
smmsp 1 1.3M 160.0K 298.0K 1.9M 
rpc 1 336.0K 68.0K 73.0K 1.7M 
rpcuser 1 808.0K 4.0K 16.0K 1.9M 
--------------------------------------------------- 
1422 4.8G 12.2G 12.5G 81.3G

Hope it helps, see you next time!

Oracle 11g / 12c identified by values – set an Oracle password to its current value without knowing the password

This post will be a quick one. I needed to set a Oracle password as it was about to expire but the client did not want to change the password itself.

The simplest way to get the encrypted password was:

select name, password from sys.user$ where name=‘USER’;

But after Oracle 11g/12c seems a better option for security ends to use DBMS_METADATA.get_ddl or select the spare4 column on sys.user$

select name, spare4 from sys.user$ where name=‘USER’;

or

set long 9999999
set longc 9999999
select dbms_metadata.get_ddl('USER','DBSNMP') from dual;

And how to set this password back to the user?

alter user [USER] identified by values '[encrypted password]';

 

Hope it helps!

Statspack top queries script by elspased time

Hi all,

I was engaged on a report request that I needed to gather to get the TOP SQL by elapsed time and using Statspack. I got those and than I was asked to it on the following week and on the following, and you may see when this is going. So I created a script which would give is a report and I would not have to do it manually ever again 🙂

Usage: long_run_sql.sh [-h ] [ -r 

Where:
If no parameters are used 120 minutes and 7 days will be used as default for History, 5 minutes for current running
-r = Set the time in minutes for the current running SQLs
-o = Specifies the minutes to be used for the long running SQLs History
-d = set the time period wanted for the execution history, default is 7 days
-h = Shows this help message

Might still have some bugs but it well enough to share here 🙂

You have some parameters that you need to change at the top, to suite your environment and of course have Statspack working and change the sqlplus connection line

Some tweaks are required as no 2 environments are never 100% alike. But still forth the work.

#!/bin/bash
#---------------------------------------------------------------------------
# Creates a report, using statspack, of the long running sqls from database
#
# History:
# Feb-21-2018 - Elisson Almeida - Created.
#---------------------------------------------------------------------------
#usage
### Environment setup
### Global variables
DIR=/home/oracle/scripts
LOG=/home/oracle/logs
DATE=$(date +%Y%m%d_%H%M%S)
NAME=long_running_sql_report
OUTPUT_FILE=${LOG}/${NAME}_${DATE}.log
ERROR_FILE=${LOG}/${NAME}.err
TMP_FILE=$DIR/$.tmp
CONFIG_EMAIL_TO=
PATH=
ORACLE_SID=
ORACLE_BASE=
ORACLE_HOME=
#tns-admin if needed otherwise comment out
#TNS_ADMIN
RUNNING=5
HISTORY=120
Help()
{
echo "Usage: long_run_sql.sh [-h ] [ -r 

Hope it helps!

Elisson Almeida

Managing listener.log and log.xml with Linux Logrotate

Hi all,
To manage Oracle trace files the way to go is ADRCI. You can see this post from Matheus if you have not read it yet.

In the last part of the script we have a small bash code to configure the ADRCI on all databases running on a server

You could add:

adrci exec="set home $1;purge -age 10080 -type ALERT";

In this case the age parameter is in minutes but still you would be required to run it periodically which could be another script in crontab to be managed.

SO the solution that I found to be best as it takes leverage from an existing solution is called logrotate.

Logrotate is a Unix/Linix based program that helps as its name says, rotate any file that you need. You just need to create a configuration file and place it on /etc/logrotate.d on most Linuxes distributions.

But when you have a server with several databases and listerners and more, it starts to get a bit tedious and time consuming to create this manually.

In this post on the Pythian Blog, will find how to create but it does not handle the listener.log but not the log.xml so I added this piece here

for L in `\ps -ef | grep tnslsnr | grep -v grep | sed s'/-.*$//g' | awk '{print $NF}'`
do
OUT=${DEST}/"logrotate_xml_"${L}
LSRN_LOG=`lsnrctl status ${L} | grep "Listener Log File" | awk '{print $NF}'`
echo ${LSRN_LOG%.*}"*" " {" > ${OUT}
cat << ! >> ${OUT}
daily
rotate 1
compress
notifempty
}
!
echo ${OUT} has been generated
done

Using logroate really helps on the managing on Oracle related files which are not done by ADRCI.

Hope it helps,

Elisson

GRID Infrastructure life after rootcrs.pl -deconfig -force -verbose -lastnode

Hi all,

I had a client which asked us to reconfigure the GRID infrastructure on 11g after the did a:

$GI_HOME/crs/install/rootcrs.pl -deconfig -force -verbose -lastnode

The “deconfig” option is used when we need to remove the GI configuration cleanly and the “lastnode”  is executed on the last cluster node.

But what we need to do to recreate the the cluster? Well most would say “Run root.sh again” and that should solve it on most cases.

But when I tried to execute it I have several issues on crsconfig_params file. I tried to manually add the missing data and as there was much info to add but what to do next?

A colleague pointed to 2 MOS notes:

How to Deconfigure/Reconfigure(Rebuild OCR) or Deinstall Grid Infrastructure (Doc ID 1377349.1)
How to Configure or Re-configure Grid Infrastructure With config.sh/config.bat (Doc ID 1354258.1)

So if you follow those notes  you should prepare a response run the config.sh to create a proper crsconfig_params and then run root.sh

$GI_HOME/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /app/11.2.0.4/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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: /app/11.2.0.4/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
Installing Trace File Analyzer
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'greporarac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'greporarac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'greporarac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'greporarac1'
CRS-2676: Start of 'ora.diskmon' on 'greporarac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'greporarac1' succeeded
PROT-1: Failed to initialize ocrconfig
PROC-26: Error while accessing the physical storage
ORA-15077: could not locate ASM instance serving a required diskgroup

Failed to create Oracle Cluster Registry configuration, rc 255
Oracle Grid Infrastructure Repository configuration failed at /app/11.2.0.4/grid/crs/install/crsconfig_lib.pm line 6911.
/app/11.2.0.4/grid/perl/bin/perl -I/app/11.2.0.4/grid/perl/lib -I/app/11.2.0.4/grid/crs/install /app/11.2.0.4/grid/crs/install/rootcrs.pl execution failed
$GI_HOME/crs/config/config.sh -silent -responseFile $GI_HOME/crs/config/grid_configwizard_1.rsp -ignorePreReq

As a root user, execute the following script(s):
1. /app/11.2.0.4/grid/root.sh

Execute /app/11.2.0.4/grid/root.sh on the following nodes:
[rac1, rac1]

Successfully Setup Software.
[WARNING] [INS-32091] Software installation was successful. But some configuration assistants failed, were cancelled or skipped.
ACTION: Refer to the logs or contact Oracle Support Services.
oracle@rac1:/app/11.2.0.4/grid/crs/config>


root@rac1 /app/11.2.0.4/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /app/11.2.0.4/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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.
Relinking oracle with rac_on option
Using configuration parameter file: /app/11.2.0.4/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization - successful
Adding Clusterware entries to oracle-ohasd.conf

CRS-2672: Attempting to start 'ora.mdnsd' on 'rac1'
CRS-2676: Start of 'ora.mdnsd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'
CRS-2676: Start of 'ora.gpnpd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'
CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded

ASM created and started successfully.

Disk Group CRS created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Successful addition of voting disk 04713db813e14f5abf6b385896b1ca1d.
Successful addition of voting disk 8910f31e58db4f30bfdca40e34a0ffbd.
Successful addition of voting disk 7fefe24a8d4b4fcbbfd2d25a4307a1e0.
Successfully replaced voting disk group with +CRS.
CRS-4266: Voting file(s) successfully replaced
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 04713db813e14f5abf6b385896b1ca1d (/dev/mapper/ora-pure-ractestwt1-crs-1) [CRS]
2. ONLINE 8910f31e58db4f30bfdca40e34a0ffbd (/dev/mapper/ora-pure-ractestwt1-crs-2) [CRS]
3. ONLINE 7fefe24a8d4b4fcbbfd2d25a4307a1e0 (/dev/mapper/ora-pure-ractestwt1-crs-3) [CRS]
Located 3 voting disk(s).


CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.CRS.dg' on 'rac1'
CRS-2676: Start of 'ora.CRS.dg' on 'rac1' succeeded
Preparing packages for installation...
cvuqdisk-1.0.9-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

#1) Respect the privacy of others.
#2) Think before you type.
#3) With great power comes great responsibility.

p_raghu's password on rac1:
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /app/11.2.0.4/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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.
Relinking oracle with rac_on option
Using configuration parameter file: /app/11.2.0.4/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization - successful
Adding Clusterware entries to oracle-ohasd.conf

CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node rac1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Preparing packages for installation...
cvuqdisk-1.0.9-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

 

Changing initrans on a Partitioned Index

Hi all,

We were having high ITL waits and high number of deadlocks (due to the hanging transactions waiting on the ITL on the data block) on a table and index and part of the fix was to increase the INITRANS on the table and on the index.

INITRANS is a physical attribute that determines the initial number of concurrent transaction entries allocated within each data block for a given table/index/cluster. Every transaction that updates a block has to acquire an Interested Transaction List (ITL) slot, in order to store the transaction id, rollback information and ultimately lock the necessary row/index entry within the block.

For an existing object it needs to be rebuild as INITRANS is a physical attribute on the Oracle datablock. So if you change on a table and you want it to take effect for the current data you need to move the table or one partition for example.

If you are doing this type of change on a partitioned index you need to change the index default attribute as well rebuild the index partition or the entire index.

But one thing caught my eye while working on this as after setting the new INITRANS default attribute and rebuilt the index online it did not changed the INITRANS values.

See below the execution on a test system

Continue reading

ORA-02158: invalid CREATE INDEX option while using redef_table on Oracle 12cR1

Hey all,
I’m working on a table redefinition project to migrate the existing tables and indexes to new compressed tablespaces. As the customer asked to have near 0 downtime to its data we decided to go with DBMS_REDEFINITION.

Simple right? Well… I sure hoped so.
I’m preparing a serie of posts about it, but before that I would like to share some hands on issues and that the magic of the new redef_table is not that great yet, at least on 12cR1/12.1.0.2.
Prior 12c, when would need to redefine a table you would use the DBMS_REDEFINITION and its 6 steps:

0 – Manually create a interim table to receive the data with the same structure as the original table
1 – DBMS_REDEFINITION.can_redef_table
2 – DBMS_REDEFINITION.start_redef_table
3 – DBMS_REDEFINITION.sync_interim_table
4 – DBMS_REDEFINITION.copy_table_dependents
5 – DBMS_REDEFINITION.finish_redef_table

And sometimes you would need to user the DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT to help on some issues but if everything was good you would only need to do the steps above.

There are a few issues with the approach by that I mean BUGS 🙂 so you need to watch your back do an a good prep work.

In 12c you have a new procedure called DBMS_REDEFINITION.redef_table that would bundle all the 6 steps into one single procedure call. With its up and down side.
For me, the down side is that we can’t monitor the procedure, once this is no loger anything being recorded on dba_redefinition_errors.
By working as a transaction, everything works or its rolled back (Or it should but I will leave it for another post).

So the only way to know what is being done is to trace the session that is doing the redefinition. And that what I needed to do to see what was going on with a strange situation.

This is what was happening: I 1st tried the DBMS_REDEFINITION.redef_table and it raised ORA-02158: invalid CREATE INDEX option but when I used the 6 steps mentioned above (can_redef_table,start_redef_table,etc) it worked without issues:

That bugged me so I traced the session.

SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

SQL> BEGIN DBMS_REDEFINITION.REDEF_TABLE(uname => 'USER1',
tname => 'TEST1',
table_compression_type => 'COMPRESS FOR OLTP', 
table_part_tablespace => 'DATA_COMP', 
index_tablespace => 'DATA_COMP', 
index_key_compression_type   => 'COMPRESS ADVANCED LOW', 
lob_compression_type => 'COMPRESS HIGH', 
lob_tablespace => 'DATA_COMP', 
lob_store_as => 'SECUREFILE');
END;
/
BEGIN*
ERROR at line 1:ORA-02158: invalid CREATE INDEX option
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 2

And this was the create index statement that was in the trace:

250438 PARSE ERROR #140737488293344:len=421 dep=1 uid=0 oct=9 lid=0 tim=3977494319094 err=2158250439 CREATE INDEX "USER1"."TMP$$_INDEX0" ON "USER1"."REDEF$_16752430_0" ("VISIT_NO")250440   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOWNOLOGGING250441   STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645250442   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1250443   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)250444   TABLESPACE "DATA_COMP"

Can you see the issue?
Well neither could I, a colleague read the trace again and found a silly bug.

Here it is The create index had this:

COMPRESS ADVANCED LOWNOLOGGING

Instead of this, it had this:

COMPRESS ADVANCED LOW NOLOGGING

A silly space was missing and was causing the entire redefinition to fail!

I could not find any reference in MOS but that was it a space prevented to use the redef_table and caused me to lose some hours on it.

Hope this save you some time on your troubleshooting and I will be posting other strange situations that I found using the redef_table on Oracle 12cR1.

See you next post!

Elisson Almeida

GRID upgrade FREEZES – 11g to 12c

Hey guys,
Upgrading is always something critical and a delicate operation but when you have no feedback on in the screen even harder.

I was working on an upgrade and using the GUI to upgrade the GRID from 11g to 12c. The 11gr2 11.2.0.4 was working without issue and ASM was as well (note this point, we will come back here later on).

When it was time to run the rootupgrade.sh, it just got stuck. No matter what, the GRID upgrade to 12c just FROZE. Checking the logs the last message was only this:

CLSRSC-467: Shutdown of the current Oracle Grid Infrastructure stack has successfully completed.

Looking the other logs (/u01/app/12.1.0/grid/cfgtoollogs/crsconfig) there were messages related to OCR, pointing it cannot get OCR key with CLUUTIL, try using OCRDUMP. I checked ORC with ocrdump and ocrcheck. No issues there as well. Also, as I said before, the cluster was working without any issues.

As I had no error code or any thing that would give me a more specific cause. I went to a broad search on google and MOS. Saw all kind of things until I found the MOS: Wrong DiscoveryString /dev/*: rootupgrade.sh/root.sh hangs: Check OCR key using ocrdump (Doc ID 1916106.1)

I checked any my ASM disk discovery string was set to /dev/* which did not strike me as an issue as I mentioned it was working… BUT when I changed the script in ASM to /dev/asm-* the upgrade worked like a charm.

Also as note there is this note, with some best practices for upgrading: How to Upgrade to/Downgrade from Grid Infrastructure 12.1 and Known Issues (Doc ID 1579762.1).

Hope this helps and save some time in your troubleshooting.

Élisson Almeida

“TNS-12531: TNS:cannot allocate memory error” – Are you sure, Oracle?

Hey guys!
So, I was working on a server build and everything was running fine until I tried to start the listerner. The process hang on “Starting /u01/app/grid/product/12.1.0/grid/bin/tnslsnr: please wait…” and then raised TNS-12531: TNS:cannot allocate memory error.

Well 1st thing, looked the error up using orerr:

TNS-12531: TNS: cannot allocate memory
Cause: Sufficient memory could not be allocated to perform the desired activity.
Action: Either free some resource for TNS, or add more memory to the machine. For further details, turn on tracing and re-execute the operation.

Should be simple right? Well, not in this case. The server had plenty of resources and not even the database was up yet so over 90% of the server memory was free.

Checked all sort of things when I started to check the server network configuration.
Looking up found that the server will through this error also when the hostname definition is different from what is resolved by the /etc/hosts file.

Once those matched, volià, listener started successfully.

Not the memory right? Oracle and its tricks…

That kept me bugging so I found this article, which shows a trace of the listener with a bit more information.

Hope this can save you some minutes on troubleshooting.

Until next time!