oracle 10g DG 安装配置说明V2

最新推荐文章于 2025-03-10 21:40:23 发布

guo_rui22

最新推荐文章于 2025-03-10 21:40:23 发布

阅读量2.7k

点赞数

分类专栏： 8、数据库相关

本文链接：https://blog.youkuaiyun.com/guo_rui22/article/details/12711571

版权

8、数据库相关专栏收录该内容

23 篇文章

订阅专栏

本文详细介绍Oracle Data Guard配置步骤，包括环境准备、PRIMARY及STANDBY数据库配置、网络配置、数据文件迁移等关键环节，并演示了保护模式切换及物理备库转逻辑备库的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一．环境

1. 软件环境

1.1. 系统 centos 5.5 x64

1.2. Databases 10.2.0.5

1.3. DG 物理模式

2. 环境规划

	PRIMARY	STANDBY
HOSTNAME	dg1	dg2
IP	192.168.0.176	192.168.0.176
SID	mt	mt
SERVICE_NAME	mt1	mt2
DB_UNIQUE_NAME	mt1	mt2

3. 准备工作

3.1. 俩台机器安装centos 5.5x64

3.2. 俩台机器安装数据库，PRIMARY创建mt , STANDBY不需要安装实例

3.3 设置host文件

192.168.0.176 dg1

192.168.0.176 dg2

添加到俩台主机/etc/hosts

二．PRIMARY 配置

1. 确认主库处于归档模式，如果为非归档则必须改为归档模式

SQL>archive log list;

Databaselog mode No Archive Mode

Automaticarchival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldestonline log sequence 1

Currentlog sequence 2

改为归档模式

[root@dg1~]# mkdir /u01/archivelog

[root@dg1~]# chown oracle.oinstall /u01/archivelog/

进入sqlplus

SQL> shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 285212672 bytes

FixedSize 2020192 bytes

VariableSize 109055136 bytes

DatabaseBuffers 167772160 bytes

RedoBuffers 6365184 bytes

Databasemounted.

SQL>alter system set log_archive_dest_1='location=/u01/archivelog' scope =both;

Systemaltered.

SQL>alter database archivelog;

Databasealtered.

SQL>alter database open;

Databasealtered.

SQL>archive log list;

Databaselog mode Archive Mode

Automaticarchival Enabled

Archivedestination /u01/archivelog

Oldestonline log sequence 0

Next logsequence to archive 1

Currentlog sequence 1

2. 将primary 数据库置为FORCE LOGGING 模式

SQL>alter database force logging;

Database altered.

(在DataGuard环境下，如果主库不加alter database force logging . 主库用工具做大量数据插入而不到日志中去，此时插入的数据不会同步到DataGuard数据库。加入alter database forcelogging则数据都会同步过去,即使在归档模式下，也有可能会有一些nologging 的操作不产生redo,这是DG不允许的,因此必须启用数据库强制记录redo

)

3. 添加standbylogfile

3.1 查看当前redo log

SQL> select * from v$logfile;

3.2 添加standbylogfile(日志传输为ARCH模式添加。最高性能模式可有可无)

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/mt/redo04.log') size 50M;

Database altered.

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('/u01/app/oracle/oradata/mt/redo05.log') size 50M;

Database altered.

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/mt/redo06.log') size 50M;

Database altered.

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('/u01/app/oracle/oradata/mt/redo07.log') size 50M;

Database altered.

添加备用日志文件是规则：备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。(每线程日志文件最大数目 + 1 ) * 线程数。

standby的时候有两种传递日志的方式，一种是常见的archivr log，由ARCH的后台进程控制传递到standby数据库，还有一种是和redo log一样的传递方式，由产生redo log的后台进程控制，就需要standby log

在最大可用和最大保护模式，因为是采用LGWR SYNC进行redo的传送，一定要用standby logfile，但是建议在最大性能模式也添加standby logfile，据说在失败切换时可以恢复更多的数据

4. 配置网络

4.1 配置listener.ora

[root@dg1admin]# more listener.ora

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/app/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME =mt)

