oracle12c Performing Backup and Recovery文件筆記

看文件筆記
官方文件：9 Performing Backup and Recovery



一。直接備份資料庫

透過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;






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;