Search This Blog

Loading...

2015-06-19

MS SQL Server: Finding Table's Index Count

Product: MS SQL Server
Version: 2005 - 2012

Following SQL can be used to find out number of indexes per table

select top 20 OBJECT_NAME(i.object_id) table_name , count(1) n_idx from sys.indexes i
where OBJECT_NAME(i.object_id) like 'c%'
group by object_id
having COUNT(1) > 10

In above SQL, I only listing tables which have more than 10 indexes, and limiting to first 20 rows.  You can adjust them to meet your need

2015-06-11

MS SQL Server: Truncate Transaction Log File

Product: MS SQL Server
Version: 2008R2 - 2012

Following is the steps to truncate and optionally shrink DB transaction log file.  Unlike other post you found in the internet, this step includes replication database, which many others do not consider

Step 1: Determine whether DB replication is enabled.  If the database name shows up, then you have a choice to disable it (applicable if you restore the DB), or tell replications to ignore data in transaction log files (will require to re-sync replication later)

select name, log_reuse_wait_desc from sys.databases where log_reuse_wait_desc = 'REPLICATION'

Step 2a: If you want to tell replication to ignore all data in transaction logs.  The database must be published with replication in place.  This won't work if this database is restored, where replication is not setup

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

If you encounters following error, then the replication is not setup, and use Step 2b

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Step 2b: Disable database replication.  Suitable to use in restored DB

USE [Database Name]
EXEC sp_removedbreplication

Before execute above statement, if you check sys.databases, you will see it shown "REPLICATION"

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 3: Verify that all transaction logs are inactive, where STATUS=0.  If recovery mode is SIMPLE, then it must be 0 at this step.  If recovery mode is FULL, then there will be some still in STATUS=2.  Once you execute Step 7, then it will be 0

DBCC LOGINFO

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
225395281921519220640
22539522621441519230640
22539525160961519240640
227852877004815192501280
2262144104857615192606423000000019400021
2262144131072015192706424000000009300239
2262144157286415192806425000000016500010
2262144183500815192906426000000015700030
2262144209715215193006427000000016200020
2262144235929615193106428000000008000001
325395281921522300640

Step 4: Verify transaction log wait status is no longer in Replication anymore.  It must display NOTHING

select name, log_reuse_wait_desc from sys.databases

Step 5: Verify whether DB in Simple or Full recovery mode.  Column recovery_model_desc will be either SIMPLE or FULL

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases

Step 6: Check whether transaction log in first virtual log (StartOffset=8192) for 1st log file

DBCC LOGINFO

FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
21341521928192145522640
2134152192134160384145480640

If last row has STATUS=2, then you won't be able to shrink it smaller than this size.

Regardless of recovery mode, change it to Simple recovery mode in order to force it to first virtual file

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT
CHECKPOINT
CHECKPOINT
DBCC LOGINFO
DBCC SHRINKFILE([Transaction Log File Name], 1)

Step 7: If DB recovery mode is SIMPLE, then proceed with shrink the transaction log file to 1 MB size

DBCC SHRINKFILE([Transaction Log File Name], 1)

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

Step 7: If DB recovery mode is FULL, then create a dummy backup before shrink

BACKUP database [Database Name] TO DISK = 'nul:'
BACKUP LOG [Database Name] TO DISK = 'nul:'
DBCC SHRINKFILE([Transaction Log File Name], 1)

Note: "nul" doesn't backup the database to physical file.  It just mimic a DB backup, but doesn't write to anywhere

If you don't want to shrink it, then use this statement

DBCC SHRINKFILE([Transaction Log File Name], EmptyFile)

If you restored the DB and would like to change it to Simple recovery mode, then use following:

ALTER DATABASE [Database Name]  SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([Transaction Log File Name], 1)

Now the size of the ldf file should be 1 MB, if you specify 1, but no change if you specify EmptyFile

Step 8: Verify data in transaction log files.  There should be only 1 record with STATUS=2, if there is no other user using it.  You will get 1 row for each additional transaction log files with STATUS=0.  This is a good time to drop the additional transaction log files, but not the 1st transaction log file.  This is the restriction of database, and you can easily see that primary (1st) transaction log file has an entry with STATUS=2 (Active), so it can't be dropped

