github.com/tommi2day/tnscli@v0.0.0-20240401211958-338fc0647b73/README.md (about) 1 # tnscli 2 3 Small Oracle TNS Service and Connect Test Tool 4 5 [![Go Report Card](https://goreportcard.com/badge/github.com/tommi2day/tnscli)](https://goreportcard.com/report/github.com/tommi2day/tnscli) 6 ![CI](https://github.com/tommi2day/tnscli/actions/workflows/main.yml/badge.svg) 7 [![codecov](https://codecov.io/gh/Tommi2Day/tnscli/branch/main/graph/badge.svg?token=C1IP9AMBUM)](https://codecov.io/gh/Tommi2Day/tnscli) 8 ![GitHub release (latest SemVer)](https://img.shields.io/github/v/release/tommi2day/tnscli) 9 10 11 ## Features 12 - connect to a given service using real connect method. 13 - Uses given credentials or default to raise an ORA-1017 error 14 - search or list tns entries 15 - load and write tnsnames.ora to ldap 16 - generates JDBC String for a service 17 - list all affected RAC Hosts/Ports for a given service using DNS SRV entries or racinfo file 18 - run a portcheck(TCP connect test) on all needed ports 19 20 ## Setup 21 ### recommanded: setup db test user 22 **CAUTION**: Don't use anonymous checks for permanent monitoring. Some security analysis systems are qualifying this as "Brute-Force-Attack" if the check are started too often. 23 Instead, set $TNSCLI_USER and TNSCLI_PASSWORD env or use --user and --password flag in check command to connect an existing user. This user needs only a `connect` privilege. 24 Replace `c##tcheck`, `tcheck` and `<MyCheckPassword>` with your own secrets 25 - **sample for set up a common user within CDB$ROOT** 26 27 ```sql 28 alter session set container=cdb$root; 29 create user c##tcheck identified by "<MyCheckPassword>" 30 default tablespace users temporary tablespace temp 31 account unlock container=all; 32 grant connect to c##tcheck container=all; 33 alter user c##tcheck default role all container=all; 34 ``` 35 - **sample for set up a traditional (non-cdb) user ** 36 ```sql 37 create user tcheck identified by "<MyCheckPassword>" 38 default tablespace users temporary tablespace temp 39 account unlock; 40 grant connect to tcheck; 41 alter user c##tcheck default role all container=all; 42 ``` 43 - export user secrets to environment 44 ```bash 45 export TNSCLI_USER="c##tcheck" # or 46 # export TNSCLI_USER="tcheck" 47 export TNSCLI_PASSWORD="<MyCheckPassword>" 48 ``` 49 ### optional: setup RAC Address info 50 ORACLE address info can be provided with DNS SRV entries or a racinfo.ini file in $TNS_ADMIN directory. 51 52 - *DNS SRV format:* 53 ``` 54 _myrac._tcp.rac.lan. IN SRV 10 5 1521 myrac.rac.lan. 55 _myrac._tcp.rac.lan. IN SRV 10 5 1521 vip1.rac.lan. 56 _myrac._tcp.rac.lan. IN SRV 10 5 1521 vip2.rac.lan. 57 _myrac._tcp.rac.lan. IN SRV 10 5 1521 vip3.rac.lan. 58 ``` 59 60 - *racinfo.ini format* 61 ``` 62 [RAC DNS Name as in tnsnames HOST Entry] 63 san=scan-address:port 64 vip1=vip-address1:port 65 vip2=vip-address2:port 66 ... 67 Example: 68 [MYRAC.RAC.LAN] 69 scan=myrac.rac.lan:1521 70 vip1=vip1.rac.lan:1521 71 vip2=vip2.rac.lan:1521 72 vip3=vip3.rac.lan:1521 73 ``` 74 75 76 ## Usage 77 ```bash 78 tnscli – Small Oracle TNS Service and Connect Test Tool 79 80 Usage: 81 tnscli [command] 82 83 Available Commands: 84 completion Generate the autocompletion script for the specified shell 85 help Help about any command 86 ldap LDAP TNS Entries 87 list list TNS Entries 88 service Service sub command 89 version version print version string 90 91 Flags: 92 -c, --config string config file 93 --debug verbose debug output 94 -f, --filename string path to alternate tnsnames.ora 95 -h, --help help for tnscli 96 --info reduced info output 97 --no-color disable colored log output 98 -A, --tns_admin string TNS_ADMIN directory (default "$TNS_ADMIN") 99 --unit-test redirect output for unit tests 100 101 Use "tnscli [command] --help" for more information about a command. 102 103 tnscli service [command] 104 105 Available Commands: 106 check Check TNS Entries 107 info give details for the given service 108 portcheck try to connect each service and report if it is open or not 109 110 Flags: 111 -h, --help help for service 112 -s, --service string service name to check 113 114 115 tnscli service check [flags] 116 Check all TNS Entries or one with real connect to database 117 Flags: 118 -a, --all check all entries 119 -H, --dbhost print actual connected host:cdb:pdb 120 -h, --help help for check 121 -p, --password string Password for real connect or set TNSCLI_PASSWORD 122 -t, --timeout int timeout in sec (default 15) 123 -u, --user string User for real connect or set TNSCLI_USER 124 125 Global Flags: 126 -c, --config string config file 127 --debug verbose debug output 128 -f, --filename string path to alternate tnsnames.ora 129 --info reduced info output 130 -s, --service string service name to check 131 -A, --tns_admin string TNS_ADMIN directory (default "$TNS_ADMIN") 132 133 134 tnscli service portcheck [flags] 135 list defined host:port and checks if requested. If racinfo.ini or SRV info given, addresses will be checked as well 136 Flags: 137 --dnstcp Use TCP to resolve DNS names 138 -h, --help help for portcheck 139 --ipv4 resolve only IPv4 addresses 140 -n, --nameserver string alternative nameserver to use for DNS lookup (IP:PORT) 141 --nodns do not use DNS to resolve hostnames 142 -r, --racinfo string path to racinfo.ini to resolve all RAC TCP Adresses, default $TNS_ADMIN/racinfo.ini 143 -t, --timeout int timeout for tcp ping (default 5) 144 145 146 tnscli service info [command] 147 148 Available Commands: 149 jdbc print tns entry as jdbc string 150 ports list service addresses and ports 151 tns print tns entry for the given service 152 153 Flags: 154 -h, --help help for info 155 156 Global Flags: 157 -c, --config string config file 158 --debug verbose debug output 159 -f, --filename string path to alternate tnsnames.ora 160 --info reduced info output 161 -s, --service string service name to check 162 -A, --tns_admin string TNS_ADMIN directory (default "$TNS_ADMIN") 163 164 tnscli service info ports [flags] 165 list defined host:port and checks if requested. If racinfo.ini given, it will be listed as well 166 Flags: 167 --dnstcp Use TCP to resolve DNS names 168 -h, --help help for ports 169 --ipv4 resolve only IPv4 addresses 170 -n, --nameserver string alternative nameserver to use for DNS lookup (IP:PORT) 171 --nodns do not use DNS to resolve hostnames 172 -r, --racinfo string path to racinfo.ini to resolve all RAC TCP Adresses, default $TNS_ADMIN/racinfo.ini 173 174 175 tnscli service info jdbc [flags] 176 printout jdbc string for the service 177 Flags: 178 -h, --help help for jdbc 179 --noModifyTransportConnectTimeout Do not modify TRANSPORT_CONNECT_TIMEOUT in ms 180 181 182 tnscli list [flags] 183 list all TNS Entries or search one 184 Flags: 185 -C, --complete print complete entry 186 -h, --help help for list 187 -s, --search string service name to check 188 189 tnscli ldap [command] 190 handle TNS entries stored in LDAP Server 191 Available Commands: 192 clear clear ldap tns entries 193 read prints ldap tns entries to stdout 194 write update ldap tns entries 195 196 Flags: 197 -h, --help help for ldap 198 -b, --ldap.base string Base DN to search from 199 -D, --ldap.binddn string DN of user for LDAP bind, empty for anonymous access 200 -w, --ldap.bindpassword string password for LDAP Bind 201 -H, --ldap.host string Hostname of Ldap Server 202 -I, --ldap.insecure do not verify TLS 203 -o, --ldap.oraclectx string Base DN of Oracle Context 204 -p, --ldap.port int ldapport to connect, 0 means TLS flag will decide 205 --ldap.timeout int ldap timeout in sec (default 20) 206 -T, --ldap.tls use secure ldap (ldaps) 207 208 ``` 209 ### Return Codes 210 - 0 success 211 - 1 failed 212 213 ## Examples 214 215 ```bash 216 >tnscli -version 217 218 # list only service names for named tnsnames.ora 219 >tnscli list -f test/testdata/connect.ora 220 XE.LOCAL 221 222 223 # list entries with full description 224 >tnscli list --complete -f test/testdata/connect.ora 225 # Location: test/testdata/connect.ora Line: 1 226 XE.LOCAL= (DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=21521))) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1))) 227 228 229 # list nonexisting service 230 >tnscli list --search mydb 231 Error: no alias with 'mydb' found 232 233 # give tNS String for a service 234 >tnscli service info tns xe -A test/testdata/ 235 # Location: ifile.ora Line: 6 236 XE.LOCAL= (DESCRIPTION = 237 (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))) 238 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = XE)) 239 ) 240 241 #give jdbc string for a service 242 >tnscli service info jdbc xe -A test/testdata/ 243 jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE))) 244 245 #give target host and port for a service 246 >tnscli service info server xe -A test/testdata/ 247 127.0.0.1:1521 248 249 # check if port is open for a service 250 >tnscli service portcheck xe.local -A test/testdata 251 127.0.0.1 (127.0.0.1:1521) is OPEN 252 253 # give ALL target host and port for a service with RAC and DNS SRV resolution 254 >tnscli service info ports myrac -f test/testdata/rac.ora --nameserver 127.0.0.1 255 vip1.rac.lan (172.24.0.13:1521) 256 vip3.rac.lan (172.24.0.15:1521) 257 vip2.rac.lan (172.24.0.14:1521) 258 myrac.rac.lan (172.24.0.3:1521) 259 myrac.rac.lan (172.24.0.4:1521) 260 myrac.rac.lan (172.24.0.5:1521) 261 262 # static resolution with racinfo.ini without DNS. With DNS access hostnames will be resolved to IP addresses as above 263 service info ports myrac -f test/testdata/rac.ora -r test/testdata/racinfo.ini --nodns 264 myrac.rac.lan (myrac.rac.lan:1521) 265 vip1.rac.lan (vip1.rac.lan:1521) 266 vip2.rac.lan (vip2.rac.lan:1521) 267 vip3.rac.lan (vip3.rac.lan:1521) 268 269 270 # login check for unavailable service with tnsnames.ora in $TNS_ADMIN 271 >tnscli check xe.local 272 Error: service XE.LOCAL NOT reached:dial tcp 127.0.0.1:1521: 273 274 # check connect to service xe with dummy credentials, expect ORA-01017 275 >tnscli check xe -f test/testdata/connect.ora --info 276 [Thu, 13 Apr 2023 21:25:27 CEST] INFO use entry 277 XE.LOCAL=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=21521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1))) 278 279 280 [Thu, 13 Apr 2023 21:25:28 CEST] WARN Connect OK, but Login error, maybe expected 281 [Thu, 13 Apr 2023 21:25:28 CEST] INFO service xe connected in 1.06s 282 283 OK, service XE.LOCAL reachable 284 285 286 # use TNSCLI_USER/TNSCLI_PASSWORD variables for real login checks 287 >export TNSCLI_PASSWORD=supersecret 288 >tnscli check xe -f test/testdata/connect.ora --user system --info 289 [Thu, 13 Apr 2023 21:23:37 CEST] INFO use entry 290 XE.LOCAL=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=21521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XEPDB1))) 291 292 293 [Thu, 13 Apr 2023 21:23:37 CEST] INFO service xe connected using user 'system' in 69ms 294 295 OK, service XE.LOCAL reachable 296 297 # find host, CDB and PDB to the givven service 298 # this needs a proper login to the DB via --user/--password or TNSCLI_USER/TNSCLI_PASSWORD 299 >tnscli check -H -A test/testdata XEPDB1.local 300 XEPDB1.local -> localhost:XE:XEPDB1 301 302 # write tnsnames.ora to ldap server (openldap with oid* schema extensions), all parameters via commandline 303 >tnscli ldap write \ 304 --ldap.host=127.0.0.1 \ 305 --ldap.port=1636 -T -I \ 306 --ldap.base="dc=oracle,dc=local" \ 307 --ldap.oraclectx="dc=oracle,dc=local" \ 308 --ldap.binddn="cn=admin,dc=oracle,dc=local" \ 309 --ldap.bindpassword=admin \ 310 --ldap.timeout=20 \ 311 --ldap.tnssource test/testdata/ldap_file_write.ora 312 Finished successfully. For details run with --info or --debug 313 314 #read tnsnames.ora from ldap server with parameter via yaml and password via env 315 >export TNSCLI_LDAP_BINDPASSWORD=admin 316 >tnscli ldap read -T -I -c test/tnscli.yaml -A test/testdata 317 [Tue, 23 May 2023 17:23:27 CEST] INFO Return 2 TNS Ldap Entries 318 XE.LOCAL= 319 (DESCRIPTION = 320 (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))) 321 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = XE)) 322 ) 323 324 XE2.LOCAL= 325 (DESCRIPTION = 326 (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))) 327 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = XE2)) 328 ) 329 330 [Tue, 23 May 2023 17:23:27 CEST] INFO SUCCESS: 2 LDAP entries found 331 332 333 ``` 334 335 ## tnscli addon scripts 336 337 there are some additional helper scripts available in `/scripts` 338 339 ### dbhost 340 `dbhost` is a shortcut for `tnscli service check <service> --dbhost` command. it tries to connect to the given service using default user (or set TNSCLI_USER and TNSCLI_PASSWORD) 341 to get host:cdb:pdb from oracle session sys_context 342 ```bash 343 >export TNSCLI_USER="c##tcheck" # or 344 # export TNSCLI_USER="tcheck" 345 >export TNSCLI_PASSWORD="<MyCheckPassword>" 346 >dbhost MYPDB1 347 348 racnode1:MYCDB:PDB1 349 ``` 350 ### gotodb 351 gotodb use dbhost to extract the server hostname from the connection and raises an ssh command to this host. 352 Make sure both, tnscli and dbhost can be found in path 353 If the returned hostname is not a valid dns name you may use of `.ssh/config` to match host user and ssh key 354 ``` 355 host racnode1 356 HostName racnode1.rac.lan 357 User oracle 358 IdentityFile ~/.ssh/id_ora 359 ``` 360 ```bash 361 >gotodb MYPDB1 362 oracle@racnode1 ~> 363 ``` 364 ### tnslookup 365 366 `tnslookup` is a shortcut for the `tnscli list --search <service> --complete` command 367 ```bash 368 >tnslookup mypdb1 369 370 Location: /etc/oracle/tnsnames.ora Line: 6 371 MYPDB1.LOCAL= (DESCRIPTION = 372 (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=myrac.rac.lan)(PORT=1521))) 373 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME = PDB1)) 374 ) 375 ``` 376 ## Virus Warnings 377 378 some engines are reporting a virus in the binaries. This is a false positive. You may check the binaries with meta engines such as [virustotal.com](https://www.virustotal.com/gui/home/upload) or build your own binary from source. I have no glue why this happens. 379