Removing huge crfclust.bdb in 11.2.0.4 Grid home

Problem

Due to a bug (20186278) in 11.2.0.4, huge CHM(Cluster Health Monitor) files will be created in Grid Home. Remedy for this issue is available from 11.2.0.4.7 (Jul 2015) Grid Infrastructure patch set update or from 12.2. As a quick fix, you can stop the resource ora.crf and remove the files. The file location can be found as below:

Solution

Let's find the cluster health monitor's path: 

[oracle@myserver bin]$ ./oclumon manage -get reppath
CHM Repository Path = /u01/app/11.2.0.4/grid/crf/db/db01db01
Done

Let us list the files under this:

[oracle@myserver db01db01]$ ls -lhtr
total 19G
-rw-r--r-- 1 root root  89K May 29  2014 29-MAY-2014-17:48:14.txt
-rw-r--r-- 1 root root 1.3M May 29  2014 29-MAY-2014-20:02:00.txt
-rw-r--r-- 1 root root 2.1M May 31  2014 31-MAY-2014-11:52:44.txt
-rw-r--r-- 1 root root 1.2M May 31  2014 31-MAY-2014-11:58:47.txt
-rw-r--r-- 1 root root 2.1M Oct 14  2014 14-OCT-2014-16:15:09.txt
-rw-r--r-- 1 root root 1.3M Oct 14  2014 14-OCT-2014-16:29:55.txt
-rw-r--r-- 1 root root 1.9M Oct 23  2014 23-OCT-2014-15:49:22.txt
-rw-r--r-- 1 root root 1.3M Oct 23  2014 23-OCT-2014-16:26:58.txt
-rw-r--r-- 1 root root 1.8M Apr  7 16:30 07-APR-2015-16:30:33.txt
-rw-r--r-- 1 root root 1.2M Apr  7 16:38 07-APR-2015-16:38:42.txt
-rw-r----- 1 root root 8.0K May 29 16:58 repdhosts.bdb
-rw-r----- 1 root root  24K May 29 16:58 __db.001
-rw-r--r-- 1 root root 115M May 29 16:59 db01db01.ldb
-rw-r----- 1 root root 8.0K May 29 16:59 crfconn.bdb
-rw-r----- 1 root root  16M Sep  9 14:59 log.0000014662
-rw-r----- 1 root root  56K Sep  9 15:23 __db.006
-rw-r----- 1 root root 2.1M Sep  9 15:23 __db.004
-rw-r----- 1 root root  16M Sep  9 15:23 log.0000014663
-rw-r----- 1 root root 392K Sep  9 15:23 __db.002
-rw-r----- 1 root root 298M Sep  9 15:23 crfts.bdb
-rw-r----- 1 root root 460M Sep  9 15:23 crfloclts.bdb
-rw-r----- 1 root root 387M Sep  9 15:23 crfhosts.bdb
-rw-r----- 1 root root 424M Sep  9 15:23 crfcpu.bdb
-rw-r----- 1 root root  17G Sep  9 15:23 crfclust.bdb
-rw-r----- 1 root root 382M Sep  9 15:23 crfalert.bdb
-rw-r----- 1 root root 1.2M Sep  9 15:23 __db.005
-rw-r----- 1 root root 2.6M Sep  9 15:23 __db.003


As you can see in the list above, crfclust.bdb grew to 17G due to the bug. We can remove this file after stopping ora.crf as below: 

[oracle@myserver bin]$ ./crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'db01db01'

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

Now we can remove crfclust.bdb. It should be done as root user:

[root@myserver db01db01]# rm -f crfclust.bdb 

Restart ora.crf:

[oracle@myserver bin]$ ./crsctl start res ora.crf -init
CRS-2672: Attempting to start 'ora.crf' on 'db01db01'

CRS-2676: Start of 'ora.crf' on 'db01db01' succeeded

If you come across any file system full alerts on your Grid boxes, make sure you check this file if you run 11.2.0.4 binaries.

Changing database name and DBID in Oracle

After restoring production database to test for UAT or dev purposes, we may need to change the database name as well as DBID. When DBID is maintained unique, RMAN backup can be triggered for the restored database if we use recovery catalog. Let us see how to change both database name
and the DBID. You can opt to change just the database name.

First let us see the name and the DBID as below:

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
PRDIMP  47133164

To change the database name and the DBID, ensure that the database is in mounted state:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

The tool 'nid' helps us change both name and DBID. Let us see the parameters of nid as below:

[oracle@myserver ~]$ nid

DBNEWID: Release 11.2.0.4.0 - Production on Wed Aug 26 12:59:19 2015

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

Keyword     Description                    (Default)

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

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

As shown above, DBNAME accepts the new database name. If you make the SETNAME parameter to YES, only the database name will get changed. 

Execute the below to change the name only:

[oracle@myserver ~]$ nid TARGET=sys/ DBNAME=TSTIMP SETNAME='YES' LOGFILE=nid.log  

