Batch file for FTP

April 5, 2017

Hi guys

We’ve a legacy application for our Bahrain operations and after a disastrous hardware failure we wanted to find a cost effective method to bring the database dumps to Kuwait for regular health checks.

Options in front of us were pretty limited. The business in Bahrain only had a basic internet connection(dial-up DSL) without static IP address, hence we were forced to workout something that is dependable. Finally we decided to FTP the dump file in compressed form after the dump export every Friday once after realizing that the total size of the compressed dump file was less than 100MB in size!

Here is the script file We’ve compiled after referring a number of blogs/tech forum visits. Please note, the methods we implement here uses passwords seen in clear text. Hence make sure that you take maximum attention while implementing our solution in your environments.

::http://blogs.msdn.com/b/oldnewthing/archive/2012/08/01/10334557.aspx
@echo off
SETLOCAL
::Get the latest dump file name, generated using exp command
for /f "tokens=*" %%a in ('dir *.dmp /o:-d /b') do set NEWEST=%%a&& goto :next

:next
REM echo The most recently created file is %NEWEST%
::http://stackoverflow.com/questions/15567809/batch-extract-path-and-filename-from-a-variable
FOR %%i IN ("%NEWEST%") DO (
REM ECHO filedrive=%%~di
REM ECHO filepath=%%~pi
SET ZIPNAME=%%~ni
REM ECHO fileextension=%%~xi
)

SET ZIPNAME=%ZIPNAME%.zip
::Creating ZIP file using Java Runtime
::Dump files could be huge and take hours to transfer in full size. So we will use Java RunTime Executable to create a zip file which
::Will be smaller in size
::Make sure you have JRE installed, and the installation path is set in the environment variables, if not call JRE using the full path
::eg: C:\java\jre
echo Creating Zip file for transfer, filename ""%ZIPNAME%""
::We will create the zip file without meta-info, hence the M switch is used
::jar -cfM %ZIPNAME% %NEWEST%
::Another option is to go with 7Zip, 3rd party utility that could create zip/7z files
::7z a %ZIPNAME% %NEWEST%
::Or using Windows built-in Zip utility
zip %ZIPNAME% %NEWEST%
::You can use IP address or fully qualified domain names for the FTP server
::If the FQDN is not registered, you can add an entry with your hosts file
::FTP accepts parameter files, ftp -s:parameter file, so we will create the parameter during the initial running
::Please note, the parameter file created is permanent & clear text format, hence make sure that adequate security measures are practiced
::to avoid getting sensitive details exposed.
echo open ftpservername(FQDN/IP)>ftp.txt
echo username>>ftp.txt
echo password>>ftp.txt
echo binary>>ftp.txt
echo put %ZIPNAME%>>ftp.txt
echo disconnect>>ftp.txt
echo quit>>ftp.txt

REM echo put filename2.dat >>ftp.txt
REM echo put filename3.dat >>ftp.txt
REM echo put filename4.dat >>ftp.txt
::We referred the following thread for making the batch file
::Google search keywords:  pass parameter to ftp script
::http://stackoverflow.com/questions/5170627/is-it-possible-to-pass-a-variable-into-a-windows-ftp-script-file

ftp -s:ftp.txt
::Delete the zip file after transfer comples
del %ZIPNAME%

::Optionally you can use "exit" the command windows-ftp-script-file
::Exit

and usually the ftp.txt should look like below

open ftp.myserver.com
username
password
binary
put exp_10032017.zip
disconnect
quit

regards,

rajesh


Mapping Network drives in Weblogic (Windows)

March 3, 2017

Atheek's Blog

In situations where you need to talk to a remote file system from OSB running in Windows machines, you need to map the remote file system to a local drive letter. This scenario caters for two requirements.

1. When you have your OSB cluster spanning multiple servers and all servers need access to a common file (for read only purpose), it is better to have this file sit in a file share accessible by all machines. An example is when using OSB to connect to MQ as foreign JMS, the .bindings file containing the remote JNDI tree for MQ objects can be placed in a file share.

2. In File integrations where you need OSB to read from or write to an external file system.

