ITPub博客

首页 > 数据库 > Oracle > 【Oracle19c】Oracle19c rman使用简单测试

【Oracle19c】Oracle19c rman使用简单测试

原创 Oracle 作者:xysoul_云龙 时间:2020-03-11 14:08:29 0 删除 编辑

本次我们主要一起学习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');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29487349/viewspace-2679607/,如需转载,请注明出处,否则将追究法律责任。

全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。岁月老将至,如有幸在江湖中留点踪迹,也是欣慰。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    179
  • 访问量
    784457