Execute the below to change the name and DBID:

[oracle@myserver ~]$ nid TARGET=sys/ DBNAME=TSTIMP SETNAME='NO' LOGFILE=nid.log  

This takes a few seconds to change the name. The log file can be located at the current working directory. 

After executing the above command, ensure that you copy your parameter file to reflect the new name as below:

[oracle@myserver dbs]$ cp initPRDIMP.ora initTSTIMP.ora

And change db_name in your parameter file. If it is a clustered setup, add instance_name as well. Once the change is made, startup the database using resetlogs if you opted to change both. If you just changed the name, you don't have to open using reset logs. 

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID

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

TSTIMP  2323285955

Both got changed as executed the second command.

Now you can make entries in your /etc/oratab file to reflect your changed database name.

This is how we change the database name and the DBID using nid utility.

A word of caution: If you change the DBID of a database, all the backups and archive logs become unusable!

How to check whether a table is audited or not in Oracle?

Problem

After enabling audit for some tables, I wanted to check a few days later the list of audited tables. Unfortunately I could not find a column like 'ISAUDITED' in dba_audit_trail/AUD$.

Solution

We can find out the list of audited tables in 'DBA_OBJ_AUDIT_OPTS' table as below:

In the list above, the tables are audited for select,insert,update and delete operations as they are marked by 'A/A'

Now we can check whether these audited tables are accessed or not by issuing the following query:

select OBJ_NAME from dba_audit_trail where obj_name in (select object_name from dba_obj_audit_opts);

If this query displays any record, then we can conclude that that table has been accessed.

Also, if an audited table is queried by SYS, then the table will not be tracked in AUD$ or DBA_AUDIT_TRAIL. So make you you query the table by some other user so that you can get your audit tested!

Disable logon/logoff audit for a specific user in Oracle

Problem

After enabling audit, there are many entries created in DBA_AUDIT_TRAIL table. If the database is a data warehouse (DWH) one, there are chances for many logon and logoff entries in the audit table by the user who loads data to DWH.

Solution

So it's better to disable auditing of the load user, mostly an application user. We can do that as below:

SQL> noaudit create session by ODI_USER;

Noaudit succeeded.

From now on, logon and logoff actions won't be audited for ODI_USER

How to change lines of scrollback in PuTTY permanently

Problem

Often we like to go back to see what we did or what was the output we got in a putty session. But, by default PuTTY can scroll back for 200 lines. As a result, we lose the commands we typed or the logs we saw that keep changing.

Solution

Of course you can change the setting for the current session by clicking the change settings as below:

and then you can change the lines of scrollback to 200000 or to a number of your choice as below:

But how can we change that for every new session that you open. It is possible by updating the 'Default Settings' as below. Open PuTTy and then load 'Default Settings' as below:

an then click 'Window' under Category panel in the left and increase the value as you wish. Best practice is adding 2 or 3 more zeroes:

Now come back to 'Session' and then save the 'Default Settings'. Now on if you open any PuTTy session, the lines of scrollback will reflect the new setting you made.

Conclusion

It is a good DBA/Sysadmin practice to change the lines of scrollback so that you miss your logs/ commands/query output.

OEM 12C Agent upgrade failed with an error 'The space required for the upgrade at /tmp/xxx is not sufficient. Free the space at /tmp/xxx or provide a different staging location

Problem

The space required for the upgrade at /tmp/tmp_2015_07_23_10_50_01_029 is not sufficient. Free the space at /tmp/tmp_2015_07_23_10_50_01_029 or provide a different staging location

The jar file is /u04/Grid12cr5/oms12cr5/oms/sysman/jlib/auc_prereqs.jar

 

On upgrading an agent from 12.1.0.2.0 to 12.1.0.5.0, I faced the following issue:

On clicking the Initializing upgrade parameters, the following message is displayed:

Executing command mkdir  -p /tmp/tmp_2015_07_23_10_50_01_029

Exit Code :0

The value of skipCleanup is false

Directory /tmp/tmp_2015_07_23_10_50_01_029 created successfully

The status of push jar file /tmp/tmp_2015_07_23_10_50_01_029/auc_prereqs.jar is true

Checking the free space available at /tmp/tmp_2015_07_23_10_50_01_029

Space free : 1 GB

Space required : 2 GB

Actual Space Available at : /tmp/tmp_2015_07_23_10_50_01_029 is 1GB

The check failed

Exit Code :1

The space required for the upgrade at /tmp/tmp_2015_07_23_10_50_01_029 is not sufficient. Free the space at /tmp/tmp_2015_07_23_10_50_01_029 or provide a different staging location

Solution 

Since I chose the default staging location for the 'Stage Location', Grid pushed the files to /tmp directory. Instead, we can choose a directory where we have some space. I have chosen /u04 as I have enough space there:

Now the upgrade just goes smooth: