TRẦN VĂN BÌNH MASTER: Backup và Recovery của Container Database (CDB) và Pluggable Database (PDB) trong Oracle Database 12c/18c/19c/21c

Chủ Nhật, 27 tháng 12, 2020

Backup và Recovery của Container Database (CDB) và Pluggable Database (PDB) trong Oracle Database 12c/18c/19c/21c

NỘI DUNG

1. KẾT NỐI TỚI RMAN

2. BACKUP

Container Database (CDB) Backup

Root Container Backup

Pluggable Database (PDB) Backup

Tablespace và Datafile Backups

Archived Redo Log Backups

3. RECOVERY ĐẦY ĐỦ

Root Container Complete Recovery

Pluggable Database (PDB) Complete Recovery

Tablespace and Datafile Complete Recovery

4. POINT IN TIME RECOVERY (PITR) (KHÔNG ĐẦY ĐỦ)

Container Database (CDB) Point In Time Recovery (PITR)

Pluggable Database (PDB) Point In Time Recovery (PITR)

Table Point In Time Recovery (PITR) in PDBs

1. KẾT NỐI TỚI RMAN

Kết nối tới root container trong CDB với quyền "AS SYSDBA":

$ export ORAENV_ASK=NO
$ export ORACLE_SID=cdb1
$ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
$ export ORAENV_ASK=YES

$ rman target=/
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Dec 22 17:03:20 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=797615285)

RMAN>

Kết nối tới PDB:

Cấu hình trong tnsnames.ora trước:

