Backup of Oracle database in Linux
- 2021-09-12 02:35:01
- OfStack
First, let's introduce some commands to back up and restore oracle database without using scripts
Oracle Data Backup:
Step 1 Back up user data.
1. Use the database administrator account under linux system to connect to linux terminal.
2. Create the "bak_dir" folder by executing the following statement.
3. Give read, write, and execute permissions to the "bak_dir" folder by executing the following statement.
4. Log in to the oracle database server as an sysdba user by executing the following statement.
sqlplus Database Administrator Account/Password @ Database Instance Name as sysdba
5. Specify "bak_dir" as "/opt/oracle/bak_dir" by executing the following statement.
6. Give the xx (data username) user read, write, and execute permissions for "bak_dir" by executing the following statement.
7. Exit the oracle database server by executing the following statement.
Execute the following statement to back up the sysdb user's table to the "bak_dir" directory.
8.
Oracle Data Restore:
1. On the command line, type:
2. Log in to the xx database by executing the following statement, username: xx, password: xx (please enter the actual local password)
conn xx/xx;
3. Stop triggers if there are triggers involved in the recovered table, for example: A table
4. Exit the current user by executing the following statement
quit;
5. Recover user data by executing the following statement.
5. Enter on the command line: sqlplus "/as sysdba"
6. Log in to the xx database by executing the following statement, username: xx, password: xx (please enter the actual local password)
conn xx/xx;
7. Open disabled triggers by executing the following statement
The above method can achieve what we want, but the large WEB server must be backed up automatically and regularly.
Log in with an Test user, create a table, and insert two pieces of data:
Write an exported script file first:
The first four sentences are setting environment variables, because when crontab is called regularly, the environment variables of oracle users will not be used, so they should be imported first. Line 6 takes the current date as the exported file name, such as 20120626. dmp and 20120626. log. Line 7 typed the two files into an zip package and deleted the two files.
To mark this sh as executable with the chmod command:
With the oracle user, enter the crontab-e command to edit the oracle user's task plan:
Copy the code as follows
1.[oracle@localhost backup]$ crontab -e
2.42 13***/home/oracle/backup/backup. sh This adds a plan to run the/home/oracle/backup/backup. sh at 13:42 every day.
In this way, the task is realized by using linux.
Backup strategy:
Grade 0 on Sunday
Week 1, 2, 4, 5, 6 2
Week 3 Level 1
Oracle Data Backup:
Step 1 Back up user data.
1. Use the database administrator account under linux system to connect to linux terminal.
2. Create the "bak_dir" folder by executing the following statement.
mkdir bak_dir
3. Give read, write, and execute permissions to the "bak_dir" folder by executing the following statement.
chmod 777 bak_dir
4. Log in to the oracle database server as an sysdba user by executing the following statement.
sqlplus Database Administrator Account/Password @ Database Instance Name as sysdba
5. Specify "bak_dir" as "/opt/oracle/bak_dir" by executing the following statement.
create or replace directory bak_dir as '/opt/oracle/bak_dir'
commit
6. Give the xx (data username) user read, write, and execute permissions for "bak_dir" by executing the following statement.
grant all on directory bak_dir to xx
commit
7. Exit the oracle database server by executing the following statement.
quit
Execute the following statement to back up the sysdb user's table to the "bak_dir" directory.
8.
expdp xx( The user name of the database where the table is to be exported )/xx( Password )@ Database instance name directory=bak_dir dumpfile=expdb_xx.dmp logfile=expdb_xxlog tables= Table name
Oracle Data Restore:
1. On the command line, type:
sqlplus "/as sysdba"
2. Log in to the xx database by executing the following statement, username: xx, password: xx (please enter the actual local password)
conn xx/xx;
3. Stop triggers if there are triggers involved in the recovered table, for example: A table
alter table A disable all triggers;
commit;
4. Exit the current user by executing the following statement
quit;
5. Recover user data by executing the following statement.
impdp xx/xx@ Database instance name directory=bak_dir table_exists_action=truncate dumpfile=expdb_xx.dmp logfile=impdb_xx.log
5. Enter on the command line: sqlplus "/as sysdba"
6. Log in to the xx database by executing the following statement, username: xx, password: xx (please enter the actual local password)
conn xx/xx;
7. Open disabled triggers by executing the following statement
alter table A enable all triggers;
commit;
The above method can achieve what we want, but the large WEB server must be backed up automatically and regularly.
1.-- Create a datasheet space
2.create tablespace test_data
3.logging
4.datafile '/u01/app/oradata/test/TEST.dbf'
5.size 32m
6.autoextend on
7.next 32m maxsize 2048m
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 in with an Test user, create a table, and insert two pieces of data:
create table t1(
Id varchar(50) primary key,
title varchar(50)
);
insert into t1 values(sys_guid(),'t1');
insert into t1 values(sys_guid(),'t2');
commit;
Write an exported script file first:
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH
d=$(date '+%Y%m%d')
exp TEST/123@TEST file=/home/oracle/backup/$d.dmp log=/home/oracle/backup/$d.log owner=TEST
zip -m /home/oracle/backup/$d.zip /home/oracle/backup/$d.dmp /home/oracle/backup/$d.log
The first four sentences are setting environment variables, because when crontab is called regularly, the environment variables of oracle users will not be used, so they should be imported first. Line 6 takes the current date as the exported file name, such as 20120626. dmp and 20120626. log. Line 7 typed the two files into an zip package and deleted the two files.
To mark this sh as executable with the chmod command:
chmod +x backup.sh
With the oracle user, enter the crontab-e command to edit the oracle user's task plan:
Copy the code as follows
1.[oracle@localhost backup]$ crontab -e
2.42 13***/home/oracle/backup/backup. sh This adds a plan to run the/home/oracle/backup/backup. sh at 13:42 every day.
In this way, the task is realized by using linux.
Backup strategy:
Grade 0 on Sunday
Week 1, 2, 4, 5, 6 2
Week 3 Level 1
--创建本地管理路径
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
--创建rman表空间和rman用户
create tablespace rman_tbs datafile '/oradata/luke/rman_tbs01.dbf' size 1024M;
create user rman_dlp identified by dlp default tablespace rman_tbs temporary tablespace temp;
grant connect,resource ,recovery_catalog_owner to rman;
--注册catalog 数据库
rman catalog rman_dlp/dlp
create catalog tablespace rman_tbs;
connect target sys/dg@priamry
register database;
report schema;
--设置备份参数
configure retention policy to redundancy 2;
configure retention policy to recovery window of 7 days;
--以下是备份脚本(可以通过vi进行编辑)
dlp-> touch exp_rman.par
dlp-> touch exp_rman.sh
dlp-> touch rman_bk_LEVEL0.rcv (数据库0级备份)
dlp-> touch rman_bk_LEVEL0.sh
dlp-> touch rman_bk_LEVEL1.rcv (数据库1级备份)
dlp-> touch rman_bk_LEVEL1.sh
dlp-> touch rman_bk_LEVEL2.rcv (数据库2级备份www.linuxidc.com)
dlp-> touch rman_bk_LEVEL2.sh
--倒出RMAN用户数据脚本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
--倒出RMAN数据SHELL脚本exp_rman.sh
##################################################
### exp_rman.sh ###
##################################################
#!/bin/bash
source /home/Oracle/.bash_profile
cd /dinglp/ora_managed/scripts
exp parfile=exp_rman.par
--零级备份RMAN脚本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;
--零级备份SHELL脚本的rman_bk_LEVEL0.sh
#####################################################################
### rman_bk_LEVEL0.sh ###
#####################################################################
#!/bin/bash
source /home/Oracle/.bash_profile
cd /dinglp/ora_managed/scripts
rman cmdfile=rman_bk_LEVEL0.rcv msglog=$HOME/backup/log/rman_bk_LEVEL0.log
./dinglp/ora_managed/script/exp_rman.sh
--1级差异增量备份RMAN脚本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;
--1级差异增量备份SHELL脚本rman_bk_LEVEL1.sh
#####################################################################
### rman_bk_LEVEL1.sh ###
#####################################################################
#!/bin/bash
source /home/Oracle/.bash_profile
cd /dinglp/ora_managed/scripts
rman cmdfile=rman_bk_LEVEL1.rcv msglog=/dinglp/ora_managed/backup/log/rman_bk_LEVEL1.log
. /dinglp/ora_managed/scripts/exp_rman.sh
--2级差异增量备份RMAN脚本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;
--2级差异增量备份SHELL脚本rman_bk_LEVEL2.sh
#####################################################################
### rman_bk_LEVEL2.sh ###
#####################################################################
#!/bin/bash
source /home/Oracle/.bash_profile
cd /dinglp/ora_managed/scripts
rman cmdfile=rman_bk_LEVEL2.rcv msglog=/dinglp/ora_managed/backup/log/rman_bk_LEVEL2.log
. /dinglp/ora_managed/scripts/exp_rman.sh
--提高RMAN增量备份性能
alter database enable block change tracking using file '/u01/app/Oracle/admin/devdb/bdump/luke.log';
desc v$block_change_tracking;
--RMAN 动态视图
V$ARCHIVED_LOG 显示在数据库中已经创建、备份或清除的归档文件。
V$BACKUP_CORRUPTION 显示在备份集的备份过程中找到的损坏块。
V$COPY_CORRUPTION 显示映像复制过程中找到的损坏块。
V$BACKUP_DATAFILE 用于通过确定各数据文件中的块数来创建大小相同的备份集。通过它也可以找出数据文件中已损坏的块数。 V$BACKUP_REDOLOG 显示在备份集中存储的归档日志。
V$BACKUP_SET 显示已经创建的备份集。
V$BACKUP_PIECE 显示为备份集创建的备份片。
--如何监视复制进程
使用 SET COMMAND ID 命令可将服务器会话与通道联系起来。
查询 V$PROCESS 和 V$SESSION，可以确定会话与哪些 RMAN 通道对应。
查询 V$SESSION_LONGOPS，可以监视备份和复制的进度。
--linux下自动运行备份脚本
crontab格式简介
第1列分钟1～59
第2列小时1～23（0表示子夜）
第3列日1～31
第4列月1～12
第5列星期0～6（0表示星期天）
第6列要运行的命令
[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
--完毕，RYOHEI，2010-08-04。