Oracle Data Guard 11.2.0.1一主一备的基础上，再新增一个高版本11.2.0.4的备库

来源：互联网发布：mac怎么加密文件夹编辑：程序博客网时间：2024/06/05 08:44

数据库升级需要将线上11.2.0.1升级到11.2.0.4，而且尽量做到少步骤迁移能实时同步数据，所以想到一个方案，已经有了oracle 11.2.0.1的一主一备库的数据库了，现在需要再搭建一个备库s2，而且备库需要11.2.0.4，做成一主两备的架构，在s2上升级版本，大概步骤过程如下

（1），先搭建S2，版本为11.2.0.4

（2），从M1实时同步数据到S2，S2启动到mount状态

（3），在业务低峰期间，比如凌晨2点，停止应用，断掉业务往数据库里面写

（4），在S2上做failover操作，将S2从standby切换成主库，S2变成M2

（5），在新的M2上做upgrade升级操作

（6），将应用连接到新的主库M2上

（7），将S1重做成S2，连接M2上。

1、修改监听配置文件

1.1在备库2上配置listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.71)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = powerdes)

(ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/dbhome_1)

)

ADR_BASE_LISTENER = /oracle/app/oracle

1.2 在备库2上设置tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

POWERDES =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = powerdes)

)

PD1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.62)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = powerdes)

)

PD2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.63)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = powerdes)

)

PD3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.71)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = powerdes)

)

ADR_BASE_LISTENER = /oracle/app/oracle

1.3在主库、备库1、添加配置PD3的tns配置

vim tnsnames.ora

PD3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.71)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = powerdes)

)

2，设置密码

在主库传输备库密码到备库2

[oracle@hch_test_dbm1_121_62 dbs]$ scp orapwpowerdes 192.168.121.71:/oracle/app/oracle/product/11.2.0.4/dbhome_1/dbs/

oracle@192.168.121.71's password:

orapwpowerdes 100% 2560 2.5KB/s 00:00

[oracle@hch_test_dbm1_121_62 dbs]$

3，配置参数

3.1 在主库执行

搭建dg的主要目的是为了防止主库宕机，备库能够切换为主库，但是当备库切换为主库后，那么客户端tns也要保证和原来的主库一样，一般通俗来讲，需要改变tns配置，如果客户端比多比如应用程序比较多，那么需要改变的更多，特别是有的应用已经嵌入连接方式，一改就要重启应用，那么这样改起来就非常麻烦了。那么针对dns里面2个核心要点：

（1）是IP地址，

（2）是oracle_sid；我们可以采用如下的方案，

（2.a）切换后，修改新主库即是原来的备库的ip地址为主库ip地址

（2.b）保证备库主库的oracle_sid一致也就是service_name一致。

# 主库的db_unique_name 为powerdes_m1，备库1的db_unique_name为powerdes_s1 ，备库2的db_unique_name设置成powerdes_s2.

#（1）设置参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(powerdes_m1,powerdes_s1,powerdes_s2)' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=PD3 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=powerdes_s2' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3='ENABLE';

#（2） fal_server 指定为主库即primary的网络服务名,fal_client 指定为备库即standby的网络服务名,通常来说，主库和备库是反过来的，便于主备库的切换。

SQL> ALTER SYSTEM SET FAL_SERVER='PD2,PD3' SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_CLIENT='PD1' SCOPE=BOTH;

#（3）写入启动参数文件

SQL> create pfile from spfile;

3.2 在备库2上执行操作，修改备库启动参数

（1）创建临时文件

create pfile='/oracle/p1.ora' from spfile;

（2）添加配置

vim /oracle/p1.ora

#DG CONFIG

*.log_archive_config='dg_config=(powerdes_m1,powerdes_s2)'

*.log_archive_dest_1='LOCATION=/oracle/app/oracle/flash_recovery_area/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=powerdes_s2'

*.log_archive_dest_3='SERVICE=PD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=powerdes_m1'

*.standby_file_management='AUTO'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_3=enable

*.fal_server=PD1

*.fal_client=PD3

