### 本次我們主要一起學習rman相關使用，包含複製，遷移
---

##### 19c新特性
- PDB 支援 資料庫
```
--連線示例
 connect target "sbu@prod AS SYSBACKUP"
```
- 自動刪除閃回日誌

```
--預設1440分鐘/一天，19c開始超過自動清理
db_flashback_retention_target
```

##### 18C新特性
- 複製一個pdb到另一個存在的cdb中
- 複製資料庫到Oracle雲
- 前滾物理備庫，簡化使用
- RMAN備份可以用遷移，pdb、cdb切換
- 塊丟失防寫增強

##### 部分功能測試

###### 全備

```
connect target  /
run {
	allocate channel ch1 type disk;
	allocate channel ch2 type disk;
	alter system archive log current;
	alter system archive log current;
	backup format '/backup/mypdb_full_%T_%s_%p' database plus archivelog delete all input;
	backup format '/backup/mypdb_controlfile_%T_%s_%p' current controlfile;
	alter system archive log current;
	backup format '/backup/mypdb_arch_%Y%M%D_%s_%p' archivelog all;
	release channel ch1;
	release channel ch2;
}
```
> 全備備份的資料檔案參考

```
--其中資料檔案2,5,6是pdb模板
col file_name for a50
select file_id,file_name,tablespace_name from cdb_data_files order by file_id;
```

###### 備份pdb資料庫

```
--更相信命令，參考全備
BACKUP PLUGGABLE DATABASE mypdb;
--or
[oracle@node216 ~]$ rman target sys/oracle@mypdb

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Feb 14 21:57:32 2020
Version 19.3.0.0.0

connected to target database: MYDB19C:MYPDB (DBID=2219134196)

RMAN> backup database;
```
###### 備份cdb,pdbseed

```
--備份root+pdbseed 建議一起備份
run {
	allocate channel ch1 type disk;
	allocate channel ch2 type disk;
	alter system archive log current;
	alter system archive log current;
	backup format '/backup/root_full_%T_%s_%p' database root plus archivelog delete all input;
	backup format '/backup/pdbseed_full_%T_%s_%p' database 'PDB$SEED';
	backup format '/backup/root_controlfile_%T_%s_%p' current controlfile;
	alter system archive log current;
	backup format '/backup/root_arch_%Y%M%D_%s_%p' archivelog all;
	release channel ch1;
	release channel ch2;
}

```

##### 恢復測試

```
--恢復root+pdbseed ,也可單獨恢復
 restore controlfile from '/backup/root_controlfile_20200215_62_1';
 alter database mount;
 run {
     restore database root;
     restore database 'pdb$seed';
     recover database root;
     recover database 'pdb$seed';
 }

--跳過pdb 資料庫，open
alter session set container=mypdb;
col name for a50
select file#,name,status from v$datafile;
alter database datafile 9,10,11,12,13 offline;
alter database open resetlogs;

--恢復pdb
col pdb_name for a20
select pdb_name,status from dba_pdbs;
restore pluggable database mypdb;
recover pluggable database mypdb;
alter session set container=mypdb;
alter database datafile 9,10,11,12,13 online;
alter database open;
--or
alter session set container="CDB$ROOT";
alter pluggable database mypdb open;
```

###### pdb遷移，no-cdb pdb

```
--備份
BACKUP format '/backup/testpdb_full_%T_%s_%p' PLUGGABLE DATABASE testpdb PLUS ARCHIVELOG;

--解除安裝/匯出 pdb
sqlplus / as sysdba
alter pluggable database testpdb unplug into '/home/oracle/testpdb.xml';
--刪除pdb,遷移可不刪除,注意不能刪除資料檔案
drop pluggable database testpdb keep datafiles;

--檢查目標cdb，是否適用 備份的pdb
SET SERVEROUTPUT ON
 DECLARE
 compatible CONSTANT VARCHAR2(3) :=
 CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
 pdb_descr_file => '/home/oracle/testpdb.xml')
 WHEN TRUE THEN 'YES'
 ELSE 'NO'
 END;
 BEGIN
 DBMS_OUTPUT.PUT_LINE(compatible);
 END;
 /
 
 --建立pdb
create pluggable database testpdb using '/home/oracle/testpdb.xml'   --nocopy tempfile reuse;
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/MYDB19C/pdbseed/', '/u01/app/oracle/oradata/testpdb');

--open
 alter pluggable database testpdb open;
 
 --恢復，手動刪除原有資料檔案,可不刪除，直接恢復
 SET PREPLUGIN CONTAINER=testpdb;
 LIST PREPLUGIN BACKUP OF PLUGGABLE DATABASE testpdb;
 
 --恢復
  RESTORE PLUGGABLE DATABASE testpdb FROM PREPLUGIN;
  RECOVER PLUGGABLE DATABASE testpdb FROM PREPLUGIN;
  catalog preplugin archivelog '/u01/app/oracle_base/oradata/CDB18/arch/1_26_969723966.dbf';
  
  --檢查
  RESTORE PLUGGABLE DATABASE testpdb SKIP PREPLUGIN;
  
  --open
  RECOVER PLUGGABLE DATABASE testpdb;
  ALTER PLUGGABLE DATABASE testpdb open;
  
  --or
  rman target sys/oracle@testpdb
  recover database;
```

