Oracle-单个PDB的备份脚本以及恢复步骤

最新推荐文章于 2024-06-24 22:28:26 发布

牛牛的笔记

最新推荐文章于 2024-06-24 22:28:26 发布

阅读量1.1k

点赞数 10

本文链接：https://blog.csdn.net/sinat_36757755/article/details/135581367

版权

前言:

如今越来越多的用户在生产环境使用Oracle12c之后的容器数据库架构进行用户数据库的部署，随着PDB的数量以及数据量的增长，CDB容器整体数据量变得非常巨大，这使得在对CDB容器数据库进行RMAN备份以及恢复时可能存在以下问题

1 传统方式使用RMAN对整个CDB容器进行备份，备份窗口时间很长，备份方式不灵活，备份了不必要的数据导致出现空间浪费

2 备份恢复时，如果恢复整个CDB容器里面全部的PDB数据，恢复时间长，恢复方式不灵活，恢复不必要的数据

为此在对CDB容器数据库进行备份恢复时，可以采取更加灵活适合的备份策略以及恢复方式，本文接下来将介绍如何使用rman备份方式对单个pdb进行备份以及从备份里面恢复单个PD具体步骤

备份PDB脚本

恢复单个PDB数据库，需要建立在root根容器的基础上，所以每次备份PDB时都需要把root根容器、归档日志、控制文件以及spfile文件也一并备份

0级备份脚本模板：备份控制文件、spfile文件、根容器全量数据文件、单个PDB容器的全量数据文件以及归档日志

注：请根据实际的环境以及备份保留策略对备份脚本进行修改，再进行测试使用

