You want to package several database scripts into one file using the tar utility.

This first example uses the tar utility with the -cvf options to bundle all files ending with the string .sql that exist in the current working directory:

$ tar -cvf prodrel.tar *.sql

The -c (create) option specifies you are creating a tar file. The -v (verbose) option instructs tar to display the names of the files included in the tar file. The -f (file) option directly precedes the name of the tar archive file. The file that is created in this example is named prodrel.tar. It's standard to name the tar file with the extension of .tar.

If you want to include all files in a directory tree, then specify the directory name from where you want the tar utility to begin bundling. The following command bundles all files in the /orahome/scripts directory (and any files in its subdirectories):

$ tar -cvf script.tar /orahome/scripts

Both DBAs and system administrators ubiquitously use the tar utility to bundle a large number of files together as one file. Once files have been packaged together, they can be easily moved as a unit to another location such as a remote server (see Chapter 14 for details on copying files over the network).

The tar command has the following basic syntax:

$ tar one_mandatory_option [other non-mandatory options] [tar file] [other files]

When running tar, you can specify only one mandatory option, and it must appear first on the command line (before any other options). Table 6-1 describes the most commonly used mandatory options.

There are three methods for formatting options when running the tar command:

The short format uses a single hyphen (-) followed by single letters signifying the options. Most of the examples in this chapter use the short format. This format is preferred because there is minimal typing involved. Here's an example of using the short format to create an archive of all files in the /ora01 directory (and its subdirectories):

$ tar -cvf ora01.tar /ora01

The old format is similar to the short format except that it doesn't use the hyphen. Most versions of tar still support the old syntax for backward compatibility with older Linux/Unix distributions.

The mnemonic format uses the -- double hyphen format followed by a descriptive option word. This format has the advantage that it's easier to understand which options are being used. For example, the next line of code clearly shows that you're creating a tar file, using the verbose output, for all files in the /ora01 directory (and its subdirectories):

$ tar --create --verbose --file ora01.tar /ora01

The -f or --file option must come directly before the name of the tar file you want to create. You'll receive unexpected results if you specify the f option anywhere but just before the name of the tar file. Look carefully at this next line of code and subsequent error message:

$ tar -cfv prod_rel.tar *.sql
tar: prod_rel.tar: Cannot stat: No such file or directory

The previous line of code will attempt to create a file named v and put in it a file named prod_rel.tar along with files in the current working directory ending with the extension of *.sql.

If you want to compress the files as you archive them, then use the -z option (for gzip) or the -j option (for bzip2). The next example creates a compressed archive file of everything beneath the /oracle/product/10.2 directory:

$ tar -cvzf orahome.tar /oracle/product/10.2

Depending on the version of tar, the previous command might not add an extension such as .gz to the name of the archive file. In that case, you can specify the file name with a .gz extension when creating the file, or you can rename the file after it has been created.

If you're using a non-GNU version of tar, then you may not have the z or j compression options available. In this case, you'll have to explicitly pipe the output of tar to a compression utility like gzip:

$ tar -cvf - /oracle/product/10.2 | gzip > orahome.tar.gz

You can also use tar to copy a directory from one location to another on a box. This next example uses tar to copy the scripts directory tree to the /ora01/backup directory:

$ tar -cvf - scripts | (cd /ora01/backup; tar -xvf -)

