爱数恢复单个pdb指定时间点引发的一系列问题？

原创 jieguo 2025-08-26

443

用户需求：原库是oracle exadata x8m 19c rac，需从爱数备份一体机中恢复一个pdb库指定时间点，比如5月10日的，恢复到vm测试环境，用于查找当时某个表的记录。

处理过程：

同事处理问题一波三折：当故事听吧。

用爱数恢复pdb1库到新测试环境，一开始就失败了：

爱数工程师说是omf的问题导致，怎么可能？set newname就是解决更换新路径的问题，
后来找爱数二线进行操作，上面的问题没了，但遇到了新问题：
发现只restore恢复出了文件，recover失败了，无法open。
提示另外的pdb库没找到，爱数二线工程师最后回复说是bug，需等研发补丁处理，用户着急用怎么办？等修复估计黄花菜都凉了。

思考一下：
既然恢复出了pdb的所有数据文件，那么就可以手动插拔方式恢复启动数据库了。
前面写的文章，刚好就可以干这个的只有pdb租户库的备份，文件被rm和库被drop是否可以恢复呢？

OK，关键的命令送上：

生成xml元数据文件：
BEGIN
   DBMS_PDB.RECOVER (
      pdb_descr_file    =>   '/home/oracle/PDB1.xml',
      pdb_name          =>   'PDB1',
      filenames         =>   '/data/PDB1/'
   );
END;
/
创建出租户库：
CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/PDB1.xml' NOCOPY;

顺利创建出pdb1，至少数据有了，但还有小毛病：

1）开关pdb1后，restricted报YES，说明存在问题：

检查select * from PDB_PLUG_IN_VIOLATIONS;

ojvm补丁没打，另外是rac选件在单机需要关闭：exec dbms_registry.OPTION_OFF(‘RAC’);

2）临时表空间没有创建，会导致expdp失败，报错如下

解决之后顺利导出：

3）好不容易导出dmp了，再导入到另外的测试库中时又报了如下的时区错误：

ORA-39405: Oracle Data Pump Does Not Support Importing From A Source Database With TSTZ Version 42 into a target database with TSTZ version 32.
由于目标端有生产不能停机升级时区，所以只能采用exp的方式重新从测试库导出参考以前就遇到这种问题了

4）可是同事说测试机的监听起不来

其实导出用不用监听关系不大，测试环境先不管它，本地直接往外导出即可。

改用sys和system是可以本地导出的。注意修改sys和system的密码需要在根容器下，否则提示报错。

好不容易system的密码改好了，重新导出full=y，又提示exp-00058错误

解决办法简单：要么导出不用full改成指定用户owner，要么修复EXP-00058的错误。一般建议按用户导出就好，需要的测试数据无非就在指定的用户下，没必要导出全部对象full，避免后期导入可能遇到更多未知问题。

select * FROM DBA_PROFILES WHERE  RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';
alter PROFILE ORA_STIG_PROFILE LIMIT  PASSWORD_VERIFY_FUNCTION NULL;

可参考：https://blog.csdn.net/laoli815/article/details/134113937

其实如果监听解决了，直接在目标端exp就行了，不用本地exp后还得scp到目标端。监听无非关注listener.ora文件，肯定好解决。

5)导出又报错了？ORA-02002:0RA-01000:

处理办法：1.关闭审计2.增加游标，总之根据错误信息应对处理即可。
ALTER SYSTEM SET open_cursors=10000 SCOPE=BOTH sid=’’;
alter system set audit_trail=none scope=spfile sid=’’;–需重启库生效

*关于pdb的普通用户不能本地exp的问题，做了个测试验证，有兴趣可以参看：

[oracle@oracle ~]$ export ORACLE_SID=jyc
==注意修改ORACLE_PDB_SID指定pdb1
[oracle@oracle ~]$ export ORACLE_PDB_SID=pdb1
[oracle@oracle ~]$ sqlplus jyc/jyc

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:14:19 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
==看到提示密码不对，其实是对的

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:15:21 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 PDB1                           READ WRITE NO
SQL> select * from jyc.test;

        ID
----------
         1

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle ~]$ sqlplus jyc/jyc

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:15:43 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

==看到提示密码不对，其实是对的

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:15:49 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 PDB1                           READ WRITE NO
SQL> alter user jyc identified by jyc;

User altered.

SQL> grant dba to jyc;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle ~]$ sqlplus jyc/jyc

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:16:12 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C

==看到提示密码不对，其实是对的
==密码确定是jyc为什么就连不了呢？


[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:16:29 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter user system identified by jyc;
alter user system identified by jyc
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers


SQL> exit

==尝试修改system的密码来试一下，pdb内部不能修改


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
===需要先取消ORACLE_PDB_SID的设置，才能进入根容器
[oracle@oracle ~]$ export ORACLE_PDB_SID=

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:17:01 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB5                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB1                           READ WRITE NO
         8 PDB6                           READ WRITE NO
SQL> alter user system identified by jyc;

User altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle ~]$ export ORACLE_PDB_SID=pdb1
==测试system的连接正常
[oracle@oracle ~]$ sqlplus system/jyc

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 26 12:17:42 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from jyc.test;

        ID
----------
         1

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oracle ~]$ exp system/jyc file=test.dmp log=test.log buffer=65535000 owner=jyc

Export: Release 19.0.0.0.0 - Production on Tue Aug 26 12:18:41 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user JYC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user JYC 
About to export JYC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export JYC's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@oracle ~]$ 

==使用sys和system等公共用户无需监听可以本地导出。

最后修改时间：2025-08-29 15:19:16

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者