oracle 11g switchover
时间:2014-05-06 05:05:24 作者:beebol 标签: dataguard switchover 分类: Oracle
oracle dataguard搭建请看如下链接
switchover主要是在数据库升级，硬件升级等进行。如下将如何进行switchover：
在prmary将数据库设置为standby
SQL> alter database commit to switchover to physical standby;Database altered. Database altered.
11g时，执行这个命令后，数据库已经关闭了，不需要手动进行关闭了
SQL> select db_unique_name,database_role,open_mode,switchover_status from v$database; select db_unique_name,database_role,open_mode,switchover_status from v$database * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 2650Session ID: 458 Serial number: 211 Session ID: 458 Serial number: 211
然后启动启动到mount状态查看：
SQL> startup mount ORACLE instance started. Total System Global Area 1.3462E+10 bytes Fixed Size 2265984 bytes Variable Size 3321891968 bytes Database Buffers 1.0133E+10 bytes Redo Buffers 4460544 bytes Database mounted. SQL> select db_unique_name,database_role,open_mode,switchover_status from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- SWITCHOVER_STATUS -------------------- actvdb01 PHYSICAL STANDBY MOUNTEDRECOVERY NEEDED RECOVERY NEEDED
查看已经是PHYSICAL STANDBY状态了。
打开数据库，这个时候应该是read only状态：
SQL> alter database open; Database altered. SQL> select db_unique_name,database_role,open_mode,switchover_status from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- SWITCHOVER_STATUS -------------------- actvdb01 PHYSICAL STANDBY READ ONLYRECOVERY NEEDED RECOVERY NEEDED
这个时候primary已经变成了standby了。
然后连接到之前的standby服务器上：
先查看状态，确定日志应用是否完整
SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- actvdb02 PHYSICAL STANDBY READ ONLY WITH APPLY SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 348 Next log sequence to archive 0 Current log sequence 353 SQL> alter database recover managed standby database cancel; Database altered. SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- --------------------actvdb02 PHYSICAL STANDBY READ ONLY actvdb02 PHYSICAL STANDBY READ ONLY
然后将standby切换成primary
SQL> alter database commit to switchover to primary; Database altered. SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- --------------------actvdb02 PRIMARY MOUNTED actvdb02 PRIMARY MOUNTED
完成后，状态已经变成的primary了，注意，在执行切换过程中，不能有任何session连接，如果有会有如下报错：
SQL> alter database commit to switchover to primary; alter database commit to switchover to primary * ERROR at line 1:ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
把session关闭掉，重新执行即可。
然后打开数据库,在新primary中（也就是老的standby）
SQL> alter database open; Database altered. SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- actvdb02 PRIMARY READ WRITE SQL> select db_unique_name,database_role,open_mode,switchover_status from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- SWITCHOVER_STATUS -------------------- actvdb02 PRIMARY READ WRITETO STANDBY TO STANDBY
查看状态已经变成了primary,且为read write了。表示已经成功从standby转成primary了
然后再登录到standby中（之前的primary中）启用mrp进程
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select db_unique_name,database_role,open_mode,switchover_status from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- SWITCHOVER_STATUS -------------------- actvdb01 PHYSICAL STANDBY READ ONLY WITH APPLYNOT ALLOWED NOT ALLOWED
再查看进程已经是应用日志状态了。
到新的primary中将创建表测试是否能正常应用日志：
SQL> create table test.t11 (id number)tablespace test;Table created. Table created.
到standby中查看已经能正常传输、应用日志了：
SQL> select owner,table_name from dba_tables where owner='TEST'; OWNER TABLE_NAME ------------------------------ ------------------------------ TEST T2 TEST TEST_TABLE TEST TEST01 TEST TEST02 TEST TEST03 TEST T4 TEST T5 TEST T7 TEST T6 TEST T8 TEST T11 OWNER TABLE_NAME ------------------------------ ------------------------------ TEST T912 rows selected. 12 rows selected.
只要前面 的配置没有问题，switchover是很简单的，特别注意以下几点：
*.local_listener 这个配置是的自己的tnsname名字
.fal_client='actvdb' 这个配置的是自己的tnsname,不管是primary，还是standby .fal_server='actvdbbak' 这个配置的是对方的tnsname,不管是primary，还是standby