OSB’s file transport doesn’t support UNC format names for accessing files. Hence mapping to a drive letter is mandatory. This can be done in at-least…

View original post 424 more words


Windows | ORA-12560: TNS:protocol adapter error

February 25, 2017

Hi guys

Not many DBAs prefer Windows for their Oracle databases. Linux is most preferred by most of them & most of the DBAs I know setup the bash profile under Oracle user to setup the environment during each logon to the server.

Our legacy business application database runs on Windows 2003 & trust me, we never had a single database crash (Other than the physical hardware failure that forced us to recover the database once). Depending upon how huge the database and application, the choices for hosting the Oracle database differ from one business to other.

We decided to upgrade our Oracle 10g 10.1.x.x 32Bit database to 11g R2 & as usual I have replicated the environment using my home semi-server class desktop, before the Production environment at work.

Installed 10g 32Bit, created the database using dump export file (The total size of the database is less than 7GB, hence I avoided the hectic RMAN backup and restore part)

  1. Configured RMAN against the new database & made full backup for archive logs and database.
  2. Installed 11g 11.2.0.4 64Bit database (Software Only installation)
  3. Created a new Windows Service using oradim
  4. Restored the database from RMAN backups & upgraded the database to 11g

So far so good. I had to restart the computer & after rechecking the database was up and running, tried to access the instance using sqlplus & was presented with

ORA-12560: TNS:protocol adapter error

REG_SID_MISSING

I setup ORACLE_SID=SID at the CMD window & sqlplus was happy after that.

Usually, Windows doesn’t need environment variables set exclusively for the database as Windows registry takes care of it. This is very efficient when the box has only one database running. If you have more than one database or multiple Oracle homes, the scenario changes.

In addition to, Oracle always looks for the executable based on the PATH information it reads. For example my box has 10g,11g,12c database software installed without any databases created during the installation time.

Let us consider the scenario like I didn’t re-order the PATH entries after the latest installation of 12c & try to open SQL or RMAN. The call will find the executable from 12c path entry BIN as default, and a beginner could have enough confusions due to it.

In my case, I needed my 10g instance first, hence I moved the 10g folder as the 1st entry for Oracle products, and once I finished with 10g moved 11g home folder to the 1st position.

SID_Missing

Anyway, after confirming the path settings, my immediate attention was towards registry, as Oracle services completely depend upon the registry values for each service registered.

To my utter surprise, found the 11g Service entry didn’t have ORACLE_SID string created during the instance creation using ORADIM.exe

REG_SID_MISSING

Oracle 11g 11.2.0.4 has a huge bug list and interim patches those should be applied before moving to Production instance. I really don’t know whether the missing ORACLE_SID string entry was due to one of such bugs.

So I stopped the Oracle service, added ORACLE_SID string entry with the value for my database

REG_SID_ADD

Restarted the service & sqlplus connected to the instance happily without setting up the environment variable like set ORACLE_SID=SIDNAME

REG_SID_ADDED

While the easiest solution is to setup both ORACLE_HOME, ORACLE_SID when someone wants to use the sqlplus or RMAN exclusively as a part of the database access, the above method is a definite way to deal with “ORA-12560: TNS:protocol adapter error”

regards,

rajesh


ORA-12546: TNS: permission denied

February 13, 2017

Update: 16th Feb 2017

Sorry folks, I’ve given upon 10.2.0.4 & moved to 10.2.0.5 patch upgrade that resolves the intermittent service startup issues of Oracle on Windows OS (Uncertified OS like Windows 10/2008R2)

I’m sure, if at all needed an upgrade, your DBA will choose a later version of Oracle database, when a client server architecture may not be flexible with Oracle 12c. Please make sure you try the upgrade using a virtual machine, spend some time analyzing logs and if you feel, it is worth taking a risk for the production, do the production patching using 10.2.0.5 patch. You may have to drop and recreate the Enterprise Manager repository (if you are using it at all)

Update: 14th Feb 2017

A right day to present something that’s kinda disappointing? :P Well, Even though I managed to get stuffs running, after a reboot over night, realized that the “net start Oracle serviceName” in consequent attempts brings back the dreaded “ORA-12546: TNS: permission denied” error message logged in oradim.log

