OCM_Session8_3_RoleTransitionsInvolvingPhysicalStandbyDatabases

在上一章節已經測試過物理備庫是否在應用歸檔日志，在這一章節中再次驗證主庫創建的表空間是否應用到備庫。
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 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.