> 參考：
https://mandysandhu.com/2018/03/15/oracle-18c-preplugin-backups-including-restore-and-recovery-test

- How to use Rman to Restore Of Single PDB in Multitenant to Alternate Server (文件 ID 2142675.1)
- Missing PDB$SEED's Datafiles with no backup (文件 ID 2104370.1)
- R12c 新特性：RMAN 可插拔資料庫的備份和恢復 (文件 ID 1945849.1)

---
##### Duplicating pdb 複製到已存在的另一個cdb中
###### 限制
- > Only active database duplication is supported.
- > Only the following clauses of the DUPLICATE command are supported: NORESUME,
DB_FILE_NAME_CONVERT, SECTION SIZE, and USING COMPRESSED BACKUPSET
- > The following clauses of the DUPLICATE command are not supported: SPFILE, NO
STANDBY, FARSYNC STANDBY, and LOG_FILE_NAME_CONVERT.
- > Duplicating a PDB to a CDB that is a standby database is not supported
- > Only one PDB can be duplicated at a time.
- > Partial PDB duplication is not supported, only complete PDB duplication is
supported. For example, you cannot include or exclude specific tablespaces while
duplicating a PDB
- > Duplicating a non-CDB as a PDB in an existing CDB is not supported.
- >  Duplicating PDBs that contain TDE-encrypted tablespaces is not supported.

###### 步驟

```
--配置網路
MYDB19C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node216)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb19c)
    )
  )

TESTCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node217)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testcdb)
    )
  )

--建立相關目錄，目標端
mkdir -p /u01/app/oracle/oradata/TESTCDB/mystpdb
--臨時存放歸檔等
mkdir -p /backup/mystpdb
alter system set remote_recovery_file_dest='/backup/mystpdb';

--連線
rman target sys/oracle@mydb19c auxiliary sys/oracle@testcdb
--開始複製
DUPLICATE PLUGGABLE DATABASE mypdb AS mystpdb TO testcdb FROM ACTIVE DATABASE
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/mypdb','/u01/app/oracle/oradata/TESTCDB/mystpdb';

--會自動匯入相關後設資料
```
> ###### 複製大概過程
- 恢復資料檔案：restore complete of datafile 14 to datafile copy
- 建立pdb

```
CREATE PLUGGABLE DATABASE mystpdb AS CLONE USING '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_1_testcdb.xml'        SOURCE_FILE_NAME_CONVERT = (  '/u01/app/oracle/oradata/mypdb/mypdp_system01.dbf' , '/u01/app/oracle/oradata/TESTCDB/mystpdb/mypdp_system01.dbf') NOCOPY REFRESH MODE MANUAL;
```
- 複製歸檔至目標端，並應用，增量/不完成恢復完成
- 調整資料檔案號：刪除之前，根據新環境整理新的file_id
- open pdb

###### 遠端克隆方式

```
--源端設定使用者許可權
alter session set container=mypdb;
grant create pluggable database to mypdb;
--目標端建立dblink
 create database link clonePdb connect to mypdb identified by mypdb using '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.216)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = mypdb)))';
 --開始建立，與Duplicating 處理過程相似
create pluggable database pdb8 from mypdb@clonePdb FILE_NAME_CONVERT=('/u01/app/oracle/oradata/mypdb','/u01/app/oracle/oradata/TESTCDB/pdb8');
```

【Oracle19c】Oracle19c rman使用簡單測試

相關文章