The previous line of code needs a bit of explanation. The tar command uses standard input (signified with a - as the tar file name. This is piped to the next set of commands. The cd command changes directories to /ora01/backup and then extracts to standard output (signified with a -). This essentially gives you a method for copying directories from one location to another without having to create an intermediary tarball file.

You want to retrieve files from a bundled tar file.

Use the -x option to extract files from a tar file. Usually it's a good idea to first create a new directory and extract the files in the newly created directory. This way you don't mix up files that might already exist in a directory with files from the archive.

This example creates a directory and then copies the tar file into the directory before extracting it:

$ mkdir tarball
$ cd tarball
$ cp ../mytar.tar .
$ tar -xvf mytar.tar

The previous example retrieves all files from the mytar.tar file into the directory named tarball.

When extracting files, you can retrieve all files in the tar file, or you can provide a list of specific files to be retrieved. The following example extracts only two files from the tar file:

$ tar -xvf mytar.tar script1.sql script2.sql

You can also use pattern matching to retrieve files from a tar file. This example extracts all files that end in *.bsh from the tar file named mytar.tar:

$ tar -xvf mytar.tar *.bsh

If you don't specify any files to be extracted, then all files are retrieved:

$ tar -xvf mytar.tar

$ tar -cvf orahome.tar /home/oracle

$ cd /home
$ tar -cvf orahome.tar oracle

You wonder whether there have been any changes to files in a directory since you last created a tar file.

Use the -d (difference) option of the tar command to compare files in a tar file to files in a directory tree. The following example finds any differences between the tar file backup.tar and the scripts directory:

$ tar -df backup.tar scripts

The previous command will display any differences with the physical characteristics of any of the files. Here is some sample output:

scripts/top.sql: Mod time differs
scripts/top.sql: Size differs

If you find differences and want to update the tar file to make it current, then use the -u option. This feature will update and append any files that are different or have been modified since the tarball was created. The next line of code updates or appends to the tar file any changed or new files in the scripts directory:

$ tar -uvf backup.tar scripts

The output indicates that the top.sql has been updated:

scripts/
scripts/top.sql

You want to use cpio to bundle a set of files into one file.

When using cpio to bundle files, specify o (for out or create) and v (verbose). It's customary to name a bundled cpio file with the extension of .cpio. The following command takes the output of the ls command and pipes it to cpio, which creates a file named backup.cpio:

$ ls | cpio -ov > backup.cpio

If you want to bundle up a directory tree with all files and subdirectories, then use the find command on the target directory. The following pipes the output of the find command to cpio, which bundles all files and subdirectories beneath the current working directory:

$ find . -depth | cpio -ov > orahome.cpio

If you want to create a compressed file, then pipe the output of cpio to a compression utility like gzip:

$ find . -depth | cpio -ov | gzip > orahome.cpio.gz

The -depth option tells the find command to print the directory contents before the directory itself. This behavior is desirable especially when bundling files that are in directories with restricted permissions.

The key to understanding on how to package files with cpio is that it accepts as input a piped list of files from the output of commands such as ls or find. Here is the general syntax for using cpio to bundle files:

$ [find or ls command] | cpio -o[other options] > filename

For example, if you wanted to back up all files in the oracle account's home directory tree, then run cpio as follows:

$ find /home/oracle | cpio -ov > orahome.cpio

You can also specify that you want only those file names that match a certain pattern. This example bundles all SQL scripts in the scripts directory:

$ find scripts -name "*.sql" | cpio -ov > mysql.cpio

You just downloaded some Oracle software installation files. You notice that they are bundled as cpio files. You wonder how to retrieve files from the cpio archive.

Use cpio with the idmv options when unbundling a file. The i option instructs cpio to redirect input from an archive file. The d and m options are important because these instruct cpio to create directories and preserve file modification times, respectively. The v option specifies that the file names should be printed as they are extracted.

The following example first creates a directory to store the scripts before unbundling the cpio file:

$ mkdir Disk1
$ cd Disk1

After copying the archive file to the Disk1 directory, then use cpio to unpack the file:

$ cpio -idvm < linux10g_disk1.cpio

You can also pipe the output of the cat command to cpio as an alternative method to extract the file:

$ cat linux10g_disk1.cpio | cpio -idvm

You can also uncompress and unbundle files in one concatenated string of commands:

$ cat linux10g_disk1.cpio.gz | gunzip | cpio -idvm

The previous command allows you to easily uncompress and extract the Oracle distribution media.

The cpio utility is used with the i option to extract archive files. Here is the general syntax to unbundled files using cpio:

$ cpio -i[other options] < filename

You can extract all files or a single file from a cpio archive. This next example uses the cpio utility to extract a single file named rman.bsh from a cpio file named dbascripts.cpio:

$ cpio -idvm rman.bsh < dbascripts.cpio

An alternative way to unpack a file is to pipe the output of cat to cpio. Here is the syntax for this technique:

$ cat filename | cpio -i[other options]

Also of note is that you can use cpio to unbundle tar files. This example uses cpio to extract files from a script named scripts.tar:

$ cpio -idvm < scripts.tar

Your database design tool runs on a Windows box. After generating some schema creation scripts, you want to bundle the files on the Windows server and copy them to the Linux box. You wonder whether there is a common archiving tool that works with both Windows and Linux servers.

Use the zip utility if you need to bundle and compress files and transfer them across hardware platforms. This first example uses zip with the -r (recursive) option to bundle and compress all files in the oracle10g directory tree (this includes all files and subdirectories):

$ zip -r ora10g.zip oracle10g

You can also specify files that you want included in a .zip file. The following command bundles and compresses all SQL files in the current working directory:

$ zip myzip.zip *.sql

The zip utility is widely available on Windows and Linux servers. Files created by zip on Windows can be copied to and extracted on a Linux box. The zip utility both bundles and compresses files.

The compression ratio achieved by zip is not nearly as efficient as bzip2 or gzip. However, the zip and unzip utilities are popular because the utilities are portable across many platforms such as Linux, Windows, Unix, and Mac OS. If you need cross-platform portability, then use zip to bundle and unzip to unbundle.

Your database-modeling tool runs on a Windows box. After generating some schema creation scripts, you want to bundle the files on the Windows server, copy them to the Linux box, and unbundle them.

To uncompress a zipped file, first create a target directory location, then move the zip file to the new directory, and finally use unzip to unbundled and uncompress all files and directories included in the zip file. The example in this solution performs the following steps:

$ mkdir march08
$ cd march08
$ cp /mybackups/mvzip.zip .
$ unzip mvzip.zip

You should see output indicating what directories are being created and what files are being extracted. Here's a small snippet of the output for this example:

inflating: mscd642/perf.sql
creating: mscd642/ppt/
inflating: mscd642/ppt/9inew_f.ppt
inflating: mscd642/ppt/brpreso.ppt
inflating: mscd642/ppt/chap01.ppt
inflating: mscd642/ppt/chap02.ppt

The unzip utility will list, test, or extract files from a zipped archive file. You can use this utility to unzip files regardless of the operating system platform on which the zip file was originally created. This is handy because it allows you to easily transfer files between Linux and other operating systems such as Windows.

You can also use the unzip command to extract a subset of files from an existing zip archive. The following example extracts upgrade.sql from the upgrade.zip file:

$ unzip upgrade.zip upgrade.sql

Similarly, this example retrieves all files that ended with the extension of *.sql:

$ unzip upgrade.zip *.sql

Sometimes you run into situations where you want to add only those files that do exist in the source directory and do not exist in the target directory. First recursively zip the source directory. In this example, the relative source directory is orascripts:

$ zip -r /orahome/ora.zip orascripts

Then cd to the target location and unzip the file with the -n option. In this example, there is an orascripts directory beneath the /backup/scripts directory:

$ cd /backups/scripts
$ unzip -n /orahome/ora.zip

The -n option instructs the unzip utility to not overwrite existing files. The net effect is that you unbundle only those files that do exist in the source directory but do not exist in the target directory.

You want to verify which files are contained within an archived file before extracting its contents.

Each of the bundling commands (tar, cpio, and zip) has a method for listing files within an archive file. Each technique is described briefly in its own subsection.

$ tar -tvf orahome.tar

$ tar -tzvf orahome.tar.gz

$ cpio -itv < orahome.tar

$ cpio -itv < dir.cpio

$ cat dir.cpio | cpio -itv

$ unzip -l backup.zip

It's almost always a good idea to first list the files contained in an archived file. This will show you which directories will be created and which files will be extracted. If you're ever unsure about the contents of a bundled file, then use one of the techniques described in the "Solution" section of this recipe for obtaining a listing of the bundled file's contents.

You want to find all trace files over a certain age and bundle them into an archive file.

You'll have to use a combination of commands to find and compress files. This next example finds all trace files that were modified more than two days ago and then bundles and compresses them:

$ find /ora01/admin/bdump -name "*.trc" -mtime +2 | xargs tar -czvf trc.tar.gz

This next example uses cpio to achieve the same result:

$ find /ora01/admin/bdump -name "*.trc" -mtime +2 | cpio -ov | gzip > trc.cpio.gz

Quite often you'll find yourself cleaning up old files on database servers. When dealing with log or trace files, sometimes it's desirable to first find, bundle, and compress the files and then at some later time physically delete the files after they're not needed anymore (see recipe 5-26 for examples of finding and removing files). We recommend you encapsulate the code in this recipe in a shell script and run it regularly from a scheduling utility such as cron (see Chapter 11 for details on automating jobs).

You want to add one file to a previously created bundled archive.

Each of the bundling commands (tar, cpio, and zip) has a method for adding a file or a directory to an existing archive. Each technique is described briefly in its own section here.

$ tar -rvf backup.tar newscript.sql

$ tar -rvf backup.tar scripts

$ ls *.sql | cpio -ovAF my.cpio

$ find backup | cpio -ovAF my.cpio

$ zip -g my.zip script.sql

$ zip -gr my.zip backup

If you deal with a lot of files, sometimes it's easier to add a file to an existing archive rather than re-creating the archive from scratch. In these situations, use one of the techniques in the "Solution" section of this recipe to append a file.

Before copying a large file over the network to a remote server, you want to compress it.

Several utilities are available for compressing and uncompressing files. The gzip, bzip2, and compress utilities are widely used in Linux and Unix environments. Each of them is briefly detailed in the following sections.

This first example uses gzip to compress the scripts.tar file:

$ gzip scripts.tar

The gzip utility will add an extension of .gz to the file after it has been compressed. To uncompress a file compressed by gzip, use the gunzip utility:

$ gunzip scripts.tar.gz

The gunzip utility will uncompress the file and remove the .gz extension. The uncompressed file has the original name it had before the file was compressed.

Sometimes there is a need to peer inside a compressed file without uncompressing it. The following example uses the -c option to send the contents of the gunzip command to standard output, which is then piped to grep to search for the string error:

$ gunzip -c scrdv12_ora_19029.trc.gz | grep -i error

You can also use the zcat utility to achieve the same effect. The next command is identical to the previous command:

$ zcat scrdv12_ora_19029.trc.gz | grep -i error

Use bzip2 to compress files. By default, files compressed with bzip2 will be given a .bz2 extension. This example compresses the file exp.dmp:

$ bzip2 exp.dmp

To uncompress a bzip2 compressed file, use bunzip2. This utility expects a file to be uncompressed to be named with an extension of one of the following: .bz2, .bz, .tbz2, .tbz, or .bzip2. This example uncompresses a file named exp.dmp.bz2:

$ bunzip2 exp.dmp.bz2

The bzip2 utility will uncompress the file and remove the .bz2 extension. The uncompressed file has the original name it had before the file was compressed.

Sometimes you need to view the contents of a compressed file without uncompressing it. The following example uses the -c option to send the contents of the bunzip2 command to standard output, which is then piped to grep to search for the string error:

$ bunzip2 -c scrdv12_ora_19029.trc.bz2 | grep -i error

The compress utility is aptly named. Files compressed with this utility are given a .Z extension. This example compresses a large trace file:

$ compress dwrep_lgwr_1912.trc

To uncompress a compress compressed file, use uncompress:

$ uncompress dwrep_lgwr_1912.trc.Z

The uncompress utility will uncompress the file and remove the .Z extension. The uncompressed file has the original name it had before the file was compressed.

DBAs often move files from one location to another. This frequently includes moving files to remote servers. Compressing files before transferring them is critical to being able to copy large files. Several compression utilities are available. The most commonly used are gzip, b2zip, and compress.

The gzip utility is widely available in the Linux and Unix environments. If you have scripts that need to be portable across many variants of Linux and Unix, then gzip and gunzip are excellent choices.

The bzip2 utility is a newer and more efficient compression algorithm than gzip. The bzip2 tool is CPU intensive but achieves high compression ratios. If you need good compression with large files, then use bzip2 to compress and bunzip2 to uncompress.

We recommend you use gzip or bzip2 instead of compress. The compress utility is an older tool and less efficient compression tool. We mention it in this chapter only because you may run into compress compressed files on an older Linux or Unix server. Every once in a while you may run into a shell script that a previous DBA wrote that uses the compress utility.

You just copied a file from one server to another. You need to verify that the destination file has the same contents as the source file.

Use a utility such as sum to compute a checksum on a file before and after the copy operation. This example uses the sum command to display the checksum and number of blocks within a file:

$ sum backup.tar
24092 78640

In the previous output, the checksum is 24092, and the number of blocks in the file is 78640. After copying this file to a remote server, run the sum command on the destination file to ensure that it has the same checksum and number of blocks. Table 6-2 lists the common utilities used for generating checksums.

When moving files between servers or compressing and uncompressing, it's prudent to verify that a file contains the same contents as it did before the copy or compress/uncompress operation. The most reliable way to do this is to compute a checksum. This allows you to verify that a file wasn't inadvertently corrupted during a transmission or compress/uncompress.

A checksum is a value that is calculated that allows you to verify a file's contents. The simplest form of a checksum is a count of the number of bytes in a file. For example, when transferring a file to a remote destination, you can then compare the number of bytes between the source file and the destination file. This checksum algorithm is very simplistic and not entirely reliable. However, in many situations, counting bytes is the first step to determine whether a source and destination file contain the same contents. Fortunately, many standard Linux utilities are available to calculate reliable checksum values.

DBAs also compute checksums to ensure that important files haven't been compromised or modified. For example, you can use the md5sum utility to compute and later check the checksum on a file to ensure that it hasn't been modified in any way. This example uses md5sum to calculate and store the checksums of the listener.ora, sqlnet.ora, and tnsnames.ora files:

$ cd $TNS_ADMIN
$ md5sum listener.ora sqlnet.ora tnsnames.ora >net.chk

Then at some later time, you can use md5sum to verify that these files have not been modified since the last time a checksum was computed:

$ md5sum --check net.chk
listener.ora: OK
sqlnet.ora: FAILED
tnsnames.ora: OK
md5sum: WARNING: 1 of 3 computed checksums did NOT match

From the pervious output, the sqlnet.ora file has been modified sometime after the checksum was computed. This allows you to detect changes and ensure that important files have not been compromised.

You store sensitive DBA password information in a file on disk and want to encrypt the file before sharing it with other database administrators.

Use the gpg utility to encrypt a file. The first step is to generate public and private keys required by gpg:

$ gpg --gen-key

The previous command will prompt you for input such as what key type, size, lifetime, real name, and passphrase. Usually the default values are sufficient. You will need to remember the passphrase because that is required when you decrypt an encrypted file. You should see files created for you in the HOME/.gnupg directory.

After you have successfully generated the required keys, you can now encrypt files. In this example, the oracle user encrypts a file that contains sensitive passwords.

$ gpg --encrypt --recipient oracle passwords.txt

This creates a new file named passwords.txt.gpg. It does not remove the passwords.txt file. You can manually remove the passwords.txt file with the rm command.

The following line of code decrypts the passwords.txt.gpg file:

$ gpg --output passwords.txt --decrypt passwords.txt.gpg

You will be prompted for the passphrase. After entering the correct passphrase, an unencrypted file will be created for your perusal.

The gpg (GNU Privacy Guard) utility is an encryption tool that is installed by default on most Linux servers. This utility allows you to encrypt and decrypt sensitive files. When you first use the tool, you must generate public and private keys via the --gen-key option.

To send an encrypted message, use the receiver's public key in combination with the sender's private key. In other words, before you can send an encrypted file, you must first obtain the recipient's public key and place it on your keyring. The follow steps illustrate this concept:

If you're going to e-mail the key to the user sending the encrypted file, sometimes it's desirable to export the key as an ASCII file:

$ gpg -a --export > [email protected]

You should be prompted for a passphrase:

You need a passphrase to unlock the secret key for
user: oracle@bllnx (oracle@bllnx3) <oracle@bllnx3>"

As the receiver, enter your passphrase. After you successfully enter the correct passphrase, you should now have a decrypted password.txt file.

There are other encryption utilities such as openssl that you can use to protect sensitive files. This utility allows you to encrypt files without having to create keys. Before using openssl, first view the available ciphers available:

$ openssl list-cipher-commands

Here is a partial listing of the output:

aes-128-cbc
aes-128-ecb
aes-192-cbc
aes-192-ecb
aes-256-cbc

aes-256-ecb
base64
bf
bf-cbc
bf-cfb

Pick one of the ciphers from the previous list to use for encrypting a file. The following example uses the Advanced Encryption Standard (AES) 128-bit cipher in Cipher Block Chaining (CBC) mode:

$ openssl enc -aes-128-cbc -in passwords.txt -out passwords.enc

You should now be prompted for a password:

enter aes-128-cbc encryption password:

You will be prompted again for verification:

Verifying - enter aes-128-cbc encryption password:

To decrypt the file, use the -d option:

$ openssl enc -d -aes-128-cbc -in passwords.enc -out passwords.txt

After the password you entered is successfully authenticated, the file will be decrypted. The previous command will overwrite the file passwords.txt if it exits. If you don't specify an output file, the decrypted form of the encrypted file will be displayed on your terminal.

You can also specify a password on the command line when encrypting and decrypting via the -pass option of openssl. This encrypts the passwords.txt file using a password of foo:

$ openssl enc -aes-128-cbc -in passwords.txt -out passwords.enc -pass pass:foo

Similarly, use the -pass option when decrypting:

$ openssl enc -d -aes-128-cbc -in passwords.enc -out passwords.txt -pass pass:foo

Both openssl and gpg are considered to be robust encryption and decryption utilities. You may find the openssl utility a little easier to use because you don't have to generate keys when sharing encrypted files with other users.