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. |