*.db_unique_name=powerdes_s2

*.standby_file_management=auto

（3）然后加入到启动里面

SQL> create spfile from pfile='/oracle/p1.ora';

File created.

SQL> create pfile from pfile;

create pfile from pfile

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> create pfile from spfile;

File created.

SQL>

4，开始搭建

4.1 在备库2上启动到no mount状态

启动命令：startup nomount

SQL> startup nomount

ORACLE instance started.

Total System Global Area 2.0176E+10 bytes

Fixed Size 2261928 bytes

Variable Size 3422555224 bytes

Database Buffers 1.6710E+10 bytes

Redo Buffers 41463808 bytes

SQL>

4.2 在主库上执行

同步命令：rlwrap rman target sys/sys0418@PD1 auxiliary sys/sys0418@PD3

rman上执行

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 27-MAR-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1776 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes' auxiliary format

'/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes' ;

}

executing Memory Script

......#这里有很多步骤，需要一步步同步数据文件，花费时间也比较长，过程略过，在这一步的同时，可以去看后台alert的log日志，随时观察进展。

datafile 16 switched to datafile copy

input datafile copy RECID=23 STAMP=939752404 file name=/home/oradata/powerdes/dw02.DBF

datafile 17 switched to datafile copy

input datafile copy RECID=24 STAMP=939752404 file name=/home/oradata/powerdes/timdba01.DBF

Finished Duplicate Db at 27-MAR-17

RMAN>

4.3 将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE

# alter system set log_archive_dest_state_3='enable';

SQL> alter system set log_archive_dest_state_3='enable';

System altered.

SQL>

4.4 在备库2上添加standby文件（主库上已经有了standby文件就不需要再添加咯）

alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 300M;

alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 300M;

alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 300M;

5，开启备库应用

启动standby的redo应用的两种方式：

（1）默认的物理DG启动应用后，在主库arch日志被完整写入后才会开始应用该arch log

SQL> alter database recover managedstandby database disconnect from session;

（2）可以添加current logfile参数，使得应用当前正在读写，还没有完成归档的日志

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

（3）开启多个并行度提高应用效率

SQL> alter database recover managedstandby database parallel 8 using current logfile disconnect from session;

（4）关闭REDO应用

SQL> alter database recover managedstandby database using current logfile disconnect from session nodelay;

（5）取消延时应用

SQL> alter database recover managedstandby database cancel;

实际操作：去备库操作

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,applied from v$archived_log order by sequence# asc;

SEQUENCE# APPLIED

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

138 YES

SQL>

6，打开备库

# 在mount状态应用

SQL> alter database recovermanagedstandby database using current logfile disconnect from session;

Database altered.

# 取消延时应用

SQL> alter database recovermanagedstandby database cancel;

Database altered.

# 打开库

SQL> alter database open;

Database altered.

# 再次应用redo，添加currentlogfile参数，使得应用当前正在读写，还没有完成归档的日志

SQL> alter database recovermanagedstandby database using current logfile disconnect from session;

Database altered.

SQL>

7，备库failover

因为备库是高版本11.2.0.4，主库是低版本11.2.0.1，所以不能通过swithover的方式来操作切换备库s2成为主库。

如果返回的有记录，按照列出的记录号复制对应的归档文件到待转换的standby 服务器。这一步非常重，要，必须确保所有已生成的归档文件均已存在于standby 服务器，不然可能会数据不一致造成转换时报错。

查询待转换standby 数据库的V$ARCHIVE_GAP 视图，确认归档文件是否连接，如果没有记录，就表明可以进行切换，否则需要等待现有记录转换完成再切换。

（1）文件复制之后，通过下列命令将其加入数据字典：

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

SQL>

（2）检查归档文件是否完整，分别在primary/standby 执行下列语句：

SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

THREAD# A

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

1 139

SQL>

（3）启动failover 在standby也就是S2上执行

执行下列语句：alter database recover managed standby database finish force;

SQL> alter database recover managed standby database finish force;

Database altered.

SQL>

