How to back up Oracle databases in Linux

Source: Internet
Author: User

There are many methods for backing up Oracle databases in Linux. Just like mysql, you can use different methods to back up oracle databases. The following section will introduce several backup database scripts.

First, we will introduce some commands to back up and restore oracle databases without using scripts.

Oracle Data Backup:

Step 1. Back up user data.

1. Use the database administrator account in linux to connect to the linux terminal.

2. Execute the following statement to create the "bak_dir" folder.

The Code is as follows: Copy code
Mkdir bak_dir

3. Execute the following statement to grant read, write, and execute permissions to the "bak_dir" folder.

The Code is as follows: Copy code
Chmod 777 bak_dir

4. Run the following statement to log on to the oracle database server as sysdba.
Sqlplus Database Administrator account/password @ database instance name as sysdba
5. Run the following statement to set "bak_dir" to "/opt/oracle/bak_dir ".

The Code is as follows: Copy code
Create or replace directory bak_dir as '/opt/oracle/bak_dir'
Commit

6. Execute the following statement to grant the read, write, and execute permissions of "bak_dir" to xx (Data User Name) users.

The Code is as follows: Copy code
Grant all on directory bak_dir to xx

Commit
7. Run the following statement to exit the oracle Database Server.

The Code is as follows: Copy code
Quit

Run the following statement to back up the table of sysdb to the "bak_dir" directory.
8.

The Code is as follows: Copy code

Expdp xx (Database User name where the table to be exported is located)/xx (password) @ database instance name directory = bak_dir dumpfile = expdb_xx.dmp logfile = expdb_xxlog tables = table name

 

Oracle data restoration:

1. Enter the following in the command line:

The Code is as follows: Copy code
Sqlplus "/as sysdba"

2. Run the following statement to log on to the xx Database: User name: xx, password: xx (enter the actual local password)
Conn xx/xx;

3. If A trigger is involved in the recovered table, stop the trigger, for example, table.

The Code is as follows: Copy code

Alter table A disable all triggers;

Commit;

4. Run the following statement to exit the current user.
Quit;
5. Execute the following statement to restore user data.

The Code is as follows: Copy code
Impdp xx/xx @ database instance name directory = bak_dir table_exists_action = truncate dumpfile = expdb_xx.dmp logfile = impdb_xx.log

5. Enter sqlplus "/as sysdba" in the command line"

6. Run the following statement to log on to the xx Database: User name: xx, password: xx (enter the actual local password)
Conn xx/xx;

7. Execute the following statement to open the disabled trigger.

The Code is as follows: Copy code

Alter table A enable all triggers;

Commit;

The above method can achieve what we want, but large WEB servers must be automatically and regularly backed up.

Example

The Code is as follows: Copy code


1. -- create a data table space
2. create tablespace test_data
3. logging
4. datafile '/u01/app/oradata/test/TEST. dbf'
5. size 32 m
6. autoextend on
7. next 32 m maxsize 2048 m
8. extent management local;
9.
10. -- create a user and specify a tablespace
11. create user TEST identified by 123
12. default tablespace test_data
13. temporary tablespace temp;
14.
15. -- grant permissions to users
16. grant connect, resource to TEST;

Log On with the Test user, create a table, and insert two pieces of data:

The Code is as follows: Copy code

1. create table t1 (
2. Id varchar (50) primary key,
3. title varchar (50)
4 .);
5.
6. insert into t1 values (sys_guid (), 't1 ');
7. insert into t1 values (sys_guid (), 't2 ');
8. commit;

First, write an exported script file:

The Code is as follows: Copy code

1. export ORACLE_BASE =/u01/app
2. export ORACLE_HOME =/u01/app/oracle
3. export ORACLE_SID = TEST
4. export PATH = $ ORACLE_HOME/bin: $ PATH
5. d = $ (date '+ % Y % m % D ')
6. exp TEST/123 @ TEST file =/home/oracle/backup/$ d. dmp log =/home/oracle/backup/$ d. log owner = TEST
7. zip-m/home/oracle/backup/mongod.zip/home/oracle/backup/$ d. dmp/home/oracle/backup/$ d. log

The first four sentences are environment variables. Because crontab does not use the environment variables of oracle users during scheduled calls, you must import them first. Row 6th uses the current date as the exported file name, such as 20120626. dmp and 20120626.log. In line 3, compress the two files into a zip package and delete them.

