### 本次我们主要一起学习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 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. 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'); ```