FORCE 关键字将会停止当前活动的RFS 进程，以便立刻执行failover。剩下的步骤就与一般的switchover 很相似了

（4）切换物理standby 角色为primary

SQL> alter database commit to switchover to primary;

Database altered.

SQL>

（5）启动新的primary 数据库。

如果当前数据库已mount,直接open 即可，如果处于read-only 模式，需要首先shutdown immediate，然后再直接startup。

先查看db的模式，命令为：select open_mode,database_role from v$database;

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

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

MOUNTED PRIMARY

SQL>

8，准备升级

为mount，所以需要open，但是open之前需要升级操作，因为dg过来的是11.2.0.1的版本的数据。

升级步骤：

（1）特殊方式启动

SQL> shutdown immediate;

SQL> startup upgrade;

（2）查看预升级信息

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

（3）执行升级脚本

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql 1小时左右

startup

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql 8分钟左右

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql 2分钟左右

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=true SCOPE=SPFILE;

（4）重启数据库

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

问题1------

SQL> startup;

ORA-00439: feature not enabled: RealApplication Clusters

SQL> startup upgrade;

ORA-00439: feature not enabled: RealApplication Clusters

SQL>

SQL> create pfile='/oracle/p2.ora' fromspfile;

File created.

SQL> exit

Disconnected

[oracle@hch_test_dbm2_121_71 archivelog]$vim /oracle/p2.ora 注视掉/oracle/p2.ora里面的

[oracle@hch_test_dbm2_121_71 archivelog]$

--1---------------------------------------

问题报错统计

查询了一下METALINK，发现这个问题从9i到11g，任何一个版本都可能会出现。造成这个问题的原因是，实例虽然启动，但是没有注册到监听。实例是通过PMON进程注册到监听上的，而PMON进程需要在MOUNT状态下才会启动。因此造成了上面的错误。

[oracle@hch_test_dbm1_121_63 admin]$ rlwraprman target sys/sys0418@PD1 auxiliary sys/sys0418@PD2

Recovery Manager: Release 11.2.0.1.0 -Production on Mon Mar 27 16:29:23 2017

connected to target database: POWERDES(DBID=3391761643)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-00554: initialization of internalrecovery manager package failed

RMAN-04006: error from auxiliary database:ORA-12528: TNS:listener: all appropriate instances are blocking new connections

[oracle@hch_test_dbm1_121_63 admin]$

[oracle@hch_test_dbm1_121_63 admin]$tnsping PD1

TNS Ping Utility for Linux: Version11.2.0.1.0 - Production on 27-MAR-2017 16:29:33

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.62)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = powerdes)))

OK (0 msec)

[oracle@hch_test_dbm1_121_63 admin]$ vimlistener.ora

[oracle@hch_test_dbm1_121_63 admin]$

Oracle给出了两种解决方案，一种方法是对AUXILIARY数据库直接使用/ ，对TARGET数据库通过网络访问。===我上面使用的PD2 (推荐)

问题解决，在standby库执行

[oracle@hch_test_dbm1_121_63 admin]$ rlwraprman target sys/sys0418@PD1 auxiliary /

Recovery Manager: Release 11.2.0.1.0 -Production on Mon Mar 27 16:49:30 2017

connected to target database: POWERDES(DBID=3391761643)

connected to auxiliary database: POWERDES(not mounted)

RMAN>

--2---------------------------------

RMAN> duplicate target database for standby from active databasenofilenamecheck;

Starting Duplicate Db at 27-MAR-17

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of Duplicate Db commandat 03/27/2017 17:26:19

RMAN-06217: not connected to auxiliarydatabase with a net service name

RMAN>

去主库执行

---3----------------------------

Fri Mar 31 09:58:35 2017

Errors in file/oracle/app/oracle/diag/rdbms/powerdes_m1/powerdes/trace/powerdes_arc2_30172.trc:

ORA-16057: server not in Data Guardconfiguration

PING[ARC2]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.

Fri Mar 31 09:59:36 2017

