Re: SC multiple Oracle databases in single server

luiz_silva · ‎2012-01-06

Hi,

When I try to config one single profile (SC 3.4) with two or more Oracles databases (11g), I'm facing the following problem when I run the backup:

alter database begin backup

*

ERROR at line 1:

ORA-01146: cannot start online backup - file 1 is already in backup

ORA-01110: data file 1: 'D:\DEMO3\SYSTEM01.DBF'

In this configuration there are two Oracle databases in the same profile (VM + Aplication Consistent). Both databases share the same Oracle Home directory. (this is a VMware VM housed alone in a NFS datastore).

I think the first "begin backup" command put all databases in backup mode, thus further "begin backup" will fail.

Due a lack of information, I don't know how to config this profile properly.

Here my config (both were built through GUI):

(DB #1)

ORACLE_DATABASES=demo2:sys / as sysdba

SQLPLUS_CMD=C:\app\Administrator\product\11.2.0\dbhome_1\BIN\sqlplus.exe

CNTL_FILE_BACKUP_DIR=d:\demo2

ORA_TEMP=d:\demo2

ARCHIVE_LOG_ONLY=N

ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1

ORACLE_EXPORT_PARAMETERS=Y

(DB #2)

ORACLE_DATABASES=demo3:sys / as sysdba

SQLPLUS_CMD=C:\app\Administrator\product\11.2.0\dbhome_1\BIN\sqlplus.exe

CNTL_FILE_BACKUP_DIR=d:\demo3

ORA_TEMP=d:\demo3

ARCHIVE_LOG_ONLY=N

ORACLE_HOME=C:\app\Administrator\product\11.2.0\dbhome_1

ORACLE_EXPORT_PARAMETERS=Y

Do you have any suggestion?

Thanks!!!

ktenzer · ‎2012-01-06

The ORACLE_DATABASES setting is not correct

We dont want an oracle user we want an OS user that has access to oracle DB. For example on unix this is usually oracle. On windows it could be administrator, it depends on your setup.

Basically do following from command prompt

sqlplus / as sysdba

If you get connection to Oracle that that OS user has permissions. Assuming OS user is administrator you would do following:

ORACLE_DATABASES=demo2:administrator

and

ORACLE_DATABASES=demo3:administrator

Also for windows ORACLE_EXPORT_PARAMETERS wont do anything and probably should be set to N, that is mostly for UNIX systems where we see many DBs on one host, usually windows is one DB per host. On windows we require that ORACLE_HOME and ORACLE_SID are setup.

Again to verify open command shell

echo %ORACLE_HOME%

echo %ORACLE_SID%

If these are not set then they should be, so please add them to environment

Let us know if this helps

Regards,

Keith

luiz_silva · ‎2012-01-09

Thanks, Keith.

I tried your suggestion, but there is one point that is not clear for me: what "ORACLE_SID" parameter is valid if there are two databases in the same host? I just put the first DB name.

The ORACLE_HOME parameter now was corrected in my environment and I put "administrator" as you suggested, but didn't change the behaviour.

I think the second command below will always fail if the first line issues "alter database begin backup" first. I analyzed the error log from the second command below and I see "alter database begin backup" has failed ( file 1 is already in backup).

APP_QUIESCE_CMD01="C:/Program Files/Netapp/NetApp_Snap_Creator_Framework/scServer3.4.0/snapcreator.exe" --profile oracle_vm_2i --config oracle_vm_2i_oracle_1 --action quiesce (first DB - no errors)

APP_QUIESCE_CMD02="C:/Program Files/Netapp/NetApp_Snap_Creator_Framework/scServer3.4.0/snapcreator.exe" --profile oracle_vm_2i --config oracle_vm_2i_oracle_2 --action quiesce (second DB - I got the error "ORA-01146: cannot start online backup - file 1 is already in backup"

APP_QUIESCE_CMD03="C:/Program Files/Netapp/NetApp_Snap_Creator_Framework/scServer3.4.0/snapcreator.exe" --profile oracle_vm_2i --config oracle_vm_2i_vibe --action quiesce (didn't run due error above)

Is there a trick for this kind of configuration?

Thanks in advanced.

ktenzer · ‎2012-01-09

Two databases on same host is a bit tricky on windows.

As I mentioned SC won't export oracle params on windows like it does on unix.

If you have multiple DBs on windows simply create a wrapper script .bat script which sets ORACLE_HOME and ORACLE_SID and then calls SC. Something like:

set ORACLE_HOME c:\foo

set ORACLE_SID bar

C:\bla\snapcreator.exe

Just make sure set command works, you can verify by doing echo %ORACLE_SID% and echo %ORACLE_HOME%. SC requires these to be set since we are using "sqlplus / as sysdba" to connect to oracle db.

Regards,

Keith

luiz_silva · ‎2012-01-09

Sorry, I forgot to mention that scServer (from where I issue all SC commands) and scAgent (where Oracle resides) are different machines so I can't make a .bat that sets ORACLE_HOME in scServer (that does not have Oracle installed). In the same way I can't issue SC commands from scAgent because there is no conf files there.

Sorry again, I'm not so expert in Oracle to tell what's happening. There are two databases named DEMO2 and DEMO3 and I dont know if I must use DEMO2 or DEMO3 for ORACLE_SID when is required. But IMHO I'm not pretty sure that is the root of the problem.

ktenzer · ‎2012-01-09

Why don't you just backup DEMO2 and DEMO3 separately?

Again all that is required is oracle_sid and oracle_home be set correctly and this must be done outside of SC.

If you have to back them up together only way is installing scServer on DB host and creating wrapper script so oracle_home and oracle_sid are set correctly or running two agents, one fir each DB. So when you start agent you could set oracle_home and oracle_sid then through wrapper.

The problem right now is DEMO2 is set as oracle_sid so quiescing it works but when we do DEMO3, DEMO2 is set as oracle_sid. SC supports no way of changing this so you have to handle it on your own.

Regards,

Keith

PINYOUUDK · ‎2012-04-05

How to create the environment .bat script and which SC method use for call the script? or just input parameter in SC configuration?

ktenzer · ‎2012-04-05

Not sure you are providing enough info to get an answer? Please explain what you are trying to do

Keith

PINYOUUDK · ‎2012-04-10

Keith,

i'm try to configure SnapCreator to perform hotbackup of Oracle database. For Solaris client platform it work well but not on microsoft windows platform. My windows has only 1 oracle database instance. Below is message that i got all time when i execute quiesce operation

########## Application quiesce ##########

[Tue Apr 10 00:49:48 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] INFO: Quiescing databases

[Tue Apr 10 00:49:48 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] INFO: Quiescing database SRS

[Tue Apr 10 00:49:48 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] DEBUG: Verifying correct version of database SRS

[Tue Apr 10 00:49:48 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] DEBUG: Executing SQL sequence:

connect / as sysdba;

select * from v$version;

exit;

[Tue Apr 10 00:49:48 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] DEBUG: Executing external sql script [C:\Windows\TEMP\9mMLjJpcFr.sc] for database SRS

[Tue Apr 10 00:49:49 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] DEBUG: Command [D:\oracle\SRS\11202\BIN\sqlplus /nolog @C:\Windows\TEMP\9mMLjJpcFr.sc] finish

exit code: [0]

stdout: [

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 00:49:49 2012

ERROR:

ORA-12560: TNS:protocol adapter error

SP2-0640: Not connected

]

stderr: []

[Tue Apr 10 00:49:49 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] ERROR: [ora-00020] Oracle SQL*Plus command [D:\oracle\SRS\11202\BIN\sqlplus /nolog @C:\Wind

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 00:49:49 2012

ERROR:

ORA-12560: TNS:protocol adapter error

SP2-0640: Not connected

]

[Tue Apr 10 00:49:49 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] DEBUG: Executing external sql script [C:\Windows\TEMP\9mMLjJpcFr.sc] for database SRS finis

[Tue Apr 10 00:49:49 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] ERROR: [ora-00001] Verifying correct version of database SRS failed

[Tue Apr 10 00:50:03 2012] [ep-sr-sbx-test-snap.xyzep.com:9090(3.4.0.1)] ERROR: [scf-00053] Application quiesce for plugin oracle failed with exit code 1, Exiting!

Pinyo U.

ktenzer · ‎2012-04-10

Hi Pinyo,

This is an Oracle error

ERROR:

ORA-12560: TNS:protocol adapter error

What is required is that ORACLE_HOME and ORACLE_SID are set in your ENV paramters. Do this to check:

echo %ORACLE_HOME%

echo %ORACLE_SID%

Next try to connect to database: sqlplus / as sysdba, does this work? Can you connect to database?

if both are set then start agent, first manually to troubleshoot

snapcreator-exe --start-agent 9090 --verbose --debug

Next run backup and look at agent console to troubleshoot further. The key is finding out why we are getting a TNS error?

Regards,

Keith

PINYOUUDK · ‎2012-04-10

Keith,

Yes, the error gone after both ENV were added on my windows as you suggest. Thank a lot

Pinyo U.

ktenzer · ‎2012-04-10

Great news!

Thanks for getting back to me, it always makes my day when I can help a customer

Keith

mcorbeille · ‎2013-02-12

Keith,

I am running in to a similar issue as Pinyo. Instead of an OS user can I use a domain user that has permission to administer the DB?

Thanks,

Mike

sivar · ‎2013-02-12

Yes. The domain user that has permissions to run sqlplus command connect as sysdba will also work.

Please ensure the snapcreator service is also started as the same domain user, since the environment variables may differ if you run the snapcreator service in a different account. (like system or administrator).

Thanks,

Siva Ramanathan

mcorbeille · ‎2013-02-12

Thanks, so the conf file should look like this, correct? Do both the agent and server SC services need to run as the domain user or just the agent?

ORACLE_DATABASES=db:domain\user

sivar · ‎2013-02-12

Only the agent needs to run as the domain user, in this case.

Also, I believe you don't need to specify the domain, only the domainuser name is sufficient.

ORACLE_DATABASES=db:user

SC multiple Oracle databases in single server

Get ready to power on