DBCC LOGINFO

Quick Steps - With Simple Recovery

Assume DB name is test1, and transaction log name is test1_log, and I would like to shrink the transaction log file to 1 MB

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT
EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO

Quick Steps - With Full Recovery

EXEC sp_removedbreplication
BACKUP database test1 TO DISK = 'nul:'
BACKUP LOG test1 TO DISK = 'nul:'
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO

If LOGINFO shows last row with STATUS=2, then you have to use next option

Quick Steps - With Full Recovery and Temporary Set to Simple Recovery

ALTER DATABASE test1 SET RECOVERY SIMPLE WITH NO_WAIT
EXEC sp_removedbreplication
DBCC LOGINFO
CHECKPOINT 2 times if DBCC LOGINFO not showing STATUS=2 for first row with StartOffset=8192
DBCC SHRINKFILE(test1_log, 1)
DBCC LOGINFO
ALTER DATABASE test1 SET RECOVERY FULL WITH NO_WAIT

2015-04-23

Gosu Performance Tuning #1

Product: Guidewire Software
Module: InsuranceSuite (CC, PC, BC, ContactCenter)
Version: All

This post is about generic Guidewire application tuning, but its principle is generic and applicable to any J2EE applications, programming language and reporting, such as:

  • InsuranceSuite: ClaimCenter, PolicyCenter, BillingCenter, ContactManager
  • SAP: ECC 6, BusinessObjects Data Integration, BusinessObjects InfoView 
  • Cognos reporting

General tips

  1. Do not pull in too much data that is not required.  Filter it from database (ensure WHERE clause exists in database) instead of pulling all the data into InsuranceSuite then filter (avoid WHERE clause in Gosu/Java)
  2. Always check Java garbage collection log whenever there is performance issue.  Ensure it is not due to GC activity.  If it is, then capture/analyze Java heap memory, permgen space to find out more
  3. Avoid displaying too much data in browser.  Remember that end user might not share the gigabit network in data center or main office.  Some remote office might has 1 Gbps, but share with 500 users, where effective throughput is 1 Mbps (~ 100 KB) or lower
  4. Engage DBA to ensure you cover the database related performance issue
  5. Engage OS administrator to cover OS related performance issue, especially on CPU contention, over-provision CPU/RAM in VM, storage contention, swapping, memory contention, hardware failure
  6. Engage VM administrator to cover VM related performance issue.  Such as vMotion, over-provisioning, host server contention, known performance bug
  7. From Java thread analysis, once determine the slow or long running thread, you should be able to identify the API/Gosu function/Java class.  Escalate to developer to review and tune the code, if applicable
  8. Engage network administrator that there is no drop packets or packet resend.  Network disruption going to affect both performance as well as stability
  9. You might be able to temporary gain performance improvement by suspending message queue, workflow, external integration point, if the performance is due to poor coding in those areas.  This could buy you more time to keep the business running
  10. Overtune JVM heap memory setting will lead to longer garbage collection.  More is not always good, but optimal/balance is the best

  The opinions and statements in this communication are my own and do not necessarily reflect the opinions or policies of Guidewire

2015-02-09

Car: BMW 1999 - 2006 E46 CCV Issues

BMW 1999 - 2006 E46 3 Series
Models: 320i, 328i, 325Ci, 330Ci, 325i

BMW has a special PCV valve designed called CCV valve, and has been using up to 2006 model.

This CAD$200 valve is one of the parts fall under routine maintenance every 6 - 10 yr, and especially important in cold climate like Canada where temperature drops to - 15 C or colder.  Another part which closely related to CCV functionality is coolant thermostat which again has the similar life span and fall under same routine maintenance schedule.

This blog post provided some important points to identify failures which could lead to engine hydrolock under various scenarios, and not limited to CCV valve.  So call "hydrolock" here referring to the CCV valve is stuck due to ice build up inside the valve body (not the 4 hoses connecting to it), which is causing CCV to suck the engine oil from the dipstick all the way into intake port, and flooding the combustion chamber (piston head) and causing all 6 pistons to be wet and covered with engine oil.  The engine oil will eventually able to travel through the entire exhaust system from combustion chamber, 6 spark plugs, exhaust header, 4 O2 sensors, catalytic converter, resonator (for 330Ci only), tail pipe, and engine oil dripping from tail pipe.  The fatal problem is that the car will able to start in -15 C or colder for few minutes, then shortly engine will be dead (unable to turn), and requires a toll truck.

