Architecting, installing and maintaining your SAS environment

SAS Oracle connection error

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 126
Accepted Solution

SAS Oracle connection error

Hello Everyone

 

We are getting below sas oracle error error after upgraded into sas9.4 M4. 

 

 

 

ERROR: Unable to connect to Oracle-Oracle environment is either not set or set incorrectly!
ERROR: Error in the LIBNAME statement.

 

We have defined the below entries into the local_env file: - 

 

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2./client64/lib:/usr/lib64
export LD_LIBRARY_PATH

ORACLE_HOME=/usr/lib/oracle/11.2.
export ORACLE_HOME

TNS_ADMIN=/etc/opt/oracle
export TNS_ADMIN

 


Accepted Solutions
Solution
‎06-16-2017 09:08 AM
SAS Super FREQ
Posts: 291

Re: SAS Oracle connection error

Hi there,

 

as Kurt mentioned, you must make sure that your IT folks/Sys admins have a proper backup

in place that includes the SAS environment.

 

Some documentation and best practices for backups:

 

About Backup and Restores

 

Best Practices for Backing Up your SAS Content

 

Best Practices for Restoring your SAS Content

 

It is important that the system backup or third party backup you are using includes SAS.

Some background why this is so important:

 

The Metadata Server that stores all your metadata (users, groups, libraries, tables, web content,

folders etc.) is an in-memory server. When users request metadata, a copy of the physical table

is created and "put" into memory. It remains memory until the metadata server is paused and

resumed, or stopped and restarted. The pausing and or stopping flushes the content that

is held in mem, to disk. This is key!!

If you only run a sys backup without considering SAS, all data on disk is backed up,

however, what is kept in memory will not be part of the backup.

 

Think of it as a puzzle: you have a complete puzzle, and then you take some puzzle

pieces out and throw them away. You will never be able to put the puzzle back togther.

 

Same concept with the metadata server.

If you backup without considering SAS, whatever is kept in memory will not be part of the

backup. If a restore would become necessary, it would be incomplete, and with that, not

usable. Data would be lost.

 

The SAS backup facility (the different options are described in the doc mentioned above) runs an

automatic backup each night at 1pm. The SAS backup pauses the metadata server automatically,

runs the backup, and then resumes the metadata server. The pause flushes the content that

is kept mem to disk.

 

If you are running jobs at night, or, if you do run a backup during the day and users run jobs,

already submitted jobs are put on hold until the backup ran, then continue.

New jobs that are submitted are being "refused" until the backup finishes.

A SAS backup usually doesnt run very long. Of course this depends on the amount/size of your

metadata, but generally, it would not effect any work too much.

To use another analogy to explain users running jobs during a backup, the process of jobs being put

on hold or being "refused" during a SAS backup is like a phone call.

If you talk to someone and then put the person on hold, (s)he is still on, yet paused.

If a third person now tries to call you, its busy, and the call cannot be accepted. However, ones you

end the phone call, you can then be reached again. Same concept.

 

To summarize the above:

It is of the utmost important to have a proper SAS backup in place to make sure you can restore

successfully if it would ever become necessary.

 

Having seen many cases where data was lost, I cannot stress this enough.

 

Whenever you install a hot fix, a maintenance release, and whenever you make any changes to

your environment (significant changes), you can run an adhoc backup. Please take a look

at SAS Backup Facility

 

Hope this helps.

Please let me know if you have any questions.

 

Best,

Anja

View solution in original post


All Replies
Regular Contributor
Posts: 170

Re: SAS Oracle connection error

Double-check your env_local file. I just did my M4 upgrade and found that it overwrote my env_local file with a default one, erasing all the custom configurations I previously had.
Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

I have defined my parameters in level_env.sh and this was overwritten.

 

Is there any way to recover this file.

 

Regular Contributor
Posts: 170

Re: SAS Oracle connection error

I'm pretty sure they are supposed to be defined in {sashome}/SASFoundation/9.4/bin/sasenv_local
Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

can you please share your parameter details defined for Oracle

 

Regular Contributor
Posts: 170

Re: SAS Oracle connection error

Here you go:

#ORACLE

export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/client
export ORACLE_VERSION=11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

I am still getting the same error, I am able to connect oracle via sqlplus.

Regular Contributor
Posts: 170

Re: SAS Oracle connection error

Is your LD_LIBRARY_PATH correct?  Shouldn't 11.2. be 11.2.0 or at least not end with a "."? See red text...

 

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2./client64/lib:/usr/lib64

 

Also, did you add this to "sasenv_local" under /SASFoundation/9.4/bin?

Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

11.2. is the correct path. I also added the parameters in sasenv_local.sh, but still getting the same error.

Regular Contributor
Posts: 170

Re: SAS Oracle connection error

That's all I have for now. Sorry
SAS Super FREQ
Posts: 291

Re: SAS Oracle connection error

Hi there,

 

maybe a good next step would be addressing this with Tech Sup?

You got some great suggestions and info, which you could add to the TS track if you'd open one,

then TS knows what has been checked so far and troubleshooting will be much easier.

 

Best

Anja

Super User
Posts: 6,928

Re: SAS Oracle connection error


japsas100 wrote:

I have defined my parameters in level_env.sh and this was overwritten.

 

Is there any way to recover this file.

 


Restore it (or have it restored) from the backup. That's what backups are for.

This is why it is STRONGLY recommended to have a backup from immediately before you do ANY kind of application upgrade. Doing an upgrade without a current backup is gross neglicence.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

We already fixed the issued by creating a correct symbolic link for sas -> bin/sas_en.

 

Which tool is used to restore the backup files?

Super User
Posts: 6,928

Re: SAS Oracle connection error


japsas100 wrote:

We already fixed the issued by creating a correct symbolic link for sas -> bin/sas_en.

 

Which tool is used to restore the backup files?



If you haven't set up the backup yourself, ask the system admin for the SAS server.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 126

Re: SAS Oracle connection error

ok...

Just for knowledge which tool we normally use for backup during sas upgrade and how we restore it back in case any major issue.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 297 views
  • 0 likes
  • 4 in conversation