OCM_Session8_3_Role Transitions Involving Physical Standby Databases

2022-02-01 06:35:30

在上一章节已经测试过物理备库是否在应用归档日志，在这一章节中再次验证主库创建的表空间是否应用到备库。

1.首先创建一个表空间。

SYS@PROD>create tablespace swtich_tbs datafile ‘/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf‘ size 10m;

Tablespace created.

##################################################################################################

tail -f alert_PROD.log

ue Mar 25 12:25:44 2014

create tablespace swtich_tbs datafile ‘/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf‘ size 10m

Tue Mar 25 12:25:47 2014

Completed: create tablespace swtich_tbs datafile ‘/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf‘ size 10m

##################################################################################################

2.手动归档，让日志同步。

SYS@PROD>alter system archive log current;

System altered.

##################################################################################################

tail -f alert_PROD.log

LNS1 started with pid=54, OS id=9623

Tue Mar 25 12:26:37 2014

Thread 1 advanced to log sequence 18

Current log# 5 seq# 18 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo05.log

Current log# 5 seq# 18 mem# 1: /u01/app/oracle/oradata/PROD/Disk2/redo05_1.log

Tue Mar 25 12:26:38 2014

LNS: Standby redo logfile selected for thread 1 sequence 18 for destination LOG_ARCHIVE_DEST_2

Tue Mar 25 12:27:08 2014

ARC8: Standby redo logfile selected for thread 1 sequence 17 for destination LOG_ARCHIVE_DEST_2

tail -f alert_PRODSTD.log

Tue Mar 25 12:26:30 2014

RFS[4]: Possible network disconnect with primary database

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[5]: Assigned to RFS process 4505

RFS[5]: Identified database type as ‘physical standby‘

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

RFS[5]: Successfully opened standby log 7: ‘/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log‘

Tue Mar 25 12:26:42 2014

Fetching gap sequence in thread 1, gap sequence 17-17

Tue Mar 25 12:27:08 2014

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[6]: Assigned to RFS process 4508

RFS[6]: Identified database type as ‘physical standby‘

RFS[6]: Successfully opened standby log 6: ‘/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log‘

Tue Mar 25 12:27:13 2014

Media Recovery Log /u01/app/oracle/oradata/PRODSTD/Disk2/arch/1_17_842523531.arc

Successfully added datafile 5 to media recovery

Datafile #5: ‘/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9m21f1f0_.dbf‘

Media Recovery Waiting for thread 1 sequence 18 (in transit)

##################################################################################################

3.分别在主库备库中都能查到刚在主库新建的表空间。

SYS@PROD>select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMPTS1 NO NO YES

12 SWTICH_TBS YES NO YES

7 EXAMPLE YES YES YES

10 USERS YES NO YES

7 rows selected.

SYS@PRODSTD>select * from v$tablespace;

TS# NAME INC BIG FLA ENC

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMPTS1 NO NO YES

12 SWTICH_TBS YES NO YES

7 EXAMPLE YES YES YES

10 USERS YES NO YES

7 rows selected.

第一次切换，主库PROD与备库PRODSTD切换。

参考官方文档：7 Role Transitions-->7.2 Role Transitions Involving Physical Standby Databases

http://docs.oracle.com/cd/B19306_01/server.102/b14239/role_management.htm#i1033702

Step 1 Verify it is possible to perform a switchover.

SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

SYS@PRODSTD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY SESSIONS ACTIVE

Step 2 Initiate the switchover on the primary database.

SYS@PROD>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

Step 3 Shut down and restart the former primary instance.

SYS@PROD>SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SYS@PROD>STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220360 bytes

Variable Size 176161016 bytes

Database Buffers 343932928 bytes

Redo Buffers 2973696 bytes

Database mounted.

Step 4 Verify the switchover status in the V$DATABASE view.

SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@PRODSTD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

Step 5 Switch the target physical standby database role to the primary role.

SYS@PRODSTD> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

此命令成功的条件是没有任何其他的会话连接到主库，如有可以使用如下命令：

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;

Step 6 Finish the transition of the standby database to the primary role.

SYS@PRODSTD>ALTER DATABASE OPEN;

Database altered.

Step 7 If necessary, restart log apply services on the standby databases.

SYS@PROD>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session parallel 2;

Database altered.

SYS@PROD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APP

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

17 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 NO

19 25-MAR-14 25-MAR-14 YES

19 25-MAR-14 25-MAR-14 NO

20 25-MAR-14 25-MAR-14 YES

20 25-MAR-14 25-MAR-14 NO

21 25-MAR-14 25-MAR-14 YES

26 rows selected.

SYS@PROD>

SYS@PRODSTD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APP

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

17 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 YES

19 25-MAR-14 25-MAR-14 YES

20 25-MAR-14 25-MAR-14 YES

21 25-MAR-14 25-MAR-14 NO

21 25-MAR-14 25-MAR-14 YES