1

forco

the only one logic I could reach to this intermittent issues with starting service is, read & execute permissions on Oracle executables are not being triggered always. Finish!

My further digging using “Dependency walker”  for both Oracle.exe & TNSLSNR.exe has exposed that both the executable were making reference to Microsoft APIs those are depreciated!

2

Finally: I’m doing a hack install of 10.2.0.3 on Windows 10. Updating the instance using 10.2.0.4 patch & expecting a smooth sail. I’m totally wrong, I can test it, hope it works (not always) & I shouldn’t ever attempt the same in a Production environment.

Positive thing: Once the bin folder has correct permissions & setting up the Oracle services to start automatically, the service starts without any issues and shuts down gracefully when Windows is shut down.

 

Hi guys

I’m in the midst of compiling a huge post about RMAN backup & restore for NOT DBAs & at the same time trying to fix “ORA-12546: TNS: permission denied” errors I was getting from my 10.2.0.3 upgraded to 10.2.0.4 (32Bit) on a Windows 10 virtual machine using Oracle VirtulBox (My RMAN backup was restored from 10.2.0.3 Production to this upgraded instance)

My scenario:

Windows Service for Oracle Service is set as manual. After a reboot, I try to start the service manually. Sometimes it starts, some other time I have start/stop/start/stop a number of times until the database gets started. I asked google to give me tip & found a post answered by Tom (AskTom) bombarded with questions about “ORA-12546: TNS: permission denied” after machine restarts & there were no real solutions. Few of them even claimed that they managed to start the Oracle service by turning off the firewall(?)

Then I asked google another question “fix ORA-12546: TNS: permission denied” & landed on http://www.dba-oracle.com/t_ora_12546_tns_permission_denied.htm & from that page to http://www.dba-oracle.com/security/removing_permissions.htm

Basically the 2nd page is purely for Linux, and if you are familiar with file permissions, easy to translate for Windows

I browsed the bin folder & realized that, even though the SERVER\Administrator account has full access to the oracle.exe executable, somehow my user “rajesh” who is the default administrator was not inheriting the execute right on the same.

So

I gave myself full rights on the executable and tried to restart the service & without making any complaints the Oracle database Service started and the database was opened. I was having a look at both alert log and oradim log files to insure that “ORA-12546: TNS: permission denied” not being appearing once again.

1

Then I gave my user account rajesh full rights on the bin folder (Over reaction) to make sure that I am not going to get hammered with the dreaded, without much explanations available “ORA-12546: TNS: permission denied” error once again.

2

I think during the upgrade process much of the file permissions were changed as part of the patching process & eventually the permissions were not reset to the actuals. This issue could happen with any Oracle upgrade processes. So if you are that another unlucky one, please give the above hack a try.

 

regards,

rajesh


EM dbconsole not started and giving NET HELPMSG 3547

February 1, 2017

Hi guys,

I had a nightmare after Oracle 10g 10.2.0.3 upgraded to 10.2.0.4. While almost everything else went smooth as expected, the enterprise manager (that we don’t use at all actually) will not start. Spent around 48 hours & more to find a solution and performed almost all suggestions including patching the database with SSL patch provided by Oracle. Finally I landed on the following page to resolve the issues. A minor difference was, I hadn’t had to reload the listener as mentioned with the original post.

Hi Friends, today i face some problem with EM dbconsole in one of my databases are here is the way how i resolved it Database version : 10.2.0.4 standard edition OS version : windows 2003 server wi…

Source: EM dbconsole not started and giving NET HELPMSG 3547


Changing sysman and dbsnmp passwords – It ain’t that easy

February 1, 2017

The Tendjee

And of course it’s not that easy!

Actually this is quite annoying.

I have 2 concerns: Having my backups working properly as well as the DB Console.

But also renew my passwords from time to time.

And here we are sys and system’s passwords are luckily easy to change.

But what about sysman and dbsnmp?

Those are not. Try to change it and you will have your backups stop working, OEM throws in tons of errors and soon you will be asking yourself why the hell you wanted to change those passwords in the first place.

The reason

