看文档笔记
官方文档：9 Performing Backup and Recovery
http://docs.oracle.com/database/121/ADMQS/backrest.htm#ADMQS12547
一。直接备份数据库
通过rman备份或恢复的用户需要sysdba或sysbackup权限；
Fast Recovery Area相关参数控制
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
查看当前数据库是否开启了flashback database功能；
SELECT FLASHBACK_ON FROM V$DATABASE;
--该参数默认是1440分钟，即一天；通过show parameter查看：
DB_FLASHBACK_RETENTION_TARGET
---启动flashback database功能；
ALTER DATABASE FLASHBACK ON;
Enabling Block Change Tracking
开启该功能后，将记录block变更，将提高增量备份效率；
查看block change跟踪文件位置
SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
该跟踪文件自动创建于oracle自动管理文件目录下
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata';
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
rman使用
登录rman管理器
$rman target /
显示rman配置信息
>show all
>CONFIGURE DEFAULT DEVICE TYPE TO DISK;
>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
---节省fast recovery空间，对未变化的文件如有备份，将不再备份；
CONFIGURE BACKUP OPTIMIZATION ON;
--更改保留天数
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
--决定归档日志的删除方式
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
--备份时不备份指定的表空间
CONFIGURE EXCLUDE FOR TABLESPACE example;
--控制spfile和controlfile是否自动备份
CONFIGURE CONTROLFILE AUTOBACKUP ON;
--恢复默认配置
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
configure exclude for tablespace users clear;
数据库开启状态下进行的一致备份，默认在默认位置fastrecover area;
备份前需要crosscheck archivelog all;
BACKUP DATABASE PLUS ARCHIVELOG format '/backup/fudb%d_%s.bak';
--上语句数据文件备份在默认的闪回恢复区，归档日志备份在指定的位置；
BACKUP DATABASE format '/backup/fudb%d_%s.bak' PLUS ARCHIVELOG;
---上语句数据文件备份在指定位置，但归档日志备份在默认闪回恢复区；
BACKUP DATABASE format '/backup/fudb%d_%s.bak' PLUS ARCHIVELOG format '/backup/arclog%d_%s.bak';
--上语句，对数据文件和归档日志均备份到了指定位置；
容器库和插件库备份
1。直接连接pdb数据库时；
BACKUP DATABASE
2.连接容器库时对pdb进行备份；
BACKUP PLUGGABLE DATABASE hrpdb;
BACKUP PLUGGABLE DATABASE hrpdb, salespdb, invpdb;
将数据库置于mount状态下进行一致性备份，不需要备份归档日志；
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
rman>BACKUP DATABASE;
备份完成后启动数据库
ALTER DATABASE OPEN;
自动备份脚本；
[root@oel6x64 backup]# ll
-rwxr-xr-x. 1 oracle oinstall 316 3月 10 10:53 daily_backup.sh
[root@oel6x64 backup]# cat daily_backup.sh
#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/12/db1
export ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
rman < connect target /
RUN {
ALLOCATE CHANNEL disk_iub DEVICE TYPE DISK;
RECOVER COPY OF DATABASE WITH TAG daily_iub;
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG daily_iub DATABASE;
}
exit
EOF
About the Oracle Suggested Backup Strategy and Retention
When using the Oracle suggested backup strategy, the retention is dictated by the recovery and not by the configured retention. In order to get retention beyond 24 hours, you must change the RECOVER statement to something like:
就是说用上述脚本的时候，rman configure中设置的保留策略是无效的，需手动配置保留时间；
RECOVER COPY OF DATABASE WITH TAG 'ORA_OEM_LEVEL_0' UNTIL TIME "SYSDATE-4";
对COPY出来的数据库作增量同步。
二。备份结果查看
查看备份概要；
LIST BACKUP SUMMARY;
显示特定备份对象
LIST BACKUP OF DATAFILE 3;
检查数据文件
RMAN> VALIDATE DATAFILE '/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_users_bh21l5rw_.dbf';
测试数据库是否可恢复；
RESTORE VALIDATE DATABASE;
测试特定表空间是否可恢复；
RESTORE TABLESPACE example VALIDATE;
测试是否可恢复到特定scn;
RESTORE DATAFILE 1 VALIDATE UNTIL SCN 23456;
查看rman 备份历史
select * from V$RMAN_BACKUP_JOB_DETAILS;
备份文件的集中状态；
Available, meaning that the backup is still present on disk or tape, as recorded in the repository
Expired, meaning that the backup no longer exists on disk or tape, but is still listed in the repository
Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored offsite or on a disk that is currently not mounted)
校验备份文件可用性；
CROSSCHECK BACKUPSET 1345;
CROSSCHECK DATAFILECOPY 1,5;
CROSSCHECK BACKUP;
在crosscheck后，将不存在的备份从备份报告中删除掉；
DELETE EXPIRED BACKUP;
delete expired archivelog all;
当备份磁盘umount的时候，备份文件找不到，又不想将其删除，可暂时设置其状态，防止删除；在闪回恢复区中的备份文件不能设置状态；
CHANGE BACKUPSET 4 UNAVAILABLE;
CHANGE BACKUPSET 4 AVAILABLE;
删除过期的备份；
delete OBSOLETE;
监控闪回恢复区使用情况
select * from V$RECOVERY_FILE_DEST;
select * from V$RECOVERY_AREA_USAGE.
恢复建议，只对CDB有效，无法对PDB提出建议；
Performing Oracle Advised Recovery
LIST FAILURE： Use this command to view problem statements for failures and the effect of these failures on database operations. Each failure is identified by a failure number.
ADVISE FAILURE： Use this command to view repair options, including both automated and manual repair options.
REPAIR FAILURE： Use this command to automatically repair failures listed by the most recent ADVISE FAILURE command.
--手动校验数据库
VALIDATE DATABASE;
---列出错误
LIST FAILURE
LIST FAILURE ... DETAIL
ADVISE FAILURE;
REPAIR FAILURE;
点击(此处)折叠或打开
- ===========================================================================================================================
- 整个详细过程：
- RMAN> list failure;
-
- Database Role: PRIMARY
-
- no failures found that match specification
-
- RMAN> validate database;
-
- Starting validate at 10-MAR-15
- using channel ORA_DISK_1
- channel ORA_DISK_1: starting validation of datafile
- channel ORA_DISK_1: specifying datafile(s) for validation
- input datafile file number=00003 name=/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_sysaux_bh21ho4y_.dbf
- input datafile file number=00001 name=/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_system_bh21jrkg_.dbf
- input datafile file number=00004 name=/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_undotbs1_bh21l6xo_.dbf
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03009: failure of validate command on ORA_DISK_1 channel at 03/10/2015 12:52:16
- ORA-01122: database file 14 failed verification check
- ORA-01110: data file 14: \'/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf\'
- ORA-01565: error in identifying file \'/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf\'
- ORA-27037: unable to obtain file status
- Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
-
- RMAN> list failure;
-
- Database Role: PRIMARY
-
- List of Database Failures
- =========================
-
- Failure ID Priority Status Time Detected Summary
- ---------- -------- --------- ------------- -------
- 2762 HIGH OPEN 10-MAR-15 One or more non-system datafiles are missing
-
- RMAN> list failure 2762 detail;
-
- Database Role: PRIMARY
-
- List of Database Failures
- =========================
-
- Failure ID Priority Status Time Detected Summary
- ---------- -------- --------- ------------- -------
- 2762 HIGH OPEN 10-MAR-15 One or more non-system datafiles are missing
- Impact: See impact for individual child failures
- List of child failures for parent failure ID 2762
- Failure ID Priority Status Time Detected Summary
- ---------- -------- --------- ------------- -------
- 2765 HIGH OPEN 10-MAR-15 Datafile 14: \'/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf\' is missing
- Impact: Some objects in tablespace T might be unavailable
-
-
- RMAN> advise failure;
-
- Database Role: PRIMARY
-
- List of Database Failures
- =========================
-
- Failure ID Priority Status Time Detected Summary
- ---------- -------- --------- ------------- -------
- 2762 HIGH OPEN 10-MAR-15 One or more non-system datafiles are missing
- Impact: See impact for individual child failures
- List of child failures for parent failure ID 2762
- Failure ID Priority Status Time Detected Summary
- ---------- -------- --------- ------------- -------
- 2765 HIGH OPEN 10-MAR-15 Datafile 14: \'/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf\' is missing
- Impact: Some objects in tablespace T might be unavailable
-
- analyzing automatic repair options; this may take some time
- using channel ORA_DISK_1
- analyzing automatic repair options complete
-
- Mandatory Manual Actions
- ========================
- no manual actions available
-
- Optional Manual Actions
- =======================
- 1. If file /u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf was unintentionally renamed or moved, restore it
-
- Automated Repair Options
- ========================
- Option Repair Description
- ------ ------------------
- 1 Restore and recover datafile 14
- Strategy: The repair includes complete media recovery with no data loss
- Repair script: /u01/app/oracle/12/diag/rdbms/orcl/orcl/hm/reco_1682371153.hm
-
- RMAN> repair failure;
-
- Strategy: The repair includes complete media recovery with no data loss
- Repair script: /u01/app/oracle/12/diag/rdbms/orcl/orcl/hm/reco_1682371153.hm
-
- contents of repair script:
- # restore and recover datafile
- sql \'alter database datafile 14 offline\';
- restore ( datafile 14 );
- recover datafile 14;
- sql \'alter database datafile 14 online\';
-
- Do you really want to execute the above repair (enter YES or NO)? yes
- executing repair script
-
- sql statement: alter database datafile 14 offline
-
- Starting restore at 10-MAR-15
- using channel ORA_DISK_1
-
- channel ORA_DISK_1: restoring datafile 00014
- input datafile copy RECID=25 STAMP=873982000 file name=/u01/app/oracle/12/fast_recovery_area/ORCL/datafile/o1_mf_t_bhwxshcd_.dbf
- destination for restore of datafile 00014: /u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwxqg12_.dbf
- channel ORA_DISK_1: copied datafile copy of datafile 00014
- output file name=/u01/app/oracle/12/oradata/ORCL/datafile/o1_mf_t_bhwyh34p_.dbf RECID=0 STAMP=0
- Finished restore at 10-MAR-15
-
- Starting recover at 10-MAR-15
- using channel ORA_DISK_1
-
- starting media recovery
- media recovery complete, elapsed time: 00:00:00
-
- Finished recover at 10-MAR-15
-
- sql statement: alter database datafile 14 online
- repair failure complete
-
- RMAN>
-
- ===========================================================================================================================
Performing User-Directed Recovery
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
查询对象表的依赖关系，对有依赖的表都需要允许row movement;
SELECT other.owner, other.table_name
FROM sys.all_constraints this, sys.all_constraints other
WHERE this.owner = 'HR'
AND this.table_name = 'EMPLOYEES'
AND this.r_owner = other.owner
AND this.r_constraint_name = other.constraint_name
AND this.constraint_type='R';
查看undo表空间内容保留时间
SELECT NAME, VALUE/60 MINUTES_RETAINED
FROM V$PARAMETER
WHERE NAME = 'undo_retention';
将表恢复到某一个时间；
FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');
FLASHBACK TABLE system.tt TO TIMESTAMP TO_TIMESTAMP('2015-03-10 14:42:40', 'YYYY-MM-DD HH24:MI:SS');
恢复drop的表；
要恢复的表不能在system表空间中；只能在本地管理表空间中；
DROP表后；
SELECT * FROM TAB;
BIN$ 开头的表即为删除的表；
SHOW RECYCLEBIN;
FLASHBACK TABLE HR.REG_HIST TO BEFORE DROP;
FLASHBACK DATABASE
注意：You can use the RMAN FLASHBACK DATABASE command to rewind the entire CDB only, not individual PDBs.
将数据库启动到mount状态；
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
将数据库闪回到指定时间点；
FLASHBACK DATABASE to timestamp to_date('2015-03-10 14:42:40', 'YYYY-MM-DD HH24:MI:SS');
切换到只读模式，检查是否闪回成功；
ALTER DATABASE OPEN READ ONLY;
重启，并reset log;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;
通过rman恢复数据库
必须有可用的spfile和控制文件，备份文件；
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
rman>RESTORE DATABASE;
rman>RECOVER DATABASE;
ALTER DATABASE OPEN;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29519108/viewspace-1454881/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29519108/viewspace-1454881/
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
