OCM_Session8_3_Role Transitions Involving Physical Standby Databases

 

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

  • 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

  • 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
        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
        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,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。