ORCLE database backup policy
1. Use exp and imp commands to export and import databases.
There are three modes:
A. User Mode: export (import) all user objects and data in objects;
B. Table mode: export (import) All or specified tables of the user;
C. entire database: export (import) all objects in the database.
For example:
Normal export
A. Export a complete database
Exp system/manager file = f. dmp full = y
B. Export Database definitions without exporting data
Exp system/manager file = f. dmp full = y rows = n
Normal import:
A. Full Import
Imp system/manager file = f. dmp full = y
B. If the database structure exists, only data is imported.
Imp system/manager file = f. dmp full = y ignore = y
2. Back up the database every week to prevent data recovery after the database is accidentally damaged.
The schedule is as follows:
Monday: Full backup (f1) exp xxx/xxx inctype = complete file = f1.dmp
Tuesday: incremental Backup (f2) exp xxx/xxx inctype = incremental file = f2.dmp
Wednesday: incremental Backup (f3) exp xxx/xxx inctype = incremental file = f3.dmp
Thursday: incremental Backup (f4) exp xxx/xxx inctype = incremental file = f4.dmp
Friday: cumulative backup (f5) exp xxx/xxx inctype = cumulative file = f5.dmp
Saturday: incremental Backup (f6) exp xxx/xxx inctype = incremental file = f6.dmp
Sunday: incremental Backup (f7) exp xxx/xxx inctype = incremental file = f7.dmp
For example, if the database is damaged on Sunday, the following methods can be used to restore the database:
1. Create an empty database, same as the previous structure.
2. imp xxx/xxx inctype = restore full = y FILE = f1.dmp
3. imp xxx/xxx inctype = restore full = y FILE = f5.dmp
4. imp xxx/xxx inctype = restore full = y FILE = f6.dmp
Note:
Full export: backup of the entire database
Incremental export: The data changed after the last full export is backed up.
Cumulative export: Backup Data changed since the last full export.
EXAMPLE: Back up a database in LINUX
BACKUP_DIR =/home/oracle/backups
If [! -D $ BACKUP_DIR]; then
Mkdir-p $ BACKUP_DIR
Fi
DAYS = (Sun Mon Tue Wed Thu Fri Sat) # create an array
TYPES = (incremental complete incremental cumulative incremental)
Day = 'date + % W' # obtain the number of days of the week. 0 indicates Sunday, and 1 indicates Monday.
DAY_NAME =$ {DAYS [$ day]} # obtain the array value
TYPE =$ {TYPES [$ day]}
DATE_NAME = 'date + % F'
FILE_NAME =$ {DATE_NAME}-$ {DAY_NAME}-$ {TYPE}. dmp # 2008-12-8-Mon-complete.dmp
Exp xxx/xxx inctype = $ TYPE file =$ {BACKUP_DIR}/$ {FILE_NAME}>/dev/null
Gzip $ {BACKUP_DIR}/$ {FILE_NAME}
Find $ BACKUP_DIR-mtime + 7-delete # delete a file that has been changed seven days ago