Oracle-Rman备份恢复单个PDB
前言:
对于Oracle 12C之后的CDB,PDB备份,如果每次恢复都需要恢复整个CDB,那么这将大大增大恢复的时间以及空间,特别是在CDB包含多个PDB的时候,如果我们可以只从备份里面恢复某个PDB,将大大减少恢复的时间以及空间,本文将讲述如何从NBU全备里面恢复某个PDB的操作过程。
环境:
Oracle19c+CDB
恢复过程:
注意:恢复PDB的前提条件是,需要把根容器root恢复处理,才能在此基础上恢复PDB。
1 创建实例orcl,启动到nomount
export ORACLE_SID=orcl---创建目录mkdir -p /u01/app/oracle/admin/orcl/adumpmkdir -p /u01/app/oracle/oradata/orcl/controlfilemkdir -p /u01/app/oracle/oradata/orcl/datafilemkdir -p /u01/app/oracle/oradata/orcl/tempfilemkdir -p /u01/app/oracle/oradata/orcl/onlinelogmkdir -p /u01/app/oracle/arch/orcl/archivelog---使用以下临时的pfile文件启动cat $ORACLE_HOME/dbs/initorcl.ora*._undo_autotune=FALSE*._use_adaptive_log_file_sync='FALSE'*._use_single_log_writer='TRUE'*.aq_tm_processes=1*.archive_lag_target=1200*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.audit_sys_operations=TRUE*.audit_trail='DB'*.autotask_max_active_pdbs=6*.awr_pdb_autoflush_enabled=TRUE*.awr_snapshot_time_offset=1000000*.cell_offload_processing=FALSE*.compatible='19.0.0'*.control_file_record_keep_time=31*.control_files='/u01/app/oracle/oradata/orcl/controlfile/control01.ctl'*.db_block_size=8192*.db_create_file_dest='/u01/app/oracle/oradata'*.db_files=1000*.db_name='ksyhcdb'*.db_unique_name='orcl'*.db_securefile='ALWAYS'*.deferred_segment_creation=FALSE*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.enable_ddl_logging=TRUE*.enable_pluggable_database=true*.fast_start_parallel_rollback='LOW'*.job_queue_processes=1000*.log_archive_dest_1='location=/u01/app/oracle/arch/orcl/archivelog'*.max_dump_file_size='1024M'*.memory_max_target=0*.memory_target=0*.nls_language='AMERICAN'*.nls_territory='AMERICA'*.open_cursors=1000*.open_links=10*.open_links_per_instance=50*.parallel_force_local=TRUE*.parallel_max_servers=4*.parallel_min_servers=0*.parallel_servers_target=4*.pga_aggregate_target=1g*.processes=2000*.recovery_parallelism=2*.remote_login_passwordfile='exclusive'*.result_cache_max_size=0*.sec_case_sensitive_logon=TRUE*.session_cached_cursors=300*.sga_max_size=3g*.sga_target=0*.thread=1*.undo_retention=10800*.undo_tablespace='UNDOTBS1'---数据库启动到nomuntstartup nomount
2 恢复控制文件,启动到mount
rman target /run {ALLOCATE CHANNEL CH10 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';SEND 'NB_ORA_SERV=GZLG-NBUSVR-01,NB_ORA_CLIENT=db202-vip.xxx'; restore controlfile from '/db201-cntrl_329_1_1073532480';RELEASE CHANNEL CH10;}alter database mount;
3 恢复数据文件
---设置omfsqplus / as sysdbaalter system set db_create_file_dest= '/u01/app/oracle/oradata';---恢复数据文件run {ALLOCATE CHANNEL CH00 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH01 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH02 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH03 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH04 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH05 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH06 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH07 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';SEND 'NB_ORA_SERV=xxx-NBUSVR-01,NB_ORA_CLIENT=db202-vip.xxx'; restore database root;restore pluggable database orcl;switch datafile all;switch tempfile all;RELEASE CHANNEL CH00;RELEASE CHANNEL CH01;RELEASE CHANNEL CH02;RELEASE CHANNEL CH03;RELEASE CHANNEL CH04;RELEASE CHANNEL CH05;RELEASE CHANNEL CH06;RELEASE CHANNEL CH07;}---语句批量生成跳过表空间set pagesize 400set linesize 400select '"'||b.name||'"'||':'||a.name||','from v$tablespace a,v$containers bwhere a.con_id=b.con_id and b.name not in ('CDB$ROOT','orcl');----recover数据库,跳过除了root,orcl之外的pdb表空间run {ALLOCATE CHANNEL CH00 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH01 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH02 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH03 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH04 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH05 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH06 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH07 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';SEND 'NB_ORA_SERV=xxx-NBUSVR-01,NB_ORA_CLIENT=db202-vip.xxx'; recover database skip forever tablespace "PDB$SEED":SYSAUX,"PDB$SEED":TEMP,"PDB$SEED":UNDOTBS1,"PDB$SEED":SYSTEM,"TESTDB":PSS_SDI_IDX,"TESTDB":PSS_SDI_DATA,"TESTDB":TEMP,"TESTDB":UNDOTBS1,"TESTDB":SYSAUX,"TESTDB":SYSTEM,"TESTDB":UNDO_2;RELEASE CHANNEL CH00;RELEASE CHANNEL CH01;RELEASE CHANNEL CH02;RELEASE CHANNEL CH03;RELEASE CHANNEL CH04;RELEASE CHANNEL CH05;RELEASE CHANNEL CH06;RELEASE CHANNEL CH07;}
4 开启数据库
---检查文件的恢复时间点alter session set nls_date_Format='yyyy-mm-dd hh24:mi:ss';set linesize 300set pagesize 400col name for a100select file#,name,to_char(CHECKPOINT_TIME),fuzzyfrom v$datafile_header; ---关闭归档alter database noarchivelog;---清空线程thread 1日志select 'alter database clear logfile group '||group#||';'from v$logwhere thread#=1;---删除standby logselect 'alter database drop logfile group '||group#||';'from v$standby_log;---关闭块追踪alter database disable block change tracking;---打开数据库alter database open resetlogs;---打开orcl pdbalter pluggable database orcl open;
如果想在已经恢复的根容器里面继续恢复pdb,可以这么做
1 确认根容器具备恢复的条件
---注意当前容器是要恢复pdb所在的原容器---确认当前容器的控制文件里面有恢复的pdb信息以及开启归档SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 TESTDB MOUNTED 5 ORCL MOUNTEDSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/arch/TESTDB/archivelogOldest online log sequence 1Next log sequence to archive 1Current log sequence 1
2 恢复testdb的数据文件
---设置omfsqplus / as sysdbaalter system set db_create_file_dest= '/u01/app/oracle/oradata';---恢复数据run {ALLOCATE CHANNEL CH00 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH01 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH02 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH03 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH04 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH05 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH06 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';ALLOCATE CHANNEL CH07 TYPE 'SBT_TAPE' PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';SEND 'NB_ORA_SERV=xxx-NBUSVR-01,NB_ORA_CLIENT=db202-vip.xxx'; restore pluggable database testdb;recover pluggable database testdb;switch datafile all;switch tempfile all;RELEASE CHANNEL CH00;RELEASE CHANNEL CH01;RELEASE CHANNEL CH02;RELEASE CHANNEL CH03;RELEASE CHANNEL CH04;RELEASE CHANNEL CH05;RELEASE CHANNEL CH06;RELEASE CHANNEL CH07;}
3 开启数据库
---检查文件的恢复时间点alter session set container=testdb;alter session set nls_date_Format='yyyy-mm-dd hh24:mi:ss';set linesize 300set pagesize 400col name for a100select file#,name,to_char(CHECKPOINT_TIME),fuzzyfrom v$datafile_header; ---查询数据文件状态alter session set container=testdb;select name,statusFrom v$datafile;alter session set container=testdb;select name,statusFrom v$datafile;---批量online数据文件alter session set container=testdb;select 'alter database datafile '||file#||' online;'from v$datafile;---online数据文件alter database datafile 59 online;alter database datafile 60 online;alter database datafile 61 online;alter database datafile 63 online;alter database datafile 64 online;alter database datafile 83 online;---打开testdb pdbalter pluggable database testdb open;show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 4 testdb READ WRITE NO