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.
| The code is as follows |
Copy Code |
mkdir Bak_dir
|
3. Execute the following statement to give read, write, and execute permissions to the "Bak_dir" folder.
| The code is as follows |
Copy Code |
chmod 777 Bak_dir
|
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".
| 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 assign the read, write, and Execute permissions of "Bak_dir" to the XX (data user name) user.
| The code is as follows |
Copy Code |
| Grant all on directory Bak_dir to XX |
Commit
7. Execute the following statement to exit the Oracle database server.
| The code is as follows |
Copy Code |
| Quit |
Execute the following statement to back up the Sysdb User's table to the "Bak_dir" directory.
8.
| The code is as follows |
Copy Code |
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:
| The code is as follows |
Copy Code |
| Sqlplus "/as sysdba" |
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
| The code is as follows |
Copy Code |
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.
| 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 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
| The code is as follows |
Copy Code |
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.
Cases
| The code is as follows |
Copy Code |
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:
| The code is as follows |
Copy Code |
1.create table T1 (
2. Id varchar (m) 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; |
Write an exported script file first:
| 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/$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:
| The code is as follows |
Copy Code |
1.chmod +x backup.sh |
For Oracle users, enter the CRONTAB-E command to edit the task schedule for Oracle users:
| The code is as follows |
Copy Code |
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
| The code is as follows |
Copy Code |
| --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 --Inverted 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;
Ex it; --Level 0 backup shell script 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 --one-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 ' da Tabase;
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;
Ex it; --one-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 ' da Tabase;
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;
Ex it; --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. |