Use the chmod command to mark this sh as executable:

The Code is as follows: Copy code

1. chmod + x backup. sh

Run the crontab-e command to edit the task plan of an oracle user:

The Code is as follows: Copy code

1. [oracle @ localhost backup] $ crontab-e
2.42 13 ***/home/oracle/backup. sh adds a plan to run/home/oracle/backup at every day. sh.

In this way, you can use linux to schedule tasks.

Backup policy:

Sunday level 0
Monday, second, fourth, fifth, sixth, Grade 2
Level 1 on Wednesday

 

The Code is as follows: Copy code

-- Create a local management path

Mkdir-p/dinglp/ora_managed/backup
Mkdir-p/dinglp/ora_managed/backup
Mkdir-p/dinglp/ora_managed/backup/export-
 

Mkdir-p/dinglp/ora_managed/backup/log
Mkdir-p/dinglp/ora_managed/backup/rman_backup
Mkdir-p/dinglp/ora_managed/scripts

-- Create rman tablespace and rman user

Create tablespace rman_tbs datafile '/oradata/luke/rman_tbs01.dbf' size 1024 M;
Create user rman_dlp identified by dlp default tablespace rman_tbs temporary tablespace temp;
Grant connect, resource, recovery_catalog_owner to rman;

-- Register the catalog Database

Rman catalog rman_dlp/dlp
Create catalog tablespace rman_tbs;
Connect target sys/dg @ priamry
Register database;
Report schema;

-- Set backup Parameters

Configure retention policy to redundancy 2;
Configure retention policy to recovery window of 7 days;

-- The following is a backup script (you can edit it through vi)
Dlp-> touch exp_rman.par
Dlp-> touch exp_rman.sh
Dlp-> touch rman_bk_LEVEL0.rcv (Database Backup level 0)
Dlp-> touch rman_bk_LEVEL0.sh
Dlp-> touch rman_bk_LEVEL1.rcv (Database Level 1 backup)
Dlp-> touch rman_bk_LEVEL1.sh
Dlp-> touch rman_bk_LEVEL2.rcv (Database Level 2 backup www.linuxidc.com)
Dlp-> touch rman_bk_LEVEL2.sh

-- Returns the RMAN user data script exp_rman.par.
######################################## ##########
### Exp_rman.par ###
######################################## ##########
Userid = rman_dlp/dlp
File =/dinglp/ora_managed/backup/export/rman. dmp
Log =/dinglp/ora_managed/backup/log/rman. log

-- Reverse the RMAN data SHELL script exp_rman.sh
######################################## ##########
### Exp_rman.sh ###
######################################## ##########
#! /Bin/bash
Source/home/Oracle/. bash_profile
Cd/dinglp/ora_managed/scripts
Exp parfile = exp_rman.par