#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0
export ORACLE_SID=racdb1
export PATH=$PATH:$ORACLE_HOME/bin:/usr/sbin
export BACKUP_DIR=/u01/backup/testpdb/rman_inc0/testpdb
export BACKUP_DATE=`date +%F`
export BACKUP_LOG=$BACKUP_DIR/$BACKUP_DATE/rman_inc0_$(date +%Y%m%d).log
mkdir -p $BACKUP_DIR/$BACKUP_DATE
rman target / log $BACKUP_LOG<<EOF
run{
show all;
allocate channel c1 device type disk maxpiecesize 30g;
allocate channel c2 device type disk maxpiecesize 30g;
allocate channel c3 device type disk maxpiecesize 30g;
allocate channel c4 device type disk maxpiecesize 30g;
backup current controlfile format "$BACKUP_DIR/$BACKUP_DATE/control_file_inc0_%U_%T_%s.ctl";
backup  as compressed backupset  incremental level 0 tag 'full_inc0_root' format "$BACKUP_DIR/$BACKUP_DATE/inc0_%U_%T_%s.full" database root;
backup  as compressed backupset  incremental level 0 tag 'full_inc0_testpdb' format "$BACKUP_DIR/$BACKUP_DATE/inc0_%U_%T_%s.full" pluggable database testpdb;
sql 'alter system archive log current';
backup as compressed backupset archivelog all format "$BACKUP_DIR/$BACKUP_DATE/archive_log_inc0_%U_%T_%s.arc" ;
backup current controlfile format "$BACKUP_DIR/$BACKUP_DATE/control_file_inc0_%U_%T_%s.ctl";
backup spfile format "$BACKUP_DIR/$BACKUP_DATE/spfile_file_inc0_%U_%T_%s.spf";
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
delete noprompt  backup completed before 'sysdate-7';
delete noprompt archivelog all completed before 'sysdate-6';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
EOF

恢复单个PDB步骤

以下恢复步骤，描述如何从备份里面恢复单实例的PDB

1 确认当前控制文件包含的备份片信息是否满足要恢复的根容器以及PDB需要

--注册备份片
rman target /
catalog start with '/backup';
--确认备份片
rman target /
list backup of database root;
list backup of database testpdb;
--验证备份
rman target /
restore database root preview;
restore pluggable database testpdb preview;

2 创建审计、控制文件、数据文件、临时文件、日志文件以及归档日志目录

mkdir -p /u01/app/oracle/admin/test/adump
mkdir -p /u01/app/oracle/oradata/test/controlfile
mkdir -p /u01/app/oracle/oradata/test/datafile
mkdir -p /u01/app/oracle/oradata/test/tempfile
mkdir -p /u01/app/oracle/oradata/test/onlinelog
mkdir -p /u01/app/oracle/arch/test/archivelog

3 使用临时的pfile参数文件初始化恢复的实例并启动到nomount状态

注：也可以先将数据库rman启动到dummy模式，从备份里面恢复spfile文件，在通过该参数文件数据初始化实例

---pfile模板，参数请根据实际环境进行调整
*._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/test/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='12.2.0'
*.control_file_record_keep_time=31
*.control_files='/u01/app/oracle/oradata/test/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_files=1000
*.db_name='orcl'
*.db_unique_name='test'
*.db_securefile='ALWAYS'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.enable_ddl_logging=TRUE
*.enable_pluggable_database=true
*.fast_start_parallel_rollback='LOW'
*.job_queue_processes=1000
*.log_archive_dest_1='location=/u01/app/oradata/arch'
*.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=10g
*.sga_target=0
*.thread=1
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
---启动到nomount
export ORACLE_SID=test
startup nomount pfile=inittest.ora

4 从备份里面恢复控制文件并将实例启动到mount状态

---恢复控制文件
rman target /
run {
ALLOCATE CHANNEL CH10 TYPE disk;
restore controlfile from '/backup/control_xxxx';
RELEASE CHANNEL CH10;
}
---启动到mount
alter database mount;

5 恢复根容器root以及pdb数据库testpdb的全量数据文件

---设置omf
sqlplus / as sysdba
alter system set db_create_file_dest= '/u01/app/oracle/oradata';
---恢复根容器root以及pdb数据库testpdb
rman target /
run {
ALLOCATE CHANNEL CH00 TYPE DISK;
ALLOCATE CHANNEL CH01 TYPE DISK;
ALLOCATE CHANNEL CH02 TYPE DISK;
ALLOCATE CHANNEL CH03 TYPE DISK;
ALLOCATE CHANNEL CH04 TYPE DISK;
ALLOCATE CHANNEL CH05 TYPE DISK;
ALLOCATE CHANNEL CH06 TYPE DISK;
ALLOCATE CHANNEL CH07 TYPE DISK; 
restore database root;
restore pluggable database testpdb;
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;
}

6 对数据库进行recover，追增量备份数据

注：recover的时候，要把不需要恢复的pdb表空间或者数据文件skip跳过，否则recover恢复会出现文件需要restore的报错

---语句批量生成跳过表空间
set pagesize 400
set linesize 400
select '"'||b.name||'"'||':'||a.name||','
from v$tablespace a,v$containers b
where a.con_id=b.con_id and b.name not in ('CDB$ROOT','testpdb');
---recover 数据库
rman target /
run {
ALLOCATE CHANNEL CH00 TYPE disk;
ALLOCATE CHANNEL CH01 TYPE disk;
ALLOCATE CHANNEL CH02 TYPE disk;
ALLOCATE CHANNEL CH03 TYPE disk;
ALLOCATE CHANNEL CH04 TYPE disk;
ALLOCATE CHANNEL CH05 TYPE disk;
ALLOCATE CHANNEL CH06 TYPE disk;
ALLOCATE CHANNEL CH07 TYPE disk; 
recover database skip forever tablespace 
"PDB$SEED":SYSTEM,"PDB$SEED":SYSAUX,"PDB$SEED":UNDOTBS1,"PDB$SEED":TEMP,"ORCL1PDB1":UNDOTBS1,"ORCL1PDB1":SYSAUX,"ORCL1PDB1":SYSTEM,"ORCL1PDB1":TEMP,"ORCL1PDB1":USERS,"ORCL1PDB2":SYSTEM,"ORCL1PDB2":USERS,"ORCL1PDB2":TEMP,"ORCL1PDB2":UNDOTBS1,"ORCL1PDB2":SYSAUX;
RELEASE CHANNEL CH00;
RELEASE CHANNEL CH01;
RELEASE CHANNEL CH02;
RELEASE CHANNEL CH03;
RELEASE CHANNEL CH04;
RELEASE CHANNEL CH05;
RELEASE CHANNEL CH06;
RELEASE CHANNEL CH07;
}

7 最后执行以下检查操作，然后open resetlogs方式打开数据库，打开PDB完成恢复操作

a 检查数据文件恢复的时间点以及fuuzy状态是否一致为NO

b 进程临时文件的路径是否正确

c 关闭数据库归档模式以及块跟踪模式

d 重置redo日志，删除standby log日志

---检查文件的恢复时间点，确认恢复的时间点以及fuzzy状态一致为NO
alter session set nls_date_Format='yyyy-mm-dd hh24:mi:ss';
set linesize 300
set pagesize 400
col name for a100
select file#,name,to_char(CHECKPOINT_TIME),fuzzy
from v$datafile_header;
---检查tempfile文件的路径是否正确
select name from v$tempfile;
---关闭归档
alter database noarchivelog;
---重置日志
select 'alter database clear logfile group '||group#||';'
from v$log
---删除standby log
select 'alter database drop logfile group '||group#||';'
from v$standby_log;
---关闭块追踪
alter database disable block change tracking;
---打开数据库
alter database open resetlogs;
---打开testpdb pdb
alter pluggable database testpdb open;

牛牛的笔记

关注

10
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
Oracle-单个PDB的备份脚本以及恢复步骤

如今越来越多的用户在生产环境使用Oracle12c之后的容器数据库架构进行用户数据库的部署，随着PDB的数量以及数据量的增长，CDB容器整体数据量变得非常巨大，这使得在对CDB容器数据库进行RMAN备份以及恢复存在操作瓶颈
复制链接

扫一扫