ORACLE Database Backup Technology

ORACLE Database is a large-scale relational database that can store terabytes of data. Therefore, it is critical to ensure the security of such data, we have developed a complete ORACLE database backup system. For your reference. ORACLE databases run in either of the following ways: ARCHIVELOG

ORACLE Database is a large-scale relational database that can store terabytes of data. Therefore, it is critical to ensure the security of such data, we have developed a complete ORACLE database backup system. For your reference. ORACLE databases run in either of the following ways: ARCHIVELOG

ORACLE Database is a large-scale relational database that can store terabytes of data. Therefore, it is critical to ensure the security of such data, we have developed a complete ORACLE database backup system. For your reference.

There are two ways to run ORACLE databases: ARCHIVELOG. Archive aims to restore all submitted items to the maximum extent when a database fails; the second is to restore the database to the nearest recycling point without archiving (NOARCHIVELOG. Based on the high availability of the database and the amount of workload that users can afford to lose, we archive the databases that we officially run, and archive the databases that are being developed and debugged. When creating a database, as part of creating a database, it determines the initial archiving method of the database. Generally, it is in NOARCHIVELOG mode. After the database is created, change the database to archive as needed to ARCHIVELOG.
Shutting down the database and backing up existing data to change the running mode of the database is an important change to the database. Therefore, you must back up the database to protect possible problems.

The following is a detailed introduction:

Start Instance, Mount database, but do not open database, to change the archive Mode
Sqldba lmode = y (svrmgrl)
Sqldba> connect internal
Sqldba> startup mount
Sqldba> alter database archivelog;

Enable automatic archiving
Add the following parameters to the initialization file init *. ora (usually under dbs in the lower directory of the ORACLE root directory:
Log_archive_start = true
Specify the redo login file name and storage location for the archive
Add the following parameters to the initialization file init *. ora:
Log_archive_format = % S. arc
Log_archive_dest =/arch12/arch (arch12 is the directory where log files are stored)
Close the database, restart the database, and archive the data.

The database recovery system depends on the database running mode and the workload that can be borne by the loss. For non-archiving mode, we use the ORACLE database logical backup Export. The recovery system is: A full backup (x01.dmp) is performed once a month, and a cumulative backup is performed once a week (c *. dmp), Incremental backup once a day (I *. dmp), specifically, Incremental backup from Monday to Friday, and cumulative backup or full backup on Saturday. After each backup is completed, it is automatically uploaded to other machines for storage. These backups are automatically executed and the program text is provided later.

For the archive method, we use ORCALE physical backup: After the database is created, a physical full backup is performed, without shutting down the database at ordinary times, physical backup of online database data files for frequently-changed data files within one week. When the database structure changes, such as adding tablespaces and data files, such as shutdown allowed, close the database normally, and re-create a full backup of the database. Otherwise, back up the control file and back up the data file as usual. Online log files are written on the array without mirroring or backup.

Take sugon AIX as an example to explain how to perform automatic backup:
First, create a UNIX user demo/demo;
Copy the. profile under the oracle user to the demo user to ensure that the demo user can use the Export command;
Log on as the root user and edit/var/adm/cron. allow to add the user name demo. In this way, the demo user has the right to automatically execute the shell program for database backup;

Create a database backup program and put it under the bin directory of the current user. These files must be executable.

File zlbf1: (Incremental Backup 1, Program executed on Monday );
./U11/demo/. profile
Exp system/manager inctype = INCREMENTAL file = i01.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put i01.dmp
Close
Bye
!

File zlbf2: (Incremental Backup 2, Program executed on Tuesday)
./U11/demo/. profile
Exp system/manager inctype = INCREMENTAL file = i02.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put i02.dmp
Close
Bye
!

File zlbf3: (Incremental Backup 3, Program executed on Wednesday)
./U11/demo/. profile
Exp system/manager inctype = INCREMENTAL file = i03.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put i03.dmp
Close
Bye
!

File zlbf4: (Incremental Backup 4, Program executed on Thursday)
./U11/demo/. profile
Exp system/manager inctype = INCREMENTAL file = i04.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put i04.dmp
Close
Bye
!

File zlbf5: (Incremental Backup 5, Program executed on Friday)
./U11/demo/. profile
Exp system/manager inctype = INCREMENTAL file = i05.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put i05.dmp
Close
Bye
!

File zlbf6: (Incremental Backup 6, Program executed on Saturday)
./U11/demo/. profile
Js = 'ls c *. dmp | wc-l'
If test $ js = 0
Then
Exp system/manager inctype = CUMULATIVE file = c01.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put c01.dmp
Close
Bye
!
Fi
If test $ js = 1
Then
Exp system/manger inctype = CUMULATIVE file = c02.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put c02.dmp
Close
Bye
!
Fi
If test $ js = 2
Then
Exp system/maneger inctype = CUMULATIVE file = c03.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put c03.dmp
Close
Bye
!
Fi
If test $ js = 3
Then
Exp system/manager inctype = COMPLETE file = x01.dmp
Ftp-n <!
Open 10.10.10.40
User demo
Binary
Prompt
Cd backup40
Put x01.dmp
Close
Bye
!
Rm c *. dmp
Fi

Create a file under the demo user, for example: cron. demo, the file content is as follows
59 22 ** 1 bin/zlbf1
59 22 ** 2 bin/zlbf2
59 22 ** 3 bin/zlbf3
59 22 ** 4 bin/zlbf4
59 22 ** 5 bin/zlbf5
59 22 ** 6 bin/zlbf6

Run the following command under the demo User: crontab cron. The result of the demo execution is put

To the/var/spool/cron/crontabs/demo file;
Restart the cron process and run the command: kill-1 process number;
Put the execution result in the user's email;
Check whether the result is executed. See the/var/adm/cron/log file;

Find the data file to be recycled, online redo login file, control file and parameter file;
Shut down the database and perform a full recovery (use the TAR command of the operating system to write it as a shell program). However

Then open the database for normal use. The shell program is as follows:
Tar cvf/dev/rmt/ctape2/u1/oracle/dbs/ctrl1ora7. ctl
/U1/oracle/dbs/ctrl2ora7. ctl
/U1/oracle/dbs/ctrl3ora7. ctl
/U1/oracle/dbs/systora7.dbf
/U1/oracle/dbs/rbsora7.dbf
/U1/oracle/dbs/tempora7.dbf
/U1/oracle/dbs/toolora7.dbf
/U1/oracle/dbs/usrora7.dbf
/Ls1/ls1.dbf
>/U1/oracle/dbs/log1ora7. dbf
/U1/oracle/dbs/log2ora7. dbf
/U1/oracle/dbs/log3ora7. dbf

Online tablespace and data file partial recovery are often performed:

Mark the start of online tablespace recovery
Alter tablespace name begin backup;
Use operating system commands to back up data files
Finally, the online tablespace recovery is ended.
Alter tablespace name end backup

Multiple tablespaces can be marked together. After all the data files marked by backups are marked together, the table is marked together.
When the database structure changes, you need to recycle its control files.
Sqldba> alter database backup controlfile to 'file name' reuse;

A complete backup system is a favorable guarantee for the safe operation of databases.