Errors in file/oracle/app/oracle/diag/rdbms/powerdes_m1/powerdes/trace/powerdes_arc2_30172.trc:

ORA-16057: server not in Data Guardconfiguration

PING[ARC2]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.

Fri Mar 31 10:00:36 2017

在主库上查看db_unique_name：

SQL> show parameter db_unique_name;

NAME TYPE VALUE

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

db_unique_name string powerdes_m1

SQL>

Ok，重新设置LOG_ARCHIVE_CONFIG，设置前面的为powerdes，

ALTER SYSTEM SETLOG_ARCHIVE_CONFIG='DG_CONFIG=(powerdes_m1,powerdes_s1)';

---4----------------------------

*** 2017-03-31 10:19:49.225

Redo shipping client performing standbylogin

*** 2017-03-31 10:19:49.257 4539 krsu.c

Logged on to standby successfully

Client logon and security negotiationsuccessful!

This database DGID not in Data Guardconfiguration at 'PD2'

Error 16057 attaching to destinationLOG_ARCHIVE_DEST_2 standby host 'PD2'

ORA-16057: server not in Data Guardconfiguration

*** 2017-03-31 10:19:49.260 2747 krsi.c

krsi_dst_fail: dest:2 err:16057 force:0blast:1

kcrrwkx: unknown error:16057

ORA-16055: FAL request rejected

---5---------------------------

FAL[client]: All defined FAL servers havebeen attempted.

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

Check that theCONTROL_FILE_RECORD_KEEP_TIME initialization

parameter is defined to a value that issufficiently large

enough to maintain adequate log switchinformation to resolve

archivelog gaps.

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

---6-------

报错状况：

Fri Mar 31 11:07:38 2017

Errors in file/oracle/app/oracle/diag/rdbms/powerdes_m1/powerdes/trace/powerdes_arc1_23696.trc:

ORA-16057: server not in Data Guardconfiguration

PING[ARC1]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.

Fri Mar 31 11:08:38 2017

Errors in file/oracle/app/oracle/diag/rdbms/powerdes_m1/powerdes/trace/powerdes_arc1_23696.trc:

ORA-16057: server not in Data Guardconfiguration

PING[ARC1]: Heartbeat failed to connect tostandby 'PD2'. Error is 16057.

这些告警在ADG的环境中已经多次遇到，请注意类似报错的错误ID，这里的ID是16057，Oracle对该错误是这样解释的

ORA-16057: DGID from server not in DataGuard configuration

Cause: The Data Guard name of the primarydatabase or the FAL server is not in the Data Guard configuration of thestandby.

Action: In order for the primary databaseor the FAL server to archive logs to the standby database, the Data Guard nameof the primary or FAL server must be in the Data Guard configuration of thestandby.

积极排查问题

主库通道备状况

SQL>show parameter log_archive_dest_state_2;

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

log_archive_dest_state_29 string enable

SQL>

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

log_archive_dest_state_20 string enable

log_archive_dest_state_21 string enable

log_archive_dest_state_22 string enable

log_archive_dest_state_23 string enable

log_archive_dest_state_24 string enable

log_archive_dest_state_25 string enable

log_archive_dest_state_26 string enable

log_archive_dest_state_27 string enable

log_archive_dest_state_28 string enable

log_archive_dest_state_29 string enable

SQL>

ALTERSYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

ALTERSYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

[oracle@hch_test_dbm1_121_62dbs]$ scp orapwpowerdes192.168.121.63:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwpowerdes.m1

oracle@192.168.121.63'spassword:

orapwpowerdes 100% 2560 2.5KB/s 00:00

[oracle@hch_test_dbm1_121_62dbs]$

[oracle@hch_test_dbm1_121_63dbs]$ diff orapwpowerdes orapwpowerdes.m1

[oracle@hch_test_dbm1_121_63dbs]$

ALTERSYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH;

ALTERSYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;

主库配置状况

SQL>show parameter log_archive_config;

NAME TYPE VALUE

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

log_archive_config string DG_CONFIG=(powerdes_m1,powerde

s_s1)

SQL>