The financial impact to fix the hydrolock will be as much as CAD$1000 - CAD$1650 with following cost breakdown with 1 - 2 days of repair.  Most of the parts are contaminated by engine oil:

  1. CCV valve - CAD$200 BMW, or CAD$120 OEM
  2. engine valve cover gasket - CAD$26
  3. 6 spark plugs - CAD$65
  4. 4 O2 sensors - CAD$230
  5. Intake valve gasket - CAD$10
  6. Labor charge - Assuming CAD$75/hr, CAD$600@8 hr to CAD$1200@16 hr.  Mainly for CCV and cleanup engine oil from combustion chamber and intake valve
  7. Catalytic converter - CAD$800 ($400 each)

All of these are able to be done DIY to save the labor cost, but still cost at least CAD$525 to repair.  Luckily the minimum amount of work to make the car drive-able is just the CCV and such up the engine oil from each piston (have to do about 10 times).

The most important point to focus here is to prevent engine hydrolock by keeping optimal engine temperature.

Following are the components involve in all parts related to causing engine hydrolock, but excludes above  parts which might need to be replaced after affected by hydrolock, and their location:

  1. Heater core temperature sensor - located behind the climate control unit, and accessible by removing the gear switch cover and lower storage compartment.  Cost CAD$22
  2. Coolant engine temperature sensor - located below last intake port (near to windshield), but rarely an issue.  Cost $20
  3. Lower coolant hose temperature sensor - located on lower left coolant hose, and easily reachable below the aux fan connector.  Cost $30
  4. Coolant thermostat - located next to engine oil filter and below one of the CCV hose.  Common failure. Cost $80
  5. CCV valve - below the intake valve and behind the throttle body valve.  Common failure.  Cost $200 BMW or $120
  6. Concentrate coolant - Cost CAD$25
First, you will need BMW INPA software to fully identify all the potential problems.  Torque or regular OBD2 reader will not able to read all the temperature sensors temperature


Component 1: Coolant thermostat
Procedures:
  1. This is the first component to isolate for improper engine temperature
  2. Disconnect the electrical connector, and turn off the fan completely
  3. Start the engine, and let it run idle for 10 min, or drive it around for 10 min
  4. Observe the coolant temperature (assuming the parts if not faulty) from INPA, or engine temperature gauge from dashboard (less accurate)
  5. Ensure it hits 95 - 96 C consistently
  6. The heater can on and fan can be at lowest setting (not auto mode), and engine coolant temperature should still at 95 - 96 C
  7. If heater on at full fan speed, the temperature will be 91 C and higher
  8. Connects the electrical connect back to thermostat, and start the engine as soon as possible
  9. If it can remains at 95 - 96 C at idle engine, it passed the optimum engine temperature at idle
  10. Drive the car around for 10 min, without turn on the fan, and observe its temperature still maintain at 95 - 96 C
  11. If it can maintain the temperature, then this parts works as well as the rest of the temperature sensors.  You can skip the remaining troubleshooting and jump directly to CCV valve
  12. Typical failure is soft failure, where the temperature cannot reach 90 C with idle engine and no heater.  For example only can reach 50 C, or 80 C max.  If this is the case, then this part is bad, and require replacement before proceed to diagnose other parts
  13. A side effect of engine coolant temperature cannot exceed 80 C (optimal is 96 C) is that the yellow goo does not build up in engine oil fill cap
  14. A non scientific ways (if you don't has INPA software or OBD2 reader) to know whether to know whether engine coolant temperature is above 90 C on cold climate (says below 10 C) is to drive the car with heater on full fan speed for 30 minute and turn on the fan on central air vent (central air temperature knob on 3 dots red color).  Uses your fingers to touch the central air vent and it should be painfully hot to the touch

Component 2: Lower coolant hose temperature sensor
Procedures:
  1. Read the sensor temperature on cold engine
  2. If the temperature is > 10 C different than outdoor temperature, then replace it
  3. Run the engine for 10 min and read sensor temperature again
  4. If it is < 50 C, then replace it
  5. Remove the electrical connector to this sensor to simulate hot coolant temperature to trick the DME computer to turn on AUX fan in front of radiator (not the main radiator fan behind the radiator).  This will reduce the engine coolant temperature

Component 3: Engine coolant temperature sensor (below last intake port)
Procedures:
  1. Read the sensor temperature on cold engine.  It should be 5 - 15 C higher than outside temperature.  Cold engine temperature accuracy is not important, so don't replace it simply because it is 20 C higher than outside temperature
  2. Start the engine, and observe the temperature increase.  This reading can be from any OBD2 reader, including Torque cellphone app
  3. It should raise every minute, and will reach 96 C before start dropping to 91 C (with heater fan on full speed), or maintaining at 95 - 96 C (with fan off)
  4. The hot engine temperature must be accurately reads 95 C - 96 C (without heater fan turn on).  If it is not, then disconnect the electrical connector in thermostat to force the temperature reaching 96 C.  Its reading must reach 95 C - 96 C, unless bad thermostat
  5. If thermostat is good, and it cannot read 96 C, then this sensor is bad.  Replace this CAD$20 sensor
  6. Bad sensor that reads high temperature, or disconnecting the electrical connection will cause the sensor reading in OBD2 reader and dashboard to read max temperature, and will turn on the thermostat to reduce engine coolant temperature to 72 C max
  7. This sensor can be removed by using:
    1. 22 mm 3/8" 12-points socket (not deep socket) like this http://www.ultimategarage.com/shop/part.php?manufacturers_id=15&products_id=5794
    2. Modified open 22 mm wrench like this http://www.radtorque.com/images/custom_accessory_2.jpg
    3. 22 mm wrench like this https://erwin.volkswagen.de/workshop/englisch/pictures/44026100000.jpg
    4. S-type 22 mm wrench like this http://www.harborfreight.com/5-piece-metric-s-type-wrench-set-99699.html
    5. 22 mm 3/8" socket with joint socket adapter (prefer 1/4" adaptor) like this http://www.harborfreight.com/5-piece-metric-s-type-wrench-set-99699.html
Component 4: Heater core temperature sensor
Procedures:
  1. This is another reliable temperature sensor located behind climate control unit, and easily accessible to replace it or disconnect it (for troubleshooting) by removing following 2 components
    1. Gear shift cover (2 screws next to gear shift, and 2 screws on the dash)
    2. Small storage bin below the climate control IHKA panel
  2. Only INPA software can read the heater core temperature
  3. With cold engine, read its value, and its value should be 5 - 10 C similar to cabin temperature
  4. Turn on the engine, and set the fan to AUTO mode
  5. Watch the temperature slowly change every minute
  6. Its temperature should reach maximum of 92 C after 15 min with idle engine or 5 min driving.  If it took longer, there might be failure on other components, but should not be this sensor, unless it is loose, and not tightly attach to the box (heater core box)
  7. Disconnect the electrical connector (the cable is green color) to force the thermostat to turn on.  This will keep the engine coolant temperature at 70 C instead of 96 C
  8. If this sensor is missing, or reporting high temperature on warm engine, then it is bad, and need replacement
  9. This component cost ~ CAD$22 and can be any brand found in eBay, so don't waste money to buy BMW brand
