standby database ope时，提示ORA-10458: ORA-01152: ORA-01110:

王亮

在配置完standby database，完成数据库同步时，将standby database切换到open状态时，提示下面的的错误

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10458: standby database requiresrecovery

ORA-01152: file 1 was not restored from asufficiently old backup

ORA-01110: data file 1:

'/u01/app/oracle/oradata/racdg/datafile/system.256.1034746429'

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

退出sqlplus，找到数据库的aler告警日志，查询详细的报错信息，如下：

Thu Mar 12 08:48:32 2020

ARC3 started with pid=26, OS id=36461

ARC1: Archival started

ARC2: Archival started

ARC2: Becoming the 'no FAL' ARCH

ARC1: Becoming the heartbeat ARCH

ARC1: Becoming the active heartbeat ARCH

WARNING: No local destinations have been defined for archival of the Standby Redo Log.

Completed: alter database mount standby database

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_35271.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/racdg/onlinelog/group_1.263.1034746481'

ORA-27037: unable to obtain file status

提示没有在本地为归档设置目录。

查看standby database的pfile，发现log_archive_dest2与fal_server和standby database的db_unique_name都未能设置或设置错误

[oracle@racdg dbs]$ cat initorcl.ora

orcl.__db_cache_size=838860800

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=402653184

orcl.__sga_target=1191182336

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=285212672

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/racdg/controlfile/current.261.1034746479','/u01/app/oracle/oradata/racdg/controlfile/current.260.1034746479'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata/racdg/datafile/'

*.db_domain=''

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

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='orcldg'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'

*.fal_server='ORCLDG'

*.log_archive_config='dg_config=(orcldg,orcl)'

*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='+DATA/orcl/onlinelog','/u01/app/oracle/oradata/racdg/onlinelog'

*.open_cursors=300

*.pga_aggregate_target=395313152

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1185939456

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

查看主库的log_archive_dest2的值也是为空，这也是我犯的错误了。

SQL> showparameter log_archive_dest_2

NAME                                 TYPE        VALUE

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

log_archive_dest_2                   string

综合上面的检查结果，则可以进行相应的调整了。

首先在主库的sqlplus里，重新设置一下log_archive_dest2的值

SQL> alter system set log_archive_dest_2='service=racdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' sid='*';

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=racdg lgwr async valid
                                                 _for=(online_logfiles,primary_
                                                 role) db_unique_name=orcldg



完成之后，在standby database的pfile文件，添加上log_archive_dest2的配置，与fal_server和db_unique_name的值了。修改后，内容如下：
[oracle@racdg dbs]$ cat initorcl.ora
orcl.__db_cache_size=838860800
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=402653184
orcl.__sga_target=1191182336
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=285212672
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/racdg/controlfile/current.261.1034746479','/u01/app/oracle/oradata/racdg/controlfile/current.260.1034746479'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata/racdg/datafile/'
*.db_domain=''
*.db_file_name_convert='+DATA/orcl/datafile','/u01/app/oracle/oradata/racdg/datafile','+DATA/orcl/tempfile','/u01/app/oracle/oradata/racdg/tempfile'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcldg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldgXDB)'
*.fal_server='RACDB'
*.log_archive_config='dg_config=(orcldg,orcl)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=racdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/orcl/onlinelog','/u01/app/oracle/oradata/racdg/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=395313152
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1185939456
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
fal_server值设定为tns中指向主库的连接名
修改完成之后，关闭数据库，以修改后的pfile创建spfile，然后将数据库启动到mount状态下，并验证修改后的参数文件，是否已经加载进去

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2252664 bytes
Variable Size             402653320 bytes
Database Buffers          771751936 bytes
Redo Buffers                9195520 bytes
Database mounted.
SQL> show parameter db_uni

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcldg

SQL> show parameter log_archive_dest_2


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=racdb lgwr async valid
                                                 _for=(online_logfiles,primary_
                                                 role) db_unique_name=orcl

这里可以看到，已经修改完成，并配置完成，然后启动备库，并将备库切换到open状态

SQL> alter database recover managedstandby database disconnect from session;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-10456: cannot open standby database;media recovery session may be in progress

出现这个情况的原因是未能通过TNS标符连接到主库。检查主库与备库之间的监听是否启动，同时检查TNS配置文件中的标识名，是否与pfile中指定的是一致。结果检查都无问题。两台服务器之间，ping也能是网的。然后想到两台服务器的防火墙，因为重启过服务器，可能自己开启了，而我又未添加防火墙策略，导致无法连接。在关闭防火墙之后，使用tnsping命令，检查发现已经能通。

再执行上面的命令时，发现错误发生了变化

SQL> alter database recover managedstandby database disconnect from session;

alter database recover managed standbydatabase disconnect from session

*

ERROR at line 1:

ORA-01153: an incompatible media recoveryis active

报这个错误的原因是因为之前执行过那条命令，已经启用了恢复管理，再执行该命令时，再启动，就会报错。那这里就先停止恢复管理，打开数据库后，再连接恢复管理器步骤如下：

SQL> alter database recover managedstandby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

SQL> alter database recover managedstandby database using current logfile disconnect ;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

至此，完成了standby database的配置并启用。后面则是通过生成归档日志和创建表空间来测试数据库能否完成同步。