(ORACLE_HOME=/u01/app/oracle/product/10.2.0)

(SID_NAME = mt)

)

LISTENER=

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))

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

)

4.2 配置tnsnames.ora

[root@dg1 admin]#more listener.ora

MT1 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = mt)

)

MT2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = mt)

)

ORCL =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

EXTPROC_CONNECTION_DATA=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

5. 修改PRIMARY 参数文件

5.1 在线修改

altersystem set standby_archive_dest='/u01/archivelog' scope =spfile;

altersystem set standby_file_management=auto scope =spfile;

altersystem set fal_server=mt1 scope =spfile;

altersystem set fal_client=mt2 scope =spfile;

altersystem set log_archive_dest_1='location=/oradata/archivelog' scope =spfile;

alter system set log_archive_dest_2='service=mt2' VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=mt2' scope =both;

5.2 参数说明

•DB_UNIQUE_NAME=primary --show parameter DB_UNIQUE_NAME确认主库名称

•LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(primary,standby)‘ --此处为主库网络连接串(tnsnames.ora)

•LOG_ARCHIVE_DEST_1=‘LOCATION=/u01/primary/archive --主库的归档日志路径 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary‘ –主库的DB_UNIQUE_NAME

•LOG_ARCHIVE_DEST_2=‘SERVICE=standbyLGWR SYNC AFFIRM --此处为备库网络连接串(tnsnames.ora) VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standby‘ –备库的DB_UNIQUE_NAME

•LOG_ARCHIVE_DEST_STATE_1=ENABLE

•LOG_ARCHIVE_DEST_STATE_2=ENABLE

•REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

•FAL_SERVER=standby --网络异常恢复后将延迟的归档日志传输到备库

•FAL_CLIENT=primary

•DB_FILE_NAME_CONVERT='/u01/primary/oradata','/u01/standby/oradata‘

•LOG_FILE_NAME_CONVERT='/u01/primary/oradata','/u01/standby/oradata‘

说明:‘/u01/primary/oradata’为主库数据\日志文件路径;‘/u01/standby/oradata‘为备库数据\日志文件路径

•STANDBY_FILE_MANAGEMENT=AUTO --主库创建表空间或数据文件,那么这些修改直接同步到备库,无须手工操作.

6. 生成新的spfile

SQL>create pfile from spfile;

Filecreated.

oracle会优先使用spfilemt.ora, 如果没有就用Initmt.ora,所以可以先把spfilemt.ora删除，或者再生成个新的spfilemt.ora

三．standby 配置

1. 复制PRIMARY 密码文件到STANDBY

[oracle@dg1~]$ cd /u01/app/oracle/product/10.2.0/dbs

[oracle@dg1 dbs]$ scporapwmt oracle@192.168.0.177:/u01/app/oracle/product/10.2.0/dbs

oracle@192.168.0.177'spassword:

orapwmt 100%1536 1.5KB/s 00:00

为了保证SYS的密码相同,或者如果知道主库SYS密码则直接修改备库SYS密码

2. 修改standby 参数文件

拷贝PRIMARY 参数文件到standby

[oracle@dg1 dbs]$scp initmt.oraoracle@192.168.0.177:/u01/app/oracle/product/10.2.0/dbs

oracle@192.168.0.177'spassword:

initmt.ora 100% 1592 1.6KB/s 00:00

修改如下几个参数

log_archive_dest_2='service=mt2'DB_FILE_NAME_CONVERT='/u01/archivelog/oradata','/u01/archivelog/oradata'

LOG_FILE_NAME_CONVERT='/u01/archivelogoradata','/u01/archivelog/oradata'

3. 配置standby 网络配置

3.1 配置listener.ora

[oracle@dg1admin]$ scp listener.ora oracle@192.168.0.177:/u01/app/oracle/product/10.2.0/network/admin/

修改HOST 为dg2

3.2 配置tnsnames.ora

[oracle@dg1 admin]$ scp tnsnames.ora oracle@192.168.0.177:/u01/app/oracle/product/10.2.0/network/admin/

