Let's start with some of the scripts we use to back up and restore Oracle databases directly using commands
Oracle Data Backup:
Step 1 Back up the user data.
1. Connect the Linux terminal using the database administrator account under Linux system.
2. Execute the following statement to create the "Bak_dir" folder.
Copy Code code as follows:
3. Execute the following statement to give read, write, and execute permissions to the "Bak_dir" folder.
Copy Code code as follows:
4. Execute the following statement to sysdba the user log on to the Oracle database server.
Sqlplus database Administrator account/password @ database instance name as Sysdba
5. Execute the following statement, specifying "Bak_dir" as "/opt/oracle/bak_dir".
Copy Code code as follows:
Create or replace directory Bak_dir as '/opt/oracle/bak_dir '
Commit
6. Execute the following statement to assign the read, write, and Execute permissions of "Bak_dir" to the XX (data user name) user.
Copy Code code as follows:
Grant all on directory Bak_dir to XX
Commit
7. Execute the following statement to exit the Oracle database server.
Copy Code code as follows:
Execute the following statement to back up the Sysdb User's table to the "Bak_dir" directory.
8.
Copy Code code as follows:
EXPDP XX (to export the database user name of the table)/xx (password) @ database instance name directory=bak_dir dumpfile=expdb_xx.dmp logfile=expdb_xxlog tables= table name
Oracle Data Restore:
1. At the command line, enter:
Copy Code code as follows:
2. Execute the following statement login XX database, username: xx, password: xx (Please enter the local actual password)
Conn Xx/xx;
3. If there is a trigger in the recovered table, please stop trigger, example: a table
Copy Code code as follows:
ALTER TABLE A disable all triggers;
Commit
4. Execute the following statement to exit the current user
Quit
5. Execute the following statement to restore user data.
Copy Code code as follows:
IMPDP xx/xx@ Database Instance name Directory=bak_dir table_exists_action=truncate dumpfile=expdb_xx.dmp logfile=impdb_xx.log
5. Enter at command line: Sqlplus "/as sysdba"
6. Execute the following statement login XX database, username: xx, password: xx (Please enter the local actual password)
Conn Xx/xx;
7. Execute the following statement to open a blocked trigger
Copy Code code as follows:
ALTER TABLE A enable all triggers;
Commit
The above method can achieve what we want, but the large Web server must be scheduled to be backed up automatically.
Copy Code code as follows:
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 table space
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 the test user, create a table, and insert two data:
Copy Code code as follows:
CREATE TABLE T1 (
Id varchar (m) 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:
Copy Code code as follows:
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 4 sentences are set environment variables, because the Oracle user's environment variables are not used when crontab are called, so they are first brought in. Line 6th takes the current date as the exported file name, such as 20120626.dmp and 20120626.log. Line 7th the two files into a zip package, and delete the two files.
Use the chmod command to mark this sh as executable:
Copy Code code as follows:
For Oracle users, enter the CRONTAB-E command to edit the task schedule for Oracle users:
Code to copy the code as follows
1.[oracle@localhost backup]$ CRONTAB-E
2.42 * * */home/oracle/backup/backup.sh This adds a plan to run the/home/oracle/backup/backup.sh at 13:42 every day.
That's it, using Linux to plan the task is achieved.
Backup strategy:
Sunday Level 0
Monday, two, four, five, level 62
Wednesday Level 1
Copy Code code as follows:
--Create a local administrative 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 tablespaces and Rman users
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;
--Register Catalog database
Rman Catalog RMAN_DLP/DLP
Create catalog tablespace Rman_tbs;
Connect Target Sys/dg@priamry
Register database;
a schema;
--Set Backup parameters
Configure retention policy to redundancy 2;
Configure retention policy to recovery windows of 7 days;
--The following is a backup script (which can be edited through VI)
Dlp-> Touch Exp_rman.par
Dlp-> Touch exp_rman.sh
Dlp-> Touch RMAN_BK_LEVEL0.RCV (Database level 0 backup)
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
--Pour out 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
--Pour out 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
--Level 0 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 the current controlfile;
SQL ' alter system archive log current ';
Backup Archivelog all format '/dinglp/ora_managed/backup/rman_backup/log_%d_%s_%p_%u.bak ' deletes all input;
Release channel D2;
Release channel D1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete NoPrompt obsolete;
Resync Catalog;
Exit
--The rman_bk_level0.sh of level 0 backup shell script
#####################################################################
### 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
--first-level 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 ' deletes all input;
Release channel D1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete NoPrompt obsolete;
Resync Catalog;
Exit
--first-level 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 CMDFILE=RMAN_BK_LEVEL1.RCV Msglog=/dinglp/ora_managed/backup/log/rman_bk_level1.log
. /dinglp/ora_managed/scripts/exp_rman.sh
--Level Two 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 ' deletes all input;
Release channel D1;
}
Crosscheck backup;
Delete noprompt expired backup;
Delete NoPrompt obsolete;
Resync Catalog;
Exit
--Level two 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 CMDFILE=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 archived files that have been created, backed up, or purged in the database.
V$backup_corruption displays the damaged blocks found during backup of the backup set.
V$copy_corruption displays the damaged blocks found during image replication.
V$backup_datafile is used to create a backup set of the same size by determining the number of blocks in each data file. It also allows you to find the number of corrupted blocks in your data file. V$backup_redolog displays archived logs stored in the backup set.
V$backup_set displays the set of backups that have been created.
V$backup_piece Displays the backup slices that were created for the backup set.
--How to monitor the replication process
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 to.
Query V$session_longops, you can monitor the progress of backup and replication.
Run backup scripts automatically under--linux
Introduction to CRONTAB Format
1th Column minutes 1~59
2nd hour 1~23 (0 means midnight)
3rd Liege 1~31
4th Month 1~12
5th week 0~6 (0 for Sunday)
6th column the command to run
[ROOT@DLP ~]# Vi/etc/crontab
Shell=/bin/bash
Path=/sbin:/bin:/usr/sbin:/usr/bin
Mailto=root
home=/
# Run-parts
* * * * Root run-parts/etc/cron.hourly
4 * * * Root run-parts/etc/cron.daily
4 * * 0 root run-parts/etc/cron.weekly
4 1 * * Root run-parts/etc/cron.monthly
* * 0 root/dinglp/ora_managed/scripts/rman_bk_level0.sh
* * 3 root/dinglp/ora_managed/scripts/rman_bk_level1.sh
* * 1,2,4,5,6 root/dinglp/ora_managed/scripts/rman_bk_level2.sh
--Finished, ryohei,2010-08-04.