Sysman user is the schema of the standalone repository of the 10g EM DB Control.

If you have change only dbsnmp password your backups will work and the DB console as well but all the cool graphics that I like to show to our customers will disappear and that’s not cool.
There…

View original post 489 more words


C# A folder sync tool

January 26, 2017

Hi guys!

I’ve been busy with so many developments recently. Few projects for Oracle E-Business Suite R12 & few others for learning C#. I can say I’ve “learned” how to copy, paste, correct and use it with my projects to produce beautiful results!

My recent C# projects include a WPF solution that uses WMI classes to generate hardware details for entire Windows Domain (Linux boxes are not dealt) & today I’ve decided to build a small solution that could synchronize my C# projects with our file server.

This small project can,

  • Replicate source Directory, subdirectories & files to destination folder
  • While re-ran, check whether the destination files are older than the source files and, if yes, overwrite the old ones with newer ones.

Interested to try it yourself? here comes the code

(This project uses codes copied from both MSDN & http://www.stackoverflow.com. RecursiveFileProcessor is a MSDN example, while File exists snippet was copied from stackoverflow)

using System;
using System.IO;
using System.Collections;

//http://stackoverflow.com/questions/58744/copy-the-entire-contents-of-a-directory-in-c-sharp
//

namespace RecursiveFileProcessor
{
    class Program
    {
        public static void Main(string[] args)
        {
            string sourceDirectory = @"D:\MyProjects";
            string targetDirectory = @"\\network_share_location\MyProjects";
            Copy(sourceDirectory, targetDirectory);
            Console.WriteLine();
            Console.WriteLine("Task Completed. Press any key to continue");
            Console.ReadLine();

        }

        public static void Copy(string sourceDirectory, string targetDirectory)
        {
            DirectoryInfo diSource = new DirectoryInfo(sourceDirectory);
            DirectoryInfo diTarget = new DirectoryInfo(targetDirectory);

            CopyAll(diSource, diTarget);
        }

        public static void CopyAll(DirectoryInfo source, DirectoryInfo target)
        {

            Directory.CreateDirectory(target.FullName);

            // Copy each file into the new directory.
            foreach (FileInfo fi in source.GetFiles())
            {

                DateTime created = fi.CreationTime;
                DateTime lastmodified = fi.LastWriteTime;

                if (File.Exists(Path.Combine(target.FullName, fi.Name)))
                {
                    string tFileName = Path.Combine(target.FullName, fi.Name);
                    FileInfo f2 = new FileInfo(tFileName);
                    DateTime lm = f2.LastWriteTime;
                    Console.WriteLine(@"File {0}\{1} Already Exist {2} last modified {3}", target.FullName, fi.Name, tFileName, lm);

                    try
                    {
                        if (lastmodified > lm)
                        {
                            Console.WriteLine(@"Source file {0}\{1} last modified {2} is newer than the target file {3}\{4} last modified {5}",
                                fi.DirectoryName, fi.Name, lastmodified.ToString(), target.FullName, fi.Name, lm.ToString());
                            fi.CopyTo(Path.Combine(target.FullName, fi.Name), true);
                        }
                        else
                        {
                            Console.WriteLine(@"Destination File {0}\{1} Skipped", target.FullName, fi.Name);
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }

                }
                else
                {
                    Console.WriteLine(@"Copying {0}\{1}", target.FullName, fi.Name);
                    fi.CopyTo(Path.Combine(target.FullName, fi.Name), true);
                }

            }

            // Copy each subdirectory using recursion.
            foreach (DirectoryInfo diSourceSubDir in source.GetDirectories())
            {
                DirectoryInfo nextTargetSubDir =
                    target.CreateSubdirectory(diSourceSubDir.Name);
                CopyAll(diSourceSubDir, nextTargetSubDir);
            }
        }

    }
}

Thumb rules:

  1. Do not set your source and destination folders the same.

This project uses Framework 4.5 & I am sure versions above 2.0 should support the classes referred.

If you are new to .net developments, create a project with the name “RecursiveFileProcessor”, which will let you copy and paste the whole code as it is to your project.

Do let me know whether you enjoyed your new sync toy!

regards,

rajesh