topic Re: SAS Oracle connection error in Administration and Deployment

SAS Oracle connection error

japsas100 — Thu, 15 Jun 2017 13:38:05 GMT

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

Re: SAS Oracle connection error

Timmy2383 — Thu, 15 Jun 2017 13:42:21 GMT

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.

Re: SAS Oracle connection error

japsas100 — Thu, 15 Jun 2017 13:47:22 GMT

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

Is there any way to recover this file.

Re: SAS Oracle connection error

Timmy2383 — Thu, 15 Jun 2017 13:49:47 GMT

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

Re: SAS Oracle connection error

japsas100 — Thu, 15 Jun 2017 13:54:59 GMT

can you please share your parameter details defined for Oracle

Re: SAS Oracle connection error

Timmy2383 — Thu, 15 Jun 2017 13:58:22 GMT

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

Re: SAS Oracle connection error

japsas100 — Thu, 15 Jun 2017 14:20:57 GMT

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

Re: SAS Oracle connection error

Timmy2383 — Thu, 15 Jun 2017 14:31:35 GMT

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?

Re: SAS Oracle connection error

japsas100 — Thu, 15 Jun 2017 15:03:22 GMT

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

Re: SAS Oracle connection error

Timmy2383 — Thu, 15 Jun 2017 15:06:07 GMT

That's all I have for now. Sorry

Re: SAS Oracle connection error

anja — Thu, 15 Jun 2017 19:54:27 GMT

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

Re: SAS Oracle connection error

Kurt_Bremser — Fri, 16 Jun 2017 08:11:38 GMT

@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.

Re: SAS Oracle connection error

japsas100 — Fri, 16 Jun 2017 08:19:07 GMT

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?

Re: SAS Oracle connection error

Kurt_Bremser — Fri, 16 Jun 2017 08:20:05 GMT

@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.

Re: SAS Oracle connection error

japsas100 — Fri, 16 Jun 2017 08:26:19 GMT

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.

Re: SAS Oracle connection error

Kurt_Bremser — Fri, 16 Jun 2017 08:29:43 GMT

@japsas100 wrote:

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.

The tool that is used for backup on your server. Ask the system admin.

Re: SAS Oracle connection error

anja — Fri, 16 Jun 2017 12:52:13 GMT

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

Re: SAS Oracle connection error

japsas100 — Fri, 16 Jun 2017 13:26:52 GMT

Thanks Anja,

Just want to check how we open the Deployment Backup and Recovery tool?

When I am trying to open tool via https://hostname/SASBackupManager/BackupManager.jsp its keep running and tool not able to open.

Re: SAS Oracle connection error

anja — Fri, 16 Jun 2017 13:56:01 GMT

Hi,

It is an obvious question but would like to double check:

in the link you provided

https://hostname/SASBackupManager/BackupManager.jsp

did you modify the "hostname"?

You can get to the Deployment Backup and Recovery tool via SAS Environment Manager.

To see if it is configured/installed, you can take a look at the instructions.html file. This is

a file that's automatically created during SAS install/config. It is stored in the documents folder

underneath your SAS config dir (configdir .../Lev1/../Documents).

In there, you'll find an entry for SAS Environment Manager, including a link. Use this link

to start Env Mgr.

Once in the Env Manager, go to the Administration menu, from where you have access to the

Deployment Backup and Recovery tool.

Requirements for Env Mgr:

You have to be on SAS 9.4 and a middle tier has to be configured. The Env Mgr runs on the

middle tier.

If you do have a middle tier yet cannot find the Env Mgr in the instructions.html file, the best

approach then would be to contact Tech Sup.

Hope this helps.

Thanks

Anja