一、情形描述
注：这是最近生产遇到的一个特殊场景，因备库空间紧张，需要将备库的一个PDB停了并删除数据文件来释放空间。
如有更好办法的可以一起交流，限于技术水平，难免有纰漏之处，还望多多指正。
有一套19C RAC两节点生产库，数据库版本为Oracle 19.15，每个节点上有一个实例，该实例对应两个PDB。因最近业务需要，需将新增的多个语种业务导入到其中一个PDB1，另一个PDB2为业务过度库，这期间插入的数据在转移到PDB1后，还会删除部分数据。
这套生产主库在同城另一个机房存在一个单节点备机，备机采用的是本地SSD盘，目前服务器上所有盘插槽已用完，无法再扩展SSD盘，备库和主库设置了三天延迟应用主库日志。
目前由于业务持续不断的往主库插入数据，并产生大量归档，导致备库ASM存储空间紧张，将三天延迟改为实时同步，并保留一天归档日志，但没多久空间又产生告警。
在和业务沟通后，业务反馈可以将PDB2不进行DG同步，可在备库将其删除，只要在主库定期对PDB2下的相关业务表进行备份，备库删除PDB2,这样可以为备库腾出将近1.9T的ASM存储空间，并希望在备库能在较短的时间恢复PDB2这一PDB。
为此决定在备库采用disable recovery来停PDB2,并将对应数据文件删除，恢复采用restore pluggable database，以下是在生产上的测试过程。
二、问题处理
2.1 停备库PDB删数据文件
以下操作均在备库执行。
-- 登陆备库数据库
sqlplus / as sysdba
-- 查询备库同步模式
SQL> select distinct recovery_mode from v$archive_dest_status;
RECOVERY_MODE
----------------------------------
IDLE
MANAGED REAL TIME APPLY
-- 停止备库应用日志
SQL> alter database recover managed standby database cancel;
Database altered.
-- 关闭备库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
-- 启动到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3.2212E+10 bytes
Fixed Size 37471152 bytes
Variable Size 4026531840 bytes
Database Buffers 2.8119E+10 bytes
Redo Buffers 29634560 bytes
Database mounted.
-- 查看当前备库各PDB状态
SQL> show pdbs
2 PDB$SEED MOUNTED
3 PDB1 MOUNTED
4 PDB2 MOUNTED
-- 切换到PDB2
SQL> alter session set container=pdb2;
Session altered.
-- 将PDB2执行disable
SQL> alter pluggable database disable recovery;
Pluggable database altered.
SQL> show pdbs
4 MLANTRANS MOUNTED
SQL> conn / as sysdba
Connected.
-- 重新打开备库
SQL> alter database open;
Database altered.
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
-- 启动PDB1
SQL> alter session set container=PDB1;
Session altered.
SQL> alter database open;
Database altered.
SQL> conn / as sysdba
Connected.
-- 备库开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB2 MOUNTED
SQL> set line 500
SQL> col source_db_unique_name for a20
SQL> col name for a30
SQL> col value for a20
SQL> col unit for a30
SQL> select * from v$dataguard_stats;
0 transport lag day(2) to second(0) interval 03/06/2023 13:09:00 0
0 apply lag day(2) to second(0) interval 03/06/2023 13:09:00 0
0 apply finish time day(2) to second(3) interval 03/06/2023 13:09:00 0
0 estimated startup time 22 second 03/06/2023 13:09:00 0
SQL> select con_id, name, open_mode, recovery_status from v$pdbs order by con_id, name;
select con_id, name from v$datafile order by con_id; 2 PDB$SEED READ ONLY ENABLED
3 PDB1 READ ONLY ENABLED
4 PDB2 MOUNTED DISABLED
SQL> alter session set container=PDB2;
Session altered.
SQL> set lines 120
SQL> set pages 9999
SQL> col name for a260
-- 查询PDB2所含的数据文件
SQL> select name,status from v$datafile;
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/system.2141.1102524951 SYSOFF
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/sysaux.2143.1102524967 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/undotbs1.2145.1102524983 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/undo_2.2146.1102524983 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/users.2147.1102525001 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/xxx_core_2005.2149.1102525009 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxxqb.2152.1102525017 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxx_idx.2169.1102525185 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxx.2171.1102525221 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/ogg.2184.1102525807 RECOVER
+DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/xxxx_tbs.2358.1102529703 RECOVER
11 rows selected.
此时无法使用SQL语句删除PDB2下的表空间，如使用SQL语句删除PDB下的表空间会报如下错误：
SQL> drop tablespace system including contents and datafiles;
drop tablespace system including contents and datafiles
*
ERROR at line 1:
ORA-01109: database not open
此时只能借助物理删除，使用grid用户登录到asm，然后cd 到 +DG/XXXCDB/D824F08209B54437E0532310A8C08619/ 目录下，执行：
ASMCMD> cd +DG/XXXCDB/D824F08209B54437E0532310A8C08619 rm -rf DATAFILE rm -rf TEMPFILE
此时，可查看到备库ASM存储剩余空间增长了将近1.9T，此时主备同步正常，并可以在主库测试创建一个表插入数据，在备库查询数据是否同步。
2.2 快速恢复备库PDB
恢复备库PDB的操作也可以在备库端进行，前提是需要在备库Oracle用户下$ORACLE_HOME/network/admin下tnsnames.ora里配置了连接主库的tns连接串信息，如配置了连接串，可以通过rman restore pluggable database的方式来恢复备库PDB。
-- 备库连接主库tns执行rman 恢复
[oracle@xxx-xxx admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Mar 6 13:21:39 2023
Version 19.14.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: XXXCDB (DBID=xxxx)
--xxxcdb16是备库tnsnames.ora里配置的连接主库的tns串信息
RMAN> run{
2> restore pluggable database pdb2 from service xxxcdb16 ;
3> }
Starting restore at 2023:03:0613:21:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1519 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/system.2141.1102524951
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/sysaux.2143.1102524967
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/undotbs1.2145.1102524983
channel ORA_DISK_1: restore complete, elapsed time: 00:10:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00017 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/undo_2.2146.1102524983
channel ORA_DISK_1: restore complete, elapsed time: 00:12:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00018 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/users.2147.1102525001
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00021 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/xxx_core_2005.2149.1102525009
channel ORA_DISK_1: restore complete, elapsed time: 00:11:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxxqb.2152.1102525017
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00023 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxx_idx.2169.1102525185
channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00024 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/t_xxx.2171.1102525221
channel ORA_DISK_1: restore complete, elapsed time: 02:15:17
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00029 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/ogg.2184.1102525807
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XXXCDB16
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00053 to +DG/XXXCDB/D824F08209B54437E0532310A8C08619/DATAFILE/xxxx_tbs.2358.1102529703
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2023:03:0616:22:05
RMAN>
-- 备库恢复使用enable recovery
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB2 MOUNTED
SQL> alter session set container=cdb$root;
Session altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3.2212E+10 bytes
Fixed Size 37471152 bytes
Variable Size 4026531840 bytes
Database Buffers 2.8119E+10 bytes
Redo Buffers 29634560 bytes
Database mounted.
SQL> alter session set container=PDB2;
Session altered.
SQL> alter pluggable database enable recovery;
Pluggable database altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> alter session set container=PDB1;
Session altered.
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ ONLY NO
SQL> conn / as sysdba
Connected.
SQL> alter session set container=PDB2;
Session altered.
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ ONLY NO
SQL> conn / as sysdba
Connected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ ONLY NO
4 PDB2 READ ONLY NO
SQL> alter session set container=PDB2;
Session altered.
-- 视数据库大小，如果归档日志较多，需要一段时间恢复
-- 然后一段时间后查询数据文件状态，可以看到PDB2下的数据文件状态都为 ONLINE
SQL> select name,status from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/system.12488.1130764925 SYSTEM
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/sysaux.14822.1130764951 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/undotbs1.2929.1130765115 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/undo_2.10835.1130765751 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/users.2202.1130766517 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/mic_core_2005.10290.1130766523 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/t_micqb.14906.1130767239 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/t_PDB1_idx.10384.1130767395 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/t_PDB1.7809.1130767601 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/ogg.7057.1130775717 ONLINE
+DG/PDB1DG/D824F08209B54437E0532310A8C08619/DATAFILE/xstream_tbs.13789.1130775725 ONLINE
11 rows selected.
此时实时同步主备库，使用rman 恢复还可以开启多个channel来加快恢复的速度。
注意，在使用rman 恢复备库PDB时，如果备库tnsnames.ora里配置的tns串带有（.) 符号会导致报错，报错信息如下：
RMAN> run {
2> restore pluggable database pdb2 from service xxxcdb.host16;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "dot": expecting one of: "allforeign, application, archivelog, asdecrypted, asencrypted, backupset, channel, check, controlfile, database, database root, datafile, device, dump, farsync, file_name_convert, force, foreign, from, frompreplugin, from service, high, pluggable, preview, primary, section, skip preplugin, skip readonly, spfile, standby, tablespace, to restore point, until restore point, until, using, validate, (, ;"
RMAN-01007: at line 2 column 58 file: standard input
此时可将tns连接串在tnsnames.ora里修改为xxxcdbhost这种不带(.)的才可以。
三、总结
本场景所描述的是一特例，搭建DG还是要初期为主备库规划好相应存储空间，另外对于备库PDB库的恢复，还依赖于备库保留主库的归档日志时间，通过时间太长，归档日志不存在，会导致产生gap，就无法使用文中的恢复方法了，而是要选择归档日志采用不完全恢复了。
最后修改时间：2023-03-08 11:33:15
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