-- Zero-level backup RMAN script rman_bk_LEVEL0.rcv
Connect catalog rman_dlp/dlp
Connect target sys/dg @ primary
Run {
Allocate channel d1 type disk;
Allocate channel d2 type disk;
Backup incremental level 0 database format'/dinglp/ora_managed/backup/rman_backup/level0 _ % d _ % s _ % p _ % u. bak'
Tag = 'level 0' include current controlfile;
SQL 'alter system archive log current ';
Backup archivelog all format'/dinglp/ora_managed/backup/rman_backup/log _ % d _ % s _ % p _ % u. bak 'delete all input;
Release channel d2;
Release channel d1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete noprompt obsolete;
Resync catalog;
Exit;

-- Rman_bk_LEVEL0.sh of the zero-level backup SHELL script
######################################## #############################
### Rman_bk_LEVEL0.sh ###
######################################## #############################
#! /Bin/bash
Source/home/Oracle/. bash_profile
Cd/dinglp/ora_managed/scripts
Rman synchronized file = rman_bk_LEVEL0.rcv msglog = $ HOME/backup/log/rman_bk_LEVEL0.log
./Dinglp/ora_managed/script/exp_rman.sh

-- Level 1 differential Incremental Backup RMAN script rman_bk_LEVEL1.rcv
Connect catalog rman_dlp/dlp
Connect target sys/dg @ primary
Run {
Allocate channel d1 type disk;
Backup incremental level 1 format'/dinglp/ora_managed/backup/rman_backup/level1 _ % d _ % s _ % p _ % u. bak 'tag = 'Level 1' database;
SQL 'alter system archive log current ';
Backup archivelog all format'/dinglp/ora_managed/backup/rman_backup/log _ % d _ % s _ % p _ % u. bak 'delete all input;
Release channel d1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete noprompt obsolete;
Resync catalog;
Exit;

-- Level 1 differential Incremental Backup SHELL script rman_bk_LEVEL1.sh
######################################## #############################
### Rman_bk_LEVEL1.sh ###
######################################## #############################
#! /Bin/bash
Source/home/Oracle/. bash_profile
Cd/dinglp/ora_managed/scripts
Rman synchronized file = rman_bk_LEVEL1.rcv msglog =/dinglp/ora_managed/backup/log/rman_bk_LEVEL1.log
./Dinglp/ora_managed/scripts/exp_rman.sh

-- Level 2 Differential Incremental Backup RMAN script rman_bk_LEVEL2.rcv
Connect catalog rman_dlp/dlp
Connect target sys/dg @ primary
Run {
Allocate channel d1 type disk;
Backup incremental level 2 format'/dinglp/ora_managed/backup/rman_backup/level2 _ % d _ % s _ % p _ % u. bak 'tag = 'Level 2' database;
SQL 'alter system archive log current ';
Backup archivelog all format'/dinglp/ora_managed/backup/rman_backup/log _ % d _ % s _ % p _ % u. bak 'delete all input;
Release channel d1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete noprompt obsolete;
Resync catalog;
Exit;

-- Level 2 Differential Incremental Backup SHELL script rman_bk_LEVEL2.sh
######################################## #############################
### Rman_bk_LEVEL2.sh ###
######################################## #############################
#! /Bin/bash
Source/home/Oracle/. bash_profile
Cd/dinglp/ora_managed/scripts
Rman audit file = rman_bk_LEVEL2.rcv msglog =/dinglp/ora_managed/backup/log/rman_bk_LEVEL2.log
./Dinglp/ora_managed/scripts/exp_rman.sh

-- Improve RMAN Incremental backup performance

Alter database enable block change tracking using file '/u01/app/Oracle/admin/devdb/bdump/luke. log ';

Desc v $ block_change_tracking;

-- RMAN Dynamic View
V $ ARCHIVED_LOG displays the archive files that have been created, backed up, or cleared in the database.
V $ backup_partition uption shows the damaged blocks found during backup of the Backup set.
V $ copy_uption: shows the damaged blocks found during image copying.
V $ BACKUP_DATAFILE is used to create a backup set of the same size by determining the number of blocks in each data file. You can also find the number of corrupted blocks in the data file. V $ BACKUP_REDOLOG displays the archived logs stored in the backup set.
V $ BACKUP_SET: displays the created backup set.
V $ BACKUP_PIECE displays the backup slices created for the backup set.

-- How to monitor replication processes
Use the set command id command to associate a server session with a channel.
Query V $ PROCESS and V $ SESSION to determine which RMAN channels the SESSION corresponds.
Query V $ SESSION_LONGOPS to monitor the backup and replication progress.

-- Automatically run the backup script in linux
Crontab format Overview
1st columns minute 1 ~ 59
2nd columns hour 1 ~ 23 (0 indicates midnight)
1-3rd ~ 31
4th columns Month 1 ~ 12
5th columns in a week 0 ~ 6 (0 indicates Sunday)
6th columns of commands to run

[Root @ dlp ~] # Vi/etc/crontab

SHELL =/bin/bash
PATH =/sbin:/bin:/usr/sbin:/usr/bin
MAILTO = root
HOME =/

# Run-parts
01 *** root run-parts/etc/cron. hourly
02 4 *** root run-parts/etc/cron. daily
22 4 ** 0 root run-parts/etc/cron. weekly
42 4 1 ** root run-parts/etc/cron. monthly
00 22 ** 0 root/dinglp/ora_managed/scripts/rman_bk_LEVEL0.sh
00 22 ** 3 root/dinglp/ora_managed/scripts/rman_bk_LEVEL1.sh
00 22 ** 1, 2, 4, 5, 6 root/dinglp/ora_managed/scripts/rman_bk_LEVEL2.sh

-- Completed, RYOHEI, 2010-08-04.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.