pdb1=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbaviet)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb`)

)

$ rman target=sys@pdb1
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Dec 23 11:08:35 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

target database Password: 
connected to target database: CDB1 (DBID=797615285)

RMAN>

2. BACKUP

Container Database (CDB) Backup

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

(Backup CDB (cdb1) và mọi PDBs (pdb1, pdb2, pdb$seed) vào bản backup):

Starting backup at 22-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/cdb1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/cdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-13
channel ORA_DISK_1: finished piece 1 at 22-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4wr40_.bkp tag=TAG20131222T163015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-13
channel ORA_DISK_1: finished piece 1 at 22-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E45393F0DE5F1A8AE043D200A8C00DFC/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4z3so_.bkp tag=TAG20131222T163015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00030 name=/u01/app/oracle/oradata/pdb2/sysaux01.dbf
input datafile file number=00029 name=/u01/app/oracle/oradata/pdb2/system01.dbf
input datafile file number=00031 name=/u01/app/oracle/oradata/pdb2/pdb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-13
channel ORA_DISK_1: finished piece 1 at 22-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E4B0CA84B47E6183E043D200A8C0A806/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg50766_.bkp tag=TAG20131222T163015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 22-DEC-13
channel ORA_DISK_1: finished piece 1 at 22-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E453004B82C71772E043D200A8C08EC5/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg51bmg_.bkp tag=TAG20131222T163015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 22-DEC-13

Root Container Backup

Backup của root container là chỉ backup CDB, không backup PDBs:

$ rman target=/

RMAN> BACKUP DATABASE ROOT;

Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/cdb1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/cdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-13
channel ORA_DISK_1: finished piece 1 at 23-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T112413_9cj7bxtg_.bkp tag=TAG20131223T112413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 23-DEC-13

Pluggable Database (PDB) Backup

Có 2 cách:

* Cách 1: Vào root container và backup 1 hoặc nhiều PDBs:

$ rman target=/

RMAN> BACKUP PLUGGABLE DATABASE pdb1, pdb2;

Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-13
channel ORA_DISK_1: finished piece 1 at 23-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E45393F0DE5F1A8AE043D200A8C00DFC/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T113119_9cj7r8lp_.bkp tag=TAG20131223T113119 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00030 name=/u01/app/oracle/oradata/pdb2/sysaux01.dbf
input datafile file number=00029 name=/u01/app/oracle/oradata/pdb2/system01.dbf
input datafile file number=00031 name=/u01/app/oracle/oradata/pdb2/pdb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-13
channel ORA_DISK_1: finished piece 1 at 23-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E4B0CA84B47E6183E043D200A8C0A806/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T113119_9cj7sfbx_.bkp tag=TAG20131223T113119 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 23-DEC-13

* Cách 2: Vào đúng PDB

$ rman target=sys@pdb1

RMAN> BACKUP DATABASE;

Starting backup at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-DEC-13
channel ORA_DISK_1: finished piece 1 at 23-DEC-13
piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E45393F0DE5F1A8AE043D200A8C00DFC/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T113504_9cj7z9kb_.bkp tag=TAG20131223T113504 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 23-DEC-13

Tablespace và Datafile Backups

$ rman target=sys@pdb1

RMAN> BACKUP TABLESPACE system, sysaux, users;

$ rman target=sys@cdb1

RMAN> BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;

$ rman target=/

# Or

$ rman target=sys@pdb1

RMAN> BACKUP DATAFILE 8, 9, 10;

RMAN> BACKUP DATAFILE 1;

Starting backup at 23-DEC-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/23/2013 11:49:35
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 1

RMAN>

Archived Redo Log Backups

Đây là mức root container (không có mức PDB):

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP ARCHIVELOG ALL;

3. Recovery đầy đủ

Khi restore CDB sẽ restore root container và mọi PDB

$ rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN;
}

Starting restore at 22-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

skipping datafile 5; already restored to file /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4wr40_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4wr40_.bkp tag=TAG20131222T163015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/E45393F0DE5F1A8AE043D200A8C00DFC/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4z3so_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E45393F0DE5F1A8AE043D200A8C00DFC/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg4z3so_.bkp tag=TAG20131222T163015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00029 to /u01/app/oracle/oradata/pdb2/system01.dbf
channel ORA_DISK_1: restoring datafile 00030 to /u01/app/oracle/oradata/pdb2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00031 to /u01/app/oracle/oradata/pdb2/pdb2_users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/E4B0CA84B47E6183E043D200A8C0A806/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg50766_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/E4B0CA84B47E6183E043D200A8C0A806/backupset/2013_12_22/o1_mf_nnndf_TAG20131222T163015_9cg50766_.bkp tag=TAG20131222T163015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 22-DEC-13

Starting recover at 22-DEC-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:10

Finished recover at 22-DEC-13

Statement processed

Root Container Complete Recovery

$ rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE ROOT;
  RECOVER DATABASE ROOT;
  # Consider recovering PDBs before opening.
  ALTER DATABASE OPEN;
}

Starting restore at 23-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=247 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T112413_9cj7bxtg_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2013_12_23/o1_mf_nnndf_TAG20131223T112413_9cj7bxtg_.bkp tag=TAG20131223T112413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 23-DEC-13

Starting recover at 23-DEC-13
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 23-DEC-13

Pluggable Database (PDB) Complete Recovery

Cách 1: root container

$ rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
  RESTORE PLUGGABLE DATABASE pdb1, pdb2;
  RECOVER PLUGGABLE DATABASE pdb1, pdb2;
  ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

Cách 2: vào đúng PDB1

$ sqlplus sys@pdb1 as sysdba

CREATE USER admin_user IDENTIFIED BY admin_user;
GRANT CREATE SESSION, PDB_DBA, SYSDBA TO admin_user;
EXIT;

$ rman target=admin_user@pdb1

SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;

Tablespace and Datafile Complete Recovery

$ rman target=sys@pdb1

RUN {
  ALTER TABLESPACE users OFFLINE;
  RESTORE TABLESPACE users;
  RECOVER TABLESPACE users;
  ALTER TABLESPACE users ONLINE;
}

$ rman target=/

# Or

$ rman target=sys@pdb1

RUN {
  ALTER DATABASE DATAFILE 10 OFFLINE;
  RESTORE DATAFILE 10;
  RECOVER DATAFILE 10;
  ALTER DATABASE DATAFILE 10 ONLINE;
}

4. Point In Time Recovery (PITR)

Container Database (CDB) Point In Time Recovery (PITR)

$ rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  # Should probably open read-only and check it out first.
  ALTER DATABASE OPEN RESETLOGS;
}

Pluggable Database (PDB) Point In Time Recovery (PITR)

$ rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

Table Point In Time Recovery (PITR) in PDBs

$ rman target=/

# SCN
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

# TIME
RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL TIME "TO_DATE('01-JAN-2013 15:00', 'DD-MON-YYYY HH24:MI')"
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux'
  DATAPUMP DESTINATION '/u01/export'
  DUMP FILE 'test_t1_prev.dmp'
  NOTABLEIMPORT;

==================================

* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE là khóa học mà bất kỳ DBA nào cũng cần, khai giảng hàng tháng, đăng ký sớm để nhận ưu đãi (chỉ 10 bạn đầu tiên).

* INBOX: https://m.me/tranvanbinh.vn hoặc Hotline 090.29.12.888

* Chi tiết tham khảo:

https://bit.ly/oaz_w

hoặc

https://bit.ly/oaz_fp

==================================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
#OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #tự học oracle