技术人当遇到具体问题，能给出的各种解决方案，有一种类型叫做workaround，翻译过来通常为“应变方法”、“变通方法”； 其实这种方式通常是没有找到根本的解决方案，但是为了快速恢复业务而采用的一种巧妙规避/跳过的方式。
举个具体的例子：我有测试需求要在主库创建一个新的PDB：
1.创建新的PDB
创建一个专门存放AWRDUMP的PDB
代码语言：javascript复制CREATE PLUGGABLE DATABASE awr
ADMIN USER awr IDENTIFIED BY awr
ROLES = (dba)
DEFAULT TABLESPACE tbs_awr
DATAFILE '/flash/oradata/DEMO/awr/awr01.dbf' SIZE 250M AUTOEXTEND ON maxsize 10G
FILE_NAME_CONVERT = ('/flash/oradata/DEMO/pdbseed/',
'/flash/oradata/DEMO/awr/')
STORAGE (MAXSIZE 2G)
PATH_PREFIX = '/flash/oradata/DEMO/awr/';主库创建成功没问题。
2.发现DG备库出现同步问题
发现备库没有同步，排查alert日志发现因为这个awr的目录不会自动创建，导致报错，最终引发MRP进程终止，详细告警日志如下：
代码语言：javascript复制2023-03-15T18:03:53.650192 08:00
Recovery of Online Redo Log: Thread 1 Group 11 Seq 446 Reading mem 0
Mem# 0: DATADG/DEMORAC/ONLINELOG/group_11.276.1127389115
PR00 (PID:11733): Media Recovery Waiting for T-1.S-447 (in transit)
2023-03-15T18:03:55.023213 08:00
Recovery of Online Redo Log: Thread 1 Group 13 Seq 447 Reading mem 0
Mem# 0: DATADG/DEMORAC/ONLINELOG/group_13.278.1127389119
2023-03-15T18:38:28.999341 08:00
Recovery created pluggable database AWR
Automatic Copy of Standby datafiles for create pdb failed with error - 65169. Files need to be copied manually
2023-03-15T18:38:29.121847 08:00
Errors in file /u01/app/oracle/diag/rdbms/demorac/jydb1/trace/jydb1_pr00_11733.trc:
ORA-65169: error encountered while attempting to copy file DATADG/DEMORAC/pdbseed/system01.dbf
ORA-19504: failed to create file " DATADG/DEMORAC/awr/system01.dbf"
ORA-17502: ksfdcre:4 Failed to create file DATADG/DEMORAC/awr/system01.dbf
ORA-15173: entry 'awr' does not exist in directory 'DEMORAC'
PR00 (PID:11733): MRP0: Background Media Recovery terminated with error 1274
2023-03-15T18:38:29.188919 08:00
Errors in file /u01/app/oracle/diag/rdbms/demorac/jydb1/trace/jydb1_pr00_11733.trc:
ORA-01274: cannot add data file that was originally created as '/flash/oradata/DEMO/awr/system01.dbf'
ORA-01565: error in identifying file ' DATADG/DEMORAC/awr/system01.dbf'
ORA-17503: ksfdopn:2 Failed to open file DATADG/DEMORAC/awr/system01.dbf
ORA-15173: entry 'awr' does not exist in directory 'DEMORAC'
2023-03-15T18:38:29.195972 08:00
.... (PID:5407): Managed Standby Recovery not using Real Time Apply
2023-03-15T18:38:29.340321 08:00
Recovery interrupted!
IM on ADG: Start of Empty Journal
IM on ADG: End of Empty Journal
Recovered data files to a consistent state at change 23401386
2023-03-15T18:38:29.903857 08:00
Increasing priority of 2 RS
Reconfiguration started (old inc 6, new inc 8)
List of instances (total 2) :
1 2
My inst 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
2023-03-15T18:38:29.983551 08:00
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2023-03-15T18:38:29.986436 08:00
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
Set master node info
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Reconfiguration complete (total time 0.3 secs)
Decreasing priority of 2 RS
2023-03-15T18:38:30.492490 08:00
Stopping change tracking
2023-03-15T18:38:30.501521 08:00
Errors in file /u01/app/oracle/diag/rdbms/demorac/jydb1/trace/jydb1_pr00_11733.trc:
ORA-01274: cannot add data file that was originally created as '/flash/oradata/DEMO/awr/system01.dbf'
ORA-01565: error in identifying file ' DATADG/DEMORAC/awr/system01.dbf'
ORA-17503: ksfdopn:2 Failed to open file DATADG/DEMORAC/awr/system01.dbf
ORA-15173: entry 'awr' does not exist in directory 'DEMORAC'
2023-03-15T18:38:30.524601 08:00
Background Media Recovery process shutdown (jydb1)3.如何解决？
如果是找根本解决方案，那就是要研究为何无法自动创建成功，是否能够自动创建成功，具体的改进方案？ 如果一时找不到方案，急于恢复同步，那其实手工创建这个目录，然后重新开启同步，就能快速解决，但这种解决方式就叫做workaround：
代码语言：javascript复制ASMCMD> pwd
DATADG/DEMORAC
ASMCMD> mkdir AWR
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> set lines 180
SQL> col name for a22
SQL> col value for a22
SQL> select * from v$dataguard_stats;
SOURCE_DBID SOURCE_DB_ NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID
----------- ---------- ---------------------- ---------------------- ------------------------------ ------------------------------ ------------------------------ ----------
0 transport lag 00 00:00:00 day(2) to second(0) interval 03/15/2023 20:23:51 03/15/2023 20:23:50 0
0 apply lag 00 00:00:00 day(2) to second(0) interval 03/15/2023 20:23:51 03/15/2023 20:23:50 0
0 apply finish time 00 00:00:00.000 day(2) to second(3) interval 03/15/2023 20:23:51 0
0 estimated startup time 18 second 03/15/2023 20:23:51 0
SQL> !ps -ef|grep mrp
oracle 27472 1 0 20:22 ? 00:00:00 ora_mrp0_jydb1
oracle 27677 27436 0 20:24 pts/2 00:00:00 /bin/bash -c ps -ef|grep mrp
oracle 27679 27677 0 20:24 pts/2 00:00:00 grep mrp从alert日志也可以看到，快速解决了问题：
代码语言：javascript复制2023-03-15T20:22:38.685163 08:00
ALTER DATABASE RECOVER managed standby database disconnect
2023-03-15T20:22:38.720205 08:00
Attempt to start background Managed Standby Recovery process (jydb1)
Starting background process MRP0
2023-03-15T20:22:38.745104 08:00
MRP0 started with pid=76, OS id=27472
2023-03-15T20:22:38.747543 08:00
Background Managed Standby Recovery process started (jydb1)
2023-03-15T20:22:43.778604 08:00
Starting single instance redo apply (SIRA)
Started logmerger process
2023-03-15T20:22:43.859358 08:00
IM on ADG: Start of Empty Journal
IM on ADG: End of Empty Journal
2023-03-15T20:22:43.877044 08:00
.... (PID:5407): Managed Standby Recovery starting Real Time Apply
2023-03-15T20:22:43.940962 08:00
max_pdb is 5
2023-03-15T20:22:44.268320 08:00
Increasing priority of 2 RS
Reconfiguration started (old inc 8, new inc 10)
List of instances (total 2) :
1 2
My inst 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
2023-03-15T20:22:44.337010 08:00
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2023-03-15T20:22:44.337017 08:00
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
Set master node info
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Reconfiguration complete (total time 0.2 secs)
Decreasing priority of 2 RS
2023-03-15T20:22:45.062909 08:00
Parallel Media Recovery started with 4 slaves
2023-03-15T20:22:45.260072 08:00
Stopping change tracking
PR00 (PID:27481): Media Recovery Waiting for T-1.S-447 (in transit)
2023-03-15T20:22:45.456197 08:00
Recovery of Online Redo Log: Thread 1 Group 13 Seq 447 Reading mem 0
Mem# 0: DATADG/DEMORAC/ONLINELOG/group_13.278.1127389119
2023-03-15T20:22:45.548608 08:00
ALTER SYSTEM SET remote_listener='db01rac-scan:1521' SCOPE=MEMORY SID='jydb1';
2023-03-15T20:22:45.550060 08:00
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='jydb1';
2023-03-15T20:22:45.765532 08:00
Completed: ALTER DATABASE RECOVER managed standby database disconnect
2023-03-15T20:22:49.050096 08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file DATADG/DEMORAC/awr/system01.dbf from DATADG/DEMORAC/pdbseed/system01.dbf
AWR(5):Recovery created file DATADG/DEMORAC/awr/system01.dbf
AWR(5):Successfully added datafile 21 to media recovery
AWR(5):Datafile #21: ' DATADG/DEMORAC/awr/system01.dbf'
2023-03-15T20:22:55.588650 08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file DATADG/DEMORAC/awr/sysaux01.dbf from DATADG/DEMORAC/pdbseed/sysaux01.dbf
AWR(5):Recovery created file DATADG/DEMORAC/awr/sysaux01.dbf
AWR(5):Successfully added datafile 22 to media recovery
AWR(5):Datafile #22: ' DATADG/DEMORAC/awr/sysaux01.dbf'
2023-03-15T20:22:58.602144 08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file DATADG/DEMORAC/awr/undotbs01.dbf from DATADG/DEMORAC/pdbseed/undotbs01.dbf
AWR(5):Recovery created file DATADG/DEMORAC/awr/undotbs01.dbf
AWR(5):Successfully added datafile 23 to media recovery
AWR(5):Datafile #23: ' DATADG/DEMORAC/awr/undotbs01.dbf'
2023-03-15T20:23:00.926349 08:00
AWR(5):Recovery created file DATADG/DEMORAC/awr/awr01.dbf
AWR(5):Successfully added datafile 24 to media recovery
AWR(5):Datafile #24: ' DATADG/DEMORAC/awr/awr01.dbf'最近高层给全员开会时提到要向前走一步，如果是落到技术岗位上，那就是要不满足workaround的解决方式，去找根本的解决方案。 实际情况当然要灵活变通，比如当生产环境需要快速恢复业务/功能时，可以使用各种workaround去应对确保快速恢复，但事后排查根本原因时还是要找到根本的解决方案。 所以，文中举的这个问题根本解决方案是？先留给大家一起思考吧。