修改HOST为dg2

4. 迁移PRIMARY 数据库的数据文件、控制文件

有俩种方法

4.1 文件物理拷贝(需要停Primary)

4.1.1 Primary上创建standby 数据库所需的控制文件

SQL>alter database create standby controlfile as '/u01/standby.ctl';

4.1.2拷贝控制文件、数据文件、log目录到standby

停止Primary

SQL>shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

Log目录

[oracle@dg1 oracle]$ cd /u01/app/oracle

[oracle@dg1 oracle]$ scp -r admin/mt oracle@192.168.0.177:/u01/app/oracle/admin/

控制文件

[oracle@dg1 oracle]$ scp -r /u01/standby.ctl oracle@192.168.0.177:/u01/app/oracle/oradata/mt/

数据文件

[oracle@dg1 oracle]$ scp -r oradata/mt oracle@192.168.0.177:/u01/app/oracle/oradata/

[root@dg2 mt]# cd /u01/app/oracle/oradata/mt

[root@dg2mt]# rm -rf control0*

[oracle@dg2mt]$ cp standby.ctl control01.ctl

[oracle@dg2mt]$ cp control01.ctl control02.ctl

[oracle@dg2mt]$ cp control01.ctl control03.ctl

2.2 RMAN (不需要停Primary)

主库备份数据文件归档

[oracle@oracle1~]$ rman target /

RecoveryManager: Release 10.2.0.1.0 - Production on Wed Oct 9 08:51:29 2013

connectedto target database: TEST (DBID=2126483265)

RMAN>run

2>{allocate channel c1 type disk;

3>allocate channel c2 type disk;

4>backup database format '/u01/rman/full_%d_%T_%s_%p';

5> sql'alter system archive log current';

6>backup archivelog all format '/u01/rman/arh_%d_%T_%s_%p' delete input;

7> }

主库备份控制文件

alterdatabase create standby controlfile as '/u01/standby.ctl';

拷贝备份的数据文件、归档、控制文件到备库目录

备库还原控制文件、数据文件

