No-one under a certain age (or outside of the UK for that matter) will remember the often disturbing TV series 'tales of the unexpected' https://en.wikipedia.org/wiki/Tales_of_the_Unexpected_(TV_series). I don't know why i mention it now really other than as a tenous link to a very basic demonstration of the expect (/usr/bin/expect) unix utility that has been around for a very long time and can actually prove very useful - here is an example of what expect can do for you.
This is a (very) basic example of a script that can be used to solve a specific problem with invalid username/passwords or expired accounts.
Take the example from this technet question https://community.oracle.com/thread/3949964 - in this case when the exp utility is called from a shell script all is fine unless there is some problem with the account - in which case exp just hangs forever awaiting some input that is never going to come.
sqlplus can be worked around by use of the -L option to allow only a single logon attempt but exp does not have this.
So what to do?
This is where expect comes in.
First of all i create a very simple shell script which will do an export of the system schema - this script just contains the following few lines
export ORACLE_SID=DBNAME
export ORAENV_ASK=NO
. oraenv
exp / owner=system
In the normal case where the account is OK this runs to completion - if the username has some issue - for example invalid username/password then we see this
#./test.sh
The Oracle base remains unchanged with value /oracle
Export: Release 12.1.0.2.0 - Production on Thu Jul 14 17:19:23 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username:
When this is a batch script the script would just hang forever which could be a problem - lets see how expect can solve this for us.
I create a new script file but this time using the expect script
#!/usr/bin/expect
spawn ./test.sh
send "dummy\r"
send "dummy\r"
send "dummy\r"
send "dummy\r"
interact
This again is a very simple script that just runs test.sh and then sends in 4 inputs into the running program - in the event that we login ok we just get the word dummy appear a few times in the output which we can ignore.
In the event the initial login fails then dummy is passed in as inputs to the login prompt - these logins then fail and the additional 2 attempts that fail then return control to the shell and we get out of the hung state - then we just need to check back in the logfile for any ORA- errors to see if the script worked ok - neat eh?
So an example output when we use an invalid login is this
./rich.expect
spawn ./test.sh
dummy
dummy
dummy
dummy
The Oracle base remains unchanged with value /oracle
Export: Release 12.1.0.2.0 - Production on Thu Jul 14 17:21:41 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: Password:
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
Username: Password:
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username/password; logon denied
EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully
When it is a valid login we see this
./rich.expect
spawn ./test.sh
dummy
dummy
dummy
dummy
The Oracle base remains unchanged with value /oracle
Export: Release 12.1.0.2.0 - Production on Thu Jul 14 17:24:37 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P9 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SYSTEM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SYSTEM
About to export SYSTEM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL
etc
etc
Neat huh - expect can do way more than just this example but it shows you at least the very basics.
Comments
Post a Comment