备库执行采集awr报告时，报错ORA-01110 ORA-01157

系统：CentOS 7.9 64位

数据库：Oracle 11.2.0.4 64位

环境：rac(双节点) + dg

问题描述：备库执行采集awr报告时，报错ORA-01110、ORA-01157，如下所示：

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_20391_20392.html

select output from table(dbms_workload_repository.awr_report_html( :dbid,

                         *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '/u01/app/oracle/oradata/orcldg/datafile/tempfile/temp.11700.17638465829'

ORA-06512: at "SYS.DBMS_AWR_REPORT_LAYOUT", line 2458

ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 1278

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 915

ORA-06512: at line 1

Report written to awrrpt_1_20391_20392.html

解决过程：

主库查询

> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

+DATA/orcl/tempfile/temp.294.136457298

+DATA/orcl/tempfile/temp.11700.17638465829

备库查询

> select name from v$tempfile

NAME

----------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcldg/datafile/tempfile/temp.294.136457298

/u01/app/oracle/oradata/orcldg/datafile/tempfile/temp.11700.17638465829

说明：发现备库并没有/u01/app/oracle/oradata/orcldg/datafile/tempfile/目录，那两个temp文件固然也没有.

备库端用spfile生成pfile文件，查看pfile文件.

*.log_file_name_convert='+DATA/orcl/onlinelog/','/data/oradata/orcldg/onlinelog/'

*.db_file_name_convert='+DATA/orcl/datafile/','/u01/app/oracle/oradata/orcldg/datafile/','+DATA/orcl/','/u01/app/oracle/oradata/orcldg/datafile/'

说明：发现备库pfile文件中convert参数并没有将temp文件的转换写入，判断该dg在搭建时没有考虑temp文件的路径转换.

按如下内容修改pfile文件，主要将temp文件的转换写入.

*.log_file_name_convert='+DATA/orcl/onlinelog/','/data/oradata/orcldg/onlinelog/','+DATA/orcl/tempfile/','/u01/app/oracle/oradata/orcldg/datafile/'

*.db_file_name_convert='+DATA/orcl/datafile/','/u01/app/oracle/oradata/orcldg/datafile/','+DATA/orcl/','/u01/app/oracle/oradata/orcldg/datafile/','+DATA/orcl/tempfile/','/u01/app/oracle/oradata/orcldg/datafile/'

修改pfile文件后，关闭备库.

然后将主库的temp.294.136457298、temp.11700.17638465829文件拷贝到备库/u01/app/oracle/oradata/orcldg/datafile/目录下.

主库拷贝文件

ASMCMD> cp temp.294.136457298 /home/grid

ASMCMD> cp temp.11700.17638465829 /home/grid

主库传递到备库

$ scp temp.294.136457298 oracle@192.168.133.120: /u01/app/oracle/oradata/orcldg/datafile/

$ scp temp.11700.17638465829 oracle@192.168.133.120: /u01/app/oracle/oradata/orcldg/datafile/

备库执行

> startup nomout pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/INITorcldg.ora’

> alter database mount;

> alter system set standby_file_management=manual;

> show parameter standby

NAME                                 TYPE                              VALUE

------------------------------------ --------------------------------- ------------------------------

standby_archive_dest                 string                            ?/dbs/arch

standby_file_management              string                            MANUAL

> alter database rename file '/u01/app/oracle/oradata/orcldg/datafile/tempfile/temp.294.136457298' to '/u01/app/oracle/oradata/orcldg/datafile/temp.294.136457298';

> alter database rename file '/u01/app/oracle/oradata/orcldg/datafile/tempfile/temp.11700.17638465829' to '/u01/app/oracle/oradata/orcldg/datafile/temp.11700.17638465829';

> alter system set standby_file_management=auto;

> alter database open;

> create spfile from pfile ’/u01/app/oracle/product/11.2.0/db_1/dbs/INITorcldg.ora’;

> shutdown immediate;

> startup;