备库配置状况

SQL>show parameter log_archive_config;

NAME TYPE VALUE

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

log_archive_config string dg_config=(powerdes,powerdes_s

SQL>

看到这里有差异，所以需要重新设置下

ALTERSYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(powerdes_m1,powerdes_s1)'SCOPE=BOTH;

再次应用日志

SQL> alter database recover managed standbydatabase using current logfile disconnect from session;

Databasealtered.

SQL>

OK，主库成功连接到备库，已经开始传输归档日志了，后台alert如下所示：

ArchivedLog entry 62 added for thread 1 sequence 134 ID 0xd12c9f55 dest 1:

Primarydatabase is in MAXIMUM PERFORMANCE mode

RFS[68]:Selected log 4 for thread 1 sequence 135 dbid -903205653 branch 939330809

FriMar 31 13:10:31 2017

RFS[69]:Assigned to RFS process 17588

RFS[69]:Identified database type as 'physical standby': Client is ARCH pid 23696

FriMar 31 13:11:25 2017

alter database recover managed standbydatabase using current logfile disconnect from session

Attemptto start background Managed Standby Recovery process (powerdes)

FriMar 31 13:11:25 2017

MRP0started with pid=31, OS id=17590

MRP0:Background Managed Standby Recovery process started (powerdes)

started logmerger process

FriMar 31 13:11:30 2017

ManagedStandby Recovery starting Real Time Apply

FriMar 31 13:11:31 2017

RFS[70]:Assigned to RFS process 17634

RFS[70]:Identified database type as 'physical standby': Client is ARCH pid 23696

ParallelMedia Recovery started with 32 slaves

Waitingfor all non-current ORLs to be archived...

Allnon-current ORLs have been archived.

Completed: alter database recover managed standbydatabase using current logfile disconnect from session

MediaRecovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_73_939330809.dbf

MediaRecovery Log /oracle/app/oracle/flash_recovery_area/archivelog1_74_939330809.dbf

MediaRecovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_75_939330809.dbf

MediaRecovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_76_939330809.dbf

MediaRecovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_77_939330809.dbf

MediaRecovery Log/oracle/app/oracle/flash_recovery_area/archivelog1_78_939330809.dbf

移动redo文件路径

SQL>select member from v$logfile;

MEMBER

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

/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log

/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log

/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log

/home/oradata/powerdes/redo_dg_021.log

/home/oradata/powerdes/redo_dg_022.log

MEMBER

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

/home/oradata/powerdes/redo_dg_023.log

6rows selected.

SQL>

SQL>shutdown immediate;

ORA-01109:database not open

Databasedismounted.

ORACLEinstance shut down.

SQL>

cp文件地址：

[oracle@hch_test_dbm1_121_63~]$ mv/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log/home/oradata/powerdes/redo03.log

[oracle@hch_test_dbm1_121_63~]$ mv /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log/home/oradata/powerdes/redo02.log

[oracle@hch_test_dbm1_121_63~]$ mv/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log/home/oradata/powerdes/redo01.log

cp /home/oradata/powerdes/redo03.log /oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log

cp /home/oradata/powerdes/redo02.log/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log

cp /home/oradata/powerdes/redo01.log/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfkstobl_.log

数据库启动mount

alterdatabase rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log'to '/home/oradata/powerdes/redo03.log';

alterdatabase rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log'to '/home/oradata/powerdes/redo02.log';

alterdatabase rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log'to '/home/oradata/powerdes/redo01.log';

执行报错

SQL>alter database rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log'to '/home/oradata/powerdes/redo03.log';

alterdatabase rename file '/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log'to '/home/oradata/powerdes/redo03.log'

ERRORat line 1:

ORA-01511:error in renaming log/data files

ORA-01275:Operation RENAME is not allowed if standby file management is

automatic.

命令执行报错，提示说standbyfile maangement参数为自动，自动情况下不允许修改，好吧，听它的，修改成手动的，这样我们就可以移动它的目录地址了

SQL>show parameter standby;

NAME TYPE VALUE

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

standby_archive_dest string ?/dbs/arch

standby_file_management string AUTO

SQL>alter system set standby_file_management = MANUAL;

Systemaltered.

SQL>

SQL>alter database rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_3_dfkstobl_.log'to '/home/oradata/powerdes/redo03.log';

Databasealtered.

SQL>

执行第一个成功，但是执行第二个报错，记录如下：

SQL>alter database rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log'to '/home/oradata/powerdes/redo02.log';

alterdatabase rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.log'to '/home/oradata/powerdes/redo02.log'

ERRORat line 1:

ORA-01511:error in renaming log/data files

ORA-01516:nonexistent log file, data file, or temporary file

"/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfkstobl_.

log"

SQL>

看提示，这个文件nonexiststent log file，看是文件不存在，check下，修改成正确的文件名，再次执行。

再次执行

SQL>alter database rename file'/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_2_dfksto61_.log'to '/home/oradata/powerdes/redo02.log';

Databasealtered.

SQL>alter database rename file '/oracle/app/oracle/flash_recovery_area/POWERDES_S1/onlinelog/o1_mf_1_dfksto1q_.log'to '/home/oradata/powerdes/redo01.log';

Databasealtered.

SQL>

查看当前路径：

SQL>show parameter db_file_name_convert;

NAME TYPE VALUE

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

db_file_name_convert string

SQL>

SQL> select member from v$logfile;

MEMBER

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

/home/oradata/powerdes/redo03.log

/home/oradata/powerdes/redo02.log

/home/oradata/powerdes/redo01.log

/home/oradata/powerdes/redo_dg_021.log

/home/oradata/powerdes/redo_dg_022.log

/home/oradata/powerdes/redo_dg_023.log

6rows selected.

SQL>select name from v$datafile;

NAME

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

/home/oradata/powerdes/system01.dbf

/home/oradata/powerdes/sysaux01.dbf

/home/oradata/powerdes/undotbs01.dbf

/home/oradata/powerdes/users01.dbf

/home/oradata/powerdes/powerdesk01.dbf

/home/oradata/powerdes/plas01.dbf

/home/oradata/powerdes/pl01.dbf

/home/oradata/powerdes/help01.dbf

/home/oradata/powerdes/adobelc01.dbf

/home/oradata/powerdes/sms01.dbf

/home/oradata/powerdes/plcrm01.dbf

NAME

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

/home/oradata/powerdes/powerdesk02.dbf

/home/oradata/powerdes/datagm01.dbf

/home/oradata/powerdes/plimp01.DBF

/home/oradata/powerdes/dwetl01.DBF

/home/oradata/powerdes/dw02.DBF

/home/oradata/powerdes/timdba01.DBF

17rows selected.

SQL>

重启查看新的是否生效

SQL>shutdown immediate;

ORA-01109:database not open

Databasedismounted.

ORACLEinstance shut down.

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 2.6991E+10 bytes

FixedSize 2213976 bytes

VariableSize 1.9059E+10 bytes

DatabaseBuffers 7784628224 bytes

RedoBuffers 145174528 bytes

Databasemounted.

SQL>

SQL>select member from v$logfile;

MEMBER

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

/home/oradata/powerdes/redo03.log

/home/oradata/powerdes/redo02.log

/home/oradata/powerdes/redo01.log

/home/oradata/powerdes/redo_dg_021.log

/home/oradata/powerdes/redo_dg_022.log

/home/oradata/powerdes/redo_dg_023.log

6rows selected.

SQL>

如果主库备库都是一致的话，需要修改

----处理方法：

----主库上：

alter system setlog_archive_dest_2='service=dg_22 sync affirm net_timeout=10valid_for=(online_logfile,primary_role) db_unique_name=dg_22';

----备库上：

alter system set db_unique_name=dg_22scope=spfile;

shut immediate

startup mount

alter system set service_names=orcl; ----服务名和主库一致

最后再开启日志应用，这时主库切换日志后，可以看到备库能正常应用日志，并且主库没有出现任何异常了。

1 0