Use RedHatLinux to complete automatic Oracle physical backup

Database parameter files are usually stored in ldquo; ORACLEADMIN database name pfilerdquo; directory, the file naming rules are ldquo; init + database name. orar

Database parameter files are usually stored in the ldquo; ORACLE/ADMIN/database name/pfile/rdquo; directory. The file naming rules are ldquo; init + database name. orar

Oracle Database provides several data backup methods, but the most commonly used is logical Backup Using exp. Physical backup (including online backup and offline backup) is not used much. The main reason is that the physical backup process is complex and difficult to master. This article describes how to perform physical backup by taking the implementation of automatic physical backup (offline backup) for Oracle databases in Linux as an example. We hope to help more Oracle Database Administrators understand this physical backup process, avoid unnecessary data loss.
The goal of this article is to execute a Shell script to complete the entire process of automatic Oracle database backup, and to generate compressed files in the current directory. The specific implementation steps are as follows:

1. log on to Linux as an Oracle user.

2. Create a database. srcept file to generate some parameter information of the backup database and save it in the file database. parm.

This information has an important reference for restoring the database in the future. Therefore, you need to save this information during the physical backup process. You can also write other important information in this script.

$ Vi database. srcept

Spool database. parm

-- This is the list of system parameters of the current backup database.

Select * from v $ parameter;

-- This is part of the character set parameter of the current backup database

Select * from props $;

-- This is the file storage location and name of the current backup database.

Select * from v $ datafile;

-- This is the current backup database control file storage location and name

Select * from v $ controlfile;

-- This is the location and name of the log file stored in the current backup database

Select * from v $ logfile;

-- You can add other important information here.

-- Start generating the backup shell file. For details, refer to backup. sh.

Spool off

Spool backup. sh

Select 'cp 'name' backup/'from v $ datafile;

Select 'cp 'name' backup/'from v $ controlfile;

Select 'cp 'member' backup/'from v $ logfile;

Spool off

Shutdown immediate

Exit

!

3. Modify the backup. sh file generated in the previous step and execute it to complete the operating system backup of the database file. For clarity, name this script file alterbackup. sh.

$ Vi alterbackup. sh

Echo "this script completes the process of copying database data files, control files, and log files to the current directory"

Cat backup. sh grep 'cp/'> c. sh

# This statement extracts all statements starting with "cp/" in backup. sh to generate a new file c. sh.

Rm backup. sh

Mv c. sh backup. sh

Chmod + x backup. sh

. Backup. sh

# Note: there is a space between the dot "." And backup. sh.

4. Create a database startup script to start the database after the backup is completed. Name the script startup. script.

$ Vi startup. script

Spool StartStatus. readme

-- Start the database

Startup

-- After the database is started, you can check the StartStatus. readme file to check the database startup status.

Spool off

Exit

!

5. To save disk space and copy data to other storage locations, create a Shell file gzip. script to compress the backup data files. Note that the time is specified in the file name when the backup file is generated.

$ Vi gzip. sh

Echo "Start the Backup File compression process"

Thedate = 'date + % Y. % m. % d. % H. % m'

# Note: There is a backticks before and after the string, not single quotes.

Outfile = $ thedate

Tar-cvf backup1_outfile.tar backup /*

# Generate a file for all the files backed up to the backup directory

Gzip backupappsoutfile.tar

# Compress archive files to save hard disk space

Rm-R backup # Delete uncompressed files

6. Organize the content generated from step 1 to step 2 into a Shell file begin. sh. Before that, run the following command:

$ Chmod + x alterbackup. sh

$ Chmod + x gzip. sh

$ Vi begin. sh

Echo starts the automatic physical processing of the data library. The shellwill generate the backup.gz file under the current directory,

This file contains some database parameter information and database physical files ......"

Mkdir backup

Sqlplus internal/oracle <database. srcept

. Alterbackup. sh

Sqlplus internal/oracle <startup. script

. Gzip. sh

Echo processing: the data warehouse is automatically in the physical processing process. Check backup.tar.gz in the current directory"

7. Added backup statements for database parameter files.

Database parameter files are usually stored in the "ORACLE/ADMIN/database name/pfile/" directory. The file naming rules are "init + database name. the default database name is orcl, which can be modified according to the database installation name. If you do not know where the file is stored, run the following command to find it:

$ Find $ ORACLE_HOME-name 'initorcl. ora'

This statement may display/u01/app/oracle/product/8.1.7/dbs/initorcl. ora, because there is a link file in Linux, you need to check whether the displayed file is a link file. If yes, you need to further check its original file.

$ Ll/u01/app/oracle/product/8.1.7/dbs/initorcl. ora

In this example, the file is a link file pointing to/u01/app/oracle/admin/orcl/pfile/initorcl. ora. To this end, you can modify alterbackup. sh in step 1. The Modification result is as follows (in bold, this statement needs to be modified according to the database installation situation ):

......

Chmod + x backup. sh

Cp/u01/app/oracle/admin/orcl/pfile/initorcl. ora backup/initorcl. ora

. Backup. sh

# Note: there is a space between the dot "." And backup. sh.

8. when preparing for backup, run the "ls-l (or ll)" command to check the current directory. At this time, there should be several files: alterbackup. sh, begin. sh, database. script, gzip. sh, startup. script.

After that, execute the following command:

$ Chmod + x begin. sh

If everything is done, you can execute begin. sh to complete the backup process:

. Begin. sh

# Note that there is a space before begin. sh and the first vertex.

You only need to run begin. sh each time you need a backup. Here, you can also use crontab to automatically back up data as planned. For details about how to use crontab, refer to relevant information or query it online. This article will not introduce it any more.

Using this method for physical backup not only backs up the data files of the database, but also records some important information of the database (step 1 of the database. parm file), which is very important for future data recovery. Note that the method provided in this article requires sufficient disk space (although only the compressed file of the backup file is retained ), this shortage is left for readers to make up.

Note: This article passes the/bin/bash debugging in RedHat 7.3 and Oracle 8i Enterprise Edition Release 8.1.7 for Linux.