12 rows selected.

Step 8 Begin sending redo data to the standby databases.

SYS@PRODSTD>ALTER SYSTEM SWITCH LOGFILE;

System altered.

############################################################################################

tail -f alert_PROD.log

Tue Mar 25 13:01:50 2014

Primary database is in MAXIMUM PERFORMANCE mode

RFS[2]: Successfully opened standby log 7: ‘/u01/app/oracle/oradata/PROD/Disk1/standby07.log‘

Tue Mar 25 13:01:51 2014

Media Recovery Log /u01/app/oracle/oradata/PROD/Disk2/arch/1_22_842523531.arc

Media Recovery Waiting for thread 1 sequence 23 (in transit)

tail -f alert_PRODSTD.log

Tue Mar 25 13:01:49 2014

Thread 1 advanced to log sequence 23

Current log# 3 seq# 23 mem# 0: /u01/app/oracle/oradata/PRODSTD/Disk1/redo03.log

Current log# 3 seq# 23 mem# 1: /u01/app/oracle/oradata/PRODSTD/Disk2/redo03_1.log

Tue Mar 25 13:01:51 2014

LNS: Standby redo logfile selected for thread 1 sequence 23 for destination LOG_ARCHIVE_DEST_2

############################################################################################

SYS@PROD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APP

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

...

17 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 NO

19 25-MAR-14 25-MAR-14 YES

19 25-MAR-14 25-MAR-14 NO

20 25-MAR-14 25-MAR-14 YES

20 25-MAR-14 25-MAR-14 NO

21 25-MAR-14 25-MAR-14 YES

22 25-MAR-14 25-MAR-14 YES

27 rows selected.

SYS@PROD>

SYS@PRODSTD>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied from v$archived_log;

SEQUENCE# FIRST_TIM NEXT_TIME APP

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

.....

17 25-MAR-14 25-MAR-14 YES

18 25-MAR-14 25-MAR-14 YES

19 25-MAR-14 25-MAR-14 YES

20 25-MAR-14 25-MAR-14 YES

21 25-MAR-14 25-MAR-14 NO

21 25-MAR-14 25-MAR-14 YES

22 25-MAR-14 25-MAR-14 NO

22 25-MAR-14 25-MAR-14 YES

14 rows selected.

############################################################################################

在PRODSTD数据库中创建一个表，验证是否能在PROD数据库中查到。这里的验证可能有点问题。

SYS@PRODSTD>create table switch_tb as select * from dba_objects;

Table created.

SYS@PRODSTD>ALTER SYSTEM SWITCH LOGFILE;

System altered.

tail -f alert_PROD.log

Tue Mar 25 13:05:50 2014

Primary database is in MAXIMUM PERFORMANCE mode

RFS[2]: Successfully opened standby log 6: ‘/u01/app/oracle/oradata/PROD/Disk1/standby06.log‘

Tue Mar 25 13:05:54 2014

Media Recovery Log /u01/app/oracle/oradata/PROD/Disk2/arch/1_23_842523531.arc

Media Recovery Waiting for thread 1 sequence 24 (in transit)

tail -f alert_PRODSTD.log

Tue Mar 25 13:05:49 2014

Thread 1 advanced to log sequence 24

Current log# 4 seq# 24 mem# 0: /u01/app/oracle/oradata/PRODSTD/Disk1/redo04.log

Current log# 4 seq# 24 mem# 1: /u01/app/oracle/oradata/PRODSTD/Disk2/redo04_1.log

Tue Mar 25 13:05:50 2014

LNS: Standby redo logfile selected for thread 1 sequence 24 for destination LOG_ARCHIVE_DEST_2

SYS@PROD>select count(*) from switch_tb;

select count(*) from switch_tb

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SYS@PROD>startup force

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220360 bytes

Variable Size 176161016 bytes

Database Buffers 343932928 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS@PROD>select count(*) from switch_tb;

COUNT(*)

----------

9400

SYS@PROD>

############################################################################################

第一次切换，主库PROD与备库PRODSTD切换。

PRODSTD主库切换成备库

SYS@PRODSTD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY SESSIONS ACTIVE

SYS@PRODSTD>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

SYS@PRODSTD>SHUTDOWN IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SYS@PRODSTD>STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220360 bytes

Variable Size 192938232 bytes

Database Buffers 327155712 bytes

Redo Buffers 2973696 bytes

Database mounted.

SYS@PRODSTD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

PROD备库切换成主库

SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

SYS@PROD>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SYS@PROD>ALTER DATABASE OPEN;

Database altered.

SYS@PROD>select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PRIMARY TO STANDBY

最后确认备库处于read only状态

SYS@PRODSTD>alter database open read only;

Database altered.

OCM_Session8_3_Role Transitions Involving Physical Standby Databases,布布扣,bubuko.com

OCM_Session8_3_Role Transitions Involving Physical Standby Databases

码农公寓

相关文章