[oracle@oracle2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 909:11:57 2013

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 67111156 bytes

Database Buffers 96468992 bytes

Redo Buffers 2973696 bytes

RMAN> restore controlfile from '/u01/rman/1.ctl';

Starting restore at 09-OCT-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=/u01/app/oracle/oradata/test/control01.ctl

output filename=/u01/app/oracle/oradata/test/control02.ctl

output filename=/u01/app/oracle/oradata/test/control03.ctl

Finished restore at 09-OCT-13

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> run

2> {allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> restore database;

5> }

关闭备库启动到stanby

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 67111156 bytes

Database Buffers 96468992 bytes

Redo Buffers 2973696 bytes

SQL> alter database mount standby database;

Database altered.

备库rman恢复归档

[oracle@oracle2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 909:15:43 2013

connected to target database: TEST (DBID=2126483265, not open)

RMAN> recover database;

备库开启同步进程

alter database recover managed standby database disconnect fromsession;

至此，Data Guard 的操作已经完成，下面来开始验证。

四．启动验证

4.1 在备库将实例启动到mount 状态

SQL> startupnomount;

ORACLE instancestarted.

Total SystemGlobal Area 918552576 bytes

Fixed Size 2025040 bytes

VariableSize 247466416 bytes

DatabaseBuffers 662700032 bytes

RedoBuffers 6361088 bytes

SQL> alter database mount standby database;

Databasealtered.

SQL> recover managed standby databaseusing current logfile disconnect from session; 开始实时应用redo log

Databasealtered.

4.2 在备库启动监听

[oracle@dg2 mt]$ lsnrctl start

4.3 在主库上启动

SQL> startup

ORACLE instancestarted.

Total SystemGlobal Area 918552576 bytes

Fixed Size 2025040 bytes

VariableSize 247466416 bytes

DatabaseBuffers 662700032 bytes

RedoBuffers 6361088 bytes

Databasemounted.

Database opened.

4.4 在主库启动监听

[oracle@dg1 oracle]$ lsnrctl start

4.5 查看主库一些状态

4.5.1 进程

SQL> selectprocess from v$managed_standby;

PROCESS

---------

ARCH

LGWR

4.5.2 进程一些相关状态

SQL> selectprocess,client_process,sequence#,status from v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

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

ARCH ARCH 0 CONNECTED

ARCH ARCH 31 CLOSING

LGWR LGWR 32 WRITIN

4.5.3 归档日志最大序列号

SQL> selectmax(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4.5.4 日志应用情况

select name,creator,sequence#,applied,completion_time from v$archived_log;

4.5.5 查询数据块角色和保护级别

SQL> selectdatabase_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_statusfrom v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE

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

PROTECTION_LEVEL SWITCHOVER_STATUS

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

PRIMARY mt1 READ WRITE MAXIMUMAVAILABILITY

MAXIMUMAVAILABILITY SESSIONS ACTIVE

4.5.6 查询DG 错误信息

select error from v$archive_dest;

4.5.7 查看orace 错误信息

tail -f /u01/app/oracle/admin/mt/bdump/alert_mt.log

4.6 查看备库一些状态

4.6.1 进程

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

MRP0

RFS

4.6.2 6 rows selected.进程一些相关状态

SQL> select process,client_process,sequence#,statusfrom v$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS

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

ARCH ARCH 31 CLOSING

ARCH ARCH 0 CONNECTED

MRP0 N/A 32 APPLYING_LOG

RFS LGWR 32 IDLE

RFS ARCH 0 IDLE

RFS UNKNOWN 0 IDLE

6 rows selected.

4.6.3 归档日志最大序列号

SQL> selectmax(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

4.6.4 日志应用情况

select name,creator,sequence#,applied,completion_time fromv$archived_log;

4.6.5 查询数据块角色和保护级别

SQL> selectdatabase_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_statusfrom v$database;

DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE

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

PROTECTION_LEVEL SWITCHOVER_STATUS

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

PHYSICAL STANDBYmt2 MOUNTED MAXIMUM AVAILABILITY

MAXIMUMAVAILABILITY SESSIONS ACTIVE

4.6.6 查询DG 错误信息

select error from v$archive_dest;

4.6.7 查看orace 错误信息

tail -f /u01/app/oracle/admin/mt/bdump/alert_mt.log

4.6.8 查看日志应用模式

SQL> select recovery_mode fromv$archive_dest_status where dest_id=2;

RECOVERY_MODE

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

MANAGED REALTIME APPLY

五．保护模式切换

1. 最大可用模式

1.1. 查看当前保护模式

SQL> select protection_mode,protection_level fromv$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE

当前为最大性能模式

1.2. 修改配置文件

修改initmt.ora文件

LOG_ARCHIVE_DEST_2='SERVICE=mt2 LGWR SYNC ARRIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=mt2'

1.3. 更改保护模式

1.3.1. 关闭数据库

SQL>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLE instance shut down.

1.3.2. 打开数据库到mount

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 918552576 bytes

FixedSize 2025040 bytes

VariableSize 247466416 bytes

DatabaseBuffers 662700032 bytes

RedoBuffers 6361088 bytes

Database mounted.

1.3.3. 修改模式为最大可用

SQL> alter database set standby database to maximizeavailability;

1.3.4. 打开数据库验证

SQL>alter database open;

Databasealtered.

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

2. 最大保护模式

2.1. 查看当前保护模式

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUMAVAILABILITY

当前为最大可用模式

2.2. 修改配置文件

修改initmt.ora文件

LOG_ARCHIVE_DEST_2='SERVICE=mt2 LGWR SYNC ARRIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=mt2'

2.3. 更改保护模式

2.3.1. 关闭数据库

SQL>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLE instance shut down.

2.3.2. 打开数据库到mount

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 918552576 bytes

FixedSize 2025040 bytes

VariableSize 247466416 bytes

DatabaseBuffers 662700032 bytes

RedoBuffers 6361088 bytes

Database mounted.

2.3.3. 修改模式为最大保护

SQL> alter database set standby database to maximize protection;;

2.3.4. 打开数据库验证

SQL>alter database open;

Databasealtered.

SQL>select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PROTECTION

SQL>

3. 最大性能模式

3.1. 查看当前保护模式

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM PROTECTION MAXIMUMPROTECTION

当前为最大保护模式

3.2. 修改配置文件

修改initmt.ora文件

LOG_ARCHIVE_DEST_2='SERVICE=mt2 LGWR SYNC ARRIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=mt2'

3.3. 更改保护模式

3.3.1. 关闭数据库

SQL>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLE instance shut down.

3.3.2. 打开数据库到mount

SQL>startup mount;

ORACLEinstance started.

TotalSystem Global Area 918552576 bytes

FixedSize 2025040 bytes

VariableSize 247466416 bytes

DatabaseBuffers 662700032 bytes

RedoBuffers 6361088 bytes

Database mounted.

3.3.3. 修改模式为最大性能

SQL> alter database set standby database to maximize performance;

3.3.4. 打开数据库验证

SQL>alter database open;

Databasealtered.

SQL>select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL>

六．物理standby 转换为逻辑standby

1. 主备库Switchover

1.1. 主库检查是否支持switchover操作，转换备库

SQL>select switchover_status from v$database;

SWITCHOVER_STATUS

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

SESSIONSACTIVE

如果switchover_status 为TO STANDBY 则执行

alterdatabase commit to switchover to physical standby;

若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行

SQL> alterdatabase commit to switchover to physical standby with session shutdown;

Databasealtered.

1.2. 关闭主库，启动到mount状态查看状态

SQL> shutdownimmediate;

ORA-01507:database not mounted

ORACLEinstance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 918552576 bytes

Fixed Size 2025040 bytes

Variable Size 247466416 bytes

Database Buffers 662700032 bytes

Redo Buffers 6361088 bytes

Database mounted.

SQL> recover managed standby database using current logfiledisconnect from session;

Media recovery complete.

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROMV$DATABASE;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MT MOUNTED PHYSICAL STANDBY mt1

可见状态已经转换为standby

1.3. 备库检查状态，并前转换为主库

SQL>select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database open;

1.4. 查看状态

SQL>SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MT READ WRITEPRIMARY mt2

1.5. 切换log 验证

原备库

SQL>alter database open;

Database altered.

SQL>Alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

原主库

SQL> select max(sequence#)from v$archived_log;

MAX(SEQUENCE#)

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

2. 恢复到原来状态

2.1. 主库检查是否支持switchover操作，转换备库

SQL>select switchover_status from v$database;

SWITCHOVER_STATUS

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

SESSIONSACTIVE

如果switchover_status 为TO STANDBY 则执行

alterdatabase commit to switchover to physical standby

若为SESSIONS ACTIVE表示当前会话处于ACTIVE,则执行

SQL> alterdatabase commit to switchover to physical standby with session shutdown;

Databasealtered.

2.2. 关闭主库，启动到mount状态查看状态

SQL>shutdown immediate;

ORA-01507:database not mounted

ORACLEinstance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 918552576 bytes

Fixed Size 2025040 bytes

Variable Size 247466416 bytes

Database Buffers 662700032 bytes

Redo Buffers 6361088 bytes

Database mounted.

SQL> recover managed standby database using current logfiledisconnect from session;

Media recovery complete.

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROMV$DATABASE;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MT MOUNTED PHYSICAL STANDBY mt2

可见状态已经转换为standby

2.3. 备库检查状态，并前转换为主库

SQL>select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO PRIMARY

SQL> alterdatabase commit to switchover to primary with session shutdown;

Database altered.

SQL>startup;

2.4. 查看状态

SQL>SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

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

MT READ WRITEPRIMARY mt1

2.5. 切换log 验证

原备库

SQL>Alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

原主库

SQL> select max(sequence#)from v$archived_log;

MAX(SEQUENCE#)

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