Oracle 調整 UID GID，導致 listener 啟動時有 TNS-00525 TNS-12560 TNS-12555 問題之解決方式

Problem:

原因是系統對執行oracle服務的OS帳號調整了uid跟gid，導致database服務crash，listener服務雖還在運行，但執行權限不對，在啟動 listener 時，會出現 TNS-00525 TNS-12560 TNS-12555 錯誤，資料庫也開不起來

Q1. 查了listener還在執行中，但不是oracle帳號，而是變成54321的UID，所以要 kill 掉

# ps -ef | grep LIS
   54321 35914004        1   0 14:29:30      -  0:00 /u01/app/oracle/product/11.2.0.2/dbhome_1/bin/tnslsnr LISTENER -inherit
# kill 35914004

重新啟動listener，但是有錯誤

$ lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 16-MAR-2022 17:46:33

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0.2/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0.2/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/pp2b/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12555: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00525: Insufficient privilege for operation
   IBM/AIX RISC System/6000 Error: 1: Not owner

Listener failed to start. See the error message(s) above...

Q2. 因為有調整過Oracle UID GID，導致檔案權限變成數值

$ ls -l /etc/oratab
-rw-rw-r--    1 54321    54321           727 Mar 16 13:34 /etc/oratab

Solution:

Step 1. 檢查 /etc/oratab 權限，並將其調整

# ls -l /etc/oratab
-rw-rw-r--    1 54321    54321           727 Mar 16 13:34 /etc/oratab

# chown oracle.oinstall /etc/oratab

Step 2. 檢查 oracle database FileSystem 權限，並將其調整

# ls -ld /oracle
drwxr-xr-x    4 54321    54321           256 Mar 15 13:54 /oracle
# ls -ld /u01
drwxr-xr-x    6 54321    odba            256 Mar 16 15:37 /u01

# chown -R oracle.oinstall /oracle
# chown -R oracle.oinstall /u01

Step 3. listener啟動時，會在/tmp或/var/tmp底下，建出 '.oracle' 暫存目錄

# ls -ld /var/tmp/.oracle
drwxrwxrwt    2 root     system          256 Mar 15 13:06 /var/tmp/.oracle
# >ls -l /tmp/.oracle
total 0
srwxrwxrwx    1 54321    54321             0 Mar 16 14:29 s#35914004.1
srwxrwxrwx    1 54321    54321             0 Mar 16 14:29 sEXTPROC1521

# chmod 777 /tmp/.oracle
# chmod 777 /var/tmp/.oracle

Step 4. 重新執行 $ORACLE_HOME/root.sh

# sh root.sh
Check /u01/app/oracle/product/11.2.0.2/dbhome_1/install/root_pp2b_2022-03-16_17-43-22.log for the output of root script
# cat /u01/app/oracle/product/11.2.0.2/dbhome_1/install/root_pp2b_2022-03-16_17-43-22.log

Running Oracle 11g root script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0.2/dbhome_1
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.
Finished product-specific root actions.

Step 5. 啟動 listener & database

$ lsnrctl start
$ sqlplus / as sysdba
$ startup