Component 5: CCV valve
Procedures:
  1. This component does not affect engine coolant temperature, but only will cause high engine oil consumption (if engine coolant temperature can reach 96 C), or engine hydrolock (if engine coolant  temperature couldn't reach 96 C)
  2. First, ensure engine coolant temperature can reach 96 C.  Resolve this first before looking into CCV troubleshooting
  3. Open the engine oil fill cap on top of engine to see whether it has yellow goo
  4. If you are reading this post just for preventive maintenance or prevention only, and not to fix a problem, then you can eliminate the yellow goo by using any of following options
    1. In winter where temperature below 0 C, remove radiator fan that connects to water pump completely.  Store it and only re-attach it in summer
    2. In winter, cover the front grill with paper board to reduce the air flow to cool down the engine.  Just let the coolant radiating heat through radiator
    3. After each drive, remove the engine oil fill cap
    4. After each drive, disconnect the CCV valve connecting to valve cover (don't use this method if your CCV is old, because the plastic tube will break easily after 10 yr but will last longer for cold-climate model)
  5. Remove the CCV hose connecting to valve cover.  If you see there are lots of yellow goo build up on the wall and they are harden, the simplest way is to replace the entire CCV + hose
  6. A more time consuming method is to clean them
    1. Disconnect both CCV hose connecting to air distribution unit on top of engine which is connecting to intake valve body
    2. Stuck some cloth/paper to cover those 2 hose, or attach both hose to a grocery plastic bag
    3. Disconnect the CCV hose connecting to engine oil dipstick
    4. Connect this hose to engine oil container or a 2 - 4 liter catch can of any type
    5. Uses a funnel to pour camping stove fuel, coleman fuel, gasoline, white gas, naphtha, acetone, or auto transmission oil (more expansive) into the CCV hose connecting to valve cover.  You will need to buy 12 mm vacuum hose for this job, or funnel with flex tube
    6. The liquid will come out from the lower CCV hose (to the dipstick).  Let it drip for about 100 ml then cover the hose
    7. Fill up the liquid again, and let settle for 1 day
    8. Drain everything out
    9. Look through the hose again to ensure all the goo are gone.  If not, repeat step 5 and drain it another day
    10. Optionally use compress air to blow through each hose.  If the goo is soft, then the compress air is the quickest approach.  If it is harden, then using liquids to melt the goo is more efficient
    11. If transmission oil is used, then I recommend to fill it once with naphtha or acetone which dry up very fast (less than 5 min)

2015-01-01

CIKtel: Closing YouTube Message "Discover and add more channels....."

Company: CIKtel ISP
IPTV Model: MC600S
Application: YouTube
 
I have a CIKtel IPTV player with  above device that given by CIKtel. I would like to share the experience of using YouTube.

Firstly, an USB mouse is a requirement for using YouTube. The remote control have following problems:
  1.  It cannot close the help tips when you launch YouTube for the very first time. The message is "Discover and add more channels to follow here" with an OK button on the bottom of the screen as shown below. The remote control does not see this help message, so it cannot close it. Pressing any button on the remote control will allow you to the YouTube channels on the background as the tips never appear
Figure 1: YouTube channel subscription tips
  1. It cannot play any video that shown on the right panel. It is useless if you can't play any video, which is the primary function of YouTube
There are 2 USB ports to the right side of the device, which accept USB mouse as well as USB keyboard. I used an old Microsoft  mouse, and it recognized in 5 seconds, although there is no beep to notify me. Immediately after that I can see an arrow show up on my TV and able to navigate just like in PC

You can use any USB bluetooth mouse, wireless house, wired mouse, or trackball. The USB mouse technology is pretty standard and compatible between PC, Apple, Android.  I just dislike the fact that CIKtel QA team is not doing a good job in communicating with customer, and leave to customer to find that out themselves. I would like to highlight that CIKtel purposely designed their App Store to only allow installing Android apps which tested by them, so I do expect some message/tips to show on the screen when launching YouTube for the first time

2014-12-03

Advance UNIX: Linux bash Keyboard Shortcut

Product: RedHat, Fedora, CentOS, and various others Linux
Module: bash shell

This post is to demonstrate keyboard shortcut in Linux, which I spent lots of time in. All of these keyboard shortcut keys are in "man bash" page, but even myself don't fully understand it due to it was written for pure UNIX keyboards, instead of Windows keyboard.

I will use PuTTY to demonstrate, and I configured the emulation to xterm, and function keys can be any of those in PuTTY, e.g. ESC[n~, Linux, Xterm R6, VT400, VT100+

$ echo $TERM
xterm

Many people only uses 4 arrow keys to navigate in bash shell. Up to recall last command, down to recall next command, left and right. I won't discuss any of those simple navigation in this post.

This are the files I setup in a directory, and I will use them to illustrate advance editing

$ ls
file10.csv  file15.csv  file28.csv  PhaseInfo14.bz2
file11.csv  file22.csv  file31.csv  PhaseInfo14.csv
file12.csv  file24.csv  org.zip

Tips #1 Auto list multiple files

Shortcut: Alt-Shift-8

I would like to list all the files starts with filename "file."

Step 1: Enter "ll file"

$ ll file1

Step 2: Press Alt-Shift-8, and observe the screen
Step 3: It will automatically fill in all the file name

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv file31.csv

Tips #2 Delete word before cursor

Shortcut: Ctrl-W

While keeping cursor at end of line as shown below

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv file31.csv[cursor here]

Press Ctrl-W, and observe the screen. It will delete "file31.csv"

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv

Tips #3 Move forward/backward a word

Shortcut: Ctrl-F (forward), Ctrl-B (backward)

Note: Not case sentisive

Press these 2 shortcut keys to navigate within the command line. This is much faster than arrow key

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv file31.csv[cursor here]

Press Ctrl-W, and observe the screen. It will delete "file31.csv"

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv

Tips #4 Configure Left/Right Arrow Keys to move by word

Shortcut: Ctrl-Left, Ctrl-Right

Create following text file to configure left/right arrow key behavior. These keys are not configured by default

$ cat > ~/.inputrc << EOF
# Left arrow key - Ctrl-Left
"\eOD": shell-backward-word
# Right arrow key - Ctrl-Right
"\eOC": shell-forward-word
EOF
$ exec bash

The last command is to run bash to activate the new shortcut key (.inputrc)

Try copy-paste following command into your telnet/ssh terminal to test

$ ll file10.csv file11.csv file12.csv file15.csv file22.csv file24.csv file28.csv file31.csv[cursor here]

Press either Ctrl-Left and Ctrl-Right now. You will see the cursor is jumping backward/forward by word

Note: There are Alt-B and Alt-F which can be used for word navigation as well. Ctrl-A and Ctrl-E can be used to go to beginning and end of command line

Tips #5 Deleting word backward

Shortcut: Alt-Backspace, Ctrl-W

Tips #6 Deleting word forward

Shortcut: Alt-D, Ctrl-K

Alt-D can be used to delete word at the cursor location, while Ctrl-K will remove everything after the cursor

Tips #7 Autocomplete Multi-match

Shortcut: Alt-Space

This is again another custom key that I am configuring

$ echo >>   ~/.inputrc << EOF
"\e ": menu-complete
EOF

Let's say I have following files, which I will use to illustrate this shortcut

$ ls
ron000.mp4  ronald.zip  ronChen.txt

Step 1: Type following

$ ls ron

Step 2: Press Alt-Space to auto fill with 1st choice

$ ls ron000.mp4

Step 3: Press Alt-Space to auto fill with 2nd choice

$ ls ronald.zip

Step 4: Press Alt-Space to auto fill with 3rd choice

$ ls ronChen.txt

Step 5: Press Alt-Space and it will ring the "bell" and revert back to original word

$ ls ron

Continue pressing it again, and it will auto fill with additional choice. Then it reach the last one, it will trigger a warning sound in Windows, and not auto fill. If you press further, it will go back to 1st choice

Other Useful Shortcuts

Alt-Shift-` (which is ~) : auto complete username
Alt-Shift-4 (which is $) : auto complete env variable
Alt-Shift-2 (which is @) twice : list all hostname. Expect to see short hostname, FQDN, and localhost
Ctrl-X Ctrl-E : after you obtain the words from autocomplete or UP arrow for previous command, pressing this will allow you to edit the command in vi editor

2014-10-28

vnc xauth: timeout in locking authority file /home/user/.Xauthority

OS: CentOS 6.8

For some unknown reason when I setup VNC, I found following timeout warning when starting VNC. The commands pause for about 30 seconds prior of printing the warning. VNC seems to work, but I dislike the long pause, so I troubleshoot and solved the problem

-bash-4.1$ vncserver :2
xauth:  timeout in locking authority file /home/oracle/.Xauthority

New 'iccs2.rogers.com.my:2 (oracle)' desktop is iccs2.rogers.com.my:2

Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/iccs2.rogers.com.my:2.log

The solution is as below

Checks audit log for issue

# tail -f /var/log/audit/audit.log
type=AVC msg=audit(1414515075.757:51357): avc:  denied  { write } for  pid=4590 comm="xauth" name="oracle" dev=dm-3 ino=12 scontext=unconfined_u:unconfined_r:xauth_t:s0-s0:c0.c1023 tcontext=unconfined_u:object_r:home_root_t:s0 tclass=dir
type=SYSCALL msg=audit(1414515075.757:51357): arch=c000003e syscall=2 success=no exit=-13 a0=7fff35dc7f60 a1=c1 a2=180 a3=8 items=0 ppid=3707 pid=4590 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 tty=pts1 ses=8452 comm="xauth" exe="/usr/bin/xauth" subj=unconfined_u:unconfined_r:xauth_t:s0-s0:c0.c1023 key=(null)

This leads me to a known bug in RedHat below
https://bugzilla.redhat.com/show_bug.cgi?id=994752

Folowing command fix the problem
restorecon -R -v /home
[root@iccs2 audit]#  restorecon -R -v /home
restorecon reset /home/oracle context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_dir_t:s0
restorecon reset /home/oracle/.vnc/iccs2.guidewire.com:0.pid context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/.vnc/xstartup context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/.vnc/passwd context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/.gnome2/panel2.d/default context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gnome_home_t:s0
restorecon reset /home/oracle/.gnome2/panel2.d/default/launchers context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gnome_home_t:s0
restorecon reset /home/oracle/scchen/gc.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/scchen/cognos/cc705_cognos_sichen.dmp context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/scchen/b.txt context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/.local/share context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/Trash context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/Trash/info context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/Trash/info/VNC 1024.desktop.trashinfo context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/Trash/files context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/Trash/files/VNC 1024.desktop context unconfined_u:object_r:user_home_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/.converted-launchers context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/root context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/uuid-4e2249c7-2a94-41e5-b1ea-23ac20d6ad3c context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/home context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/home-a0f4bb81.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/root-e107fc3e.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/computer: context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/uuid-4e2249c7-2a94-41e5-b1ea-23ac20d6ad3c-5037733c.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/gvfs-metadata/computer:-e750142c.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/applications context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/applications/preferred-web-browser.desktop context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.local/share/applications/preferred-mail-reader.desktop context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:data_home_t:s0
restorecon reset /home/oracle/.gconf/desktop context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome/accessibility context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome/accessibility/keyboard context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome/accessibility/keyboard/%gconf.xml context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome/accessibility/%gconf.xml context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/gnome/%gconf.xml context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.gconf/desktop/%gconf.xml context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:gconf_home_t:s0
restorecon reset /home/oracle/.config/gnome-session/saved-session context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:config_home_t:s0
restorecon reset /home/oracle/script/db_backup.sh context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/script/afiedt.buf context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/script/db_backup.log context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:user_home_t:s0
restorecon reset /home/oracle/.dbus/session-bus context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:dbus_home_t:s0
restorecon reset /home/oracle/.dbus/session-bus/e7dac32006df225a8bda685e0000001a-0 context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:dbus_home_t:s0
restorecon reset /home/oracle/.dbus/session-bus/e7dac32006df225a8bda685e0000001a-1 context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:dbus_home_t:s0
restorecon reset /home/oracle/.dbus/session-bus/e7dac32006df225a8bda685e0000001a-2 context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:dbus_home_t:s0
restorecon reset /home/oracle/.pulse/e7dac32006df225a8bda685e0000001a-device-volumes.tdb context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:pulseaudio_home_t:s0
restorecon reset /home/oracle/.pulse/e7dac32006df225a8bda685e0000001a-card-database.tdb context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:pulseaudio_home_t:s0
restorecon reset /home/oracle/.pulse/e7dac32006df225a8bda685e0000001a-stream-volumes.tdb context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:pulseaudio_home_t:s0
restorecon reset /home/oracle/.pulse/e7dac32006df225a8bda685e0000001a-runtime context unconfined_u:object_r:home_root_t:s0->unconfined_u:object_r:pulseaudio_home_t:s0


After completed above command, now starting vncserver is immediate, and no more warning message

Other possible error is current user doesn't has permission to create new file in home directory. Manually create a file to verify whether this is the root cause

touch /home/oracle/test1
rm /home/oracle/test1

If you can't create file, then use "chmod +w /home/oracle" to give the permission

If the file system is not EXT3/EXT4 and so on, but a read-only mount point, then ensure you re-mount it and give it write permission, or set variable HOME to a different directory where writing is possible

Blog Archive