正常的话就是上面这样,没有错误出现。
(3.2)在数据库A上切换归档
SQL> alter system switch logfile;
System altered.
(3.3)查看刚才做的归档,A库和B库之间是否同步。
(注释:如果即时查看延迟不同步,就稍等会看,如果出现延迟一般都是在主库上出现。实际情况上是可能出现延迟的,经常遇到)
A上:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
27
B上:
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
27
可以看到主备一致,同步是正常的。
4.确认standby同步没有问题后,将主库A和备库B做一次switchovers
(4.1) 查看主库(A)switchover 状态
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
附: A:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了。
B.ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database finish;
或者
Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或者
SQL>Alter database commit to switchover to physical standby with session shutdown;
如果(4.1)的状态是session actiive,这里就得加with session shutdown.如果是to standby,可以不加,不过这种情况加上也没错。
(4.3)查看备库(B机) switchover 状态
SQL> select database_role, switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
附:若不是,用此语句切换:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
补充:若出现:ORA-16139: media recovery required
是因为没有执行:alter database recover managed standby database disconnect from session;
(4.4) 将备库(B)切换成主库,然后关闭
SQL> alter database commit to switchover to primary;
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
cuug MOUNTED
SQL> shutdown immediate;
注意:到目前为止B是主库, A是备库。
5、开始对B机升级
对数据字典的升级只能是主库才能,因为只有主库才允许在open状态下进行read write.显而易见备库是做不到的的。
前面步骤已经对B还是备库的时候,对B的数据库软件进行了升级,现在为了对B的数据字典进行升级,需要B是主库才行。所以在B的数据库软件升级以后,需要做主备切换,使B成为当前的主库,这样才能升级B的数据字典。从而完成数据库B的整个升级。升级A的原理也是这样。【在B备库时升级B的数据库软件,而在B是主库时升级B的数据字典;A也是这样】
sqlplus '/as sysdba'
SQL> startup upgrade; ---以upgrade方式打开数据库,否则无法打开实例
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> shutdown immediate
SQL>startup;
SQL>select count(1) from dba_objects where status='INVALID';
(5.1)编译无效对象
SQL> @?/rdbms/admin/utlrp.sql
(5.2) 再执行下面过程:
declare
threads pls_integer:=&&1;
begin
utl_recomp.recomp_parallel(threads);
end;
验证一下:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
这个时候, B 机的数据库就切换为 primary 数据库了。(可以把应用切换到这台服务器上了)注:到目前为止A的角色是备库,B的角色是主库
6、在数据库A上:
注:到目前为止A的角色是备库,B的角色是主库
7.由于A机此时在standby状态,需切换回PRIMARY状态才能完成升级,这需要B机先切回备库,A机才可以切回主库
(7.1) 首先,A机启动到recover managed standby状态:
lsnrctl start
sqlplus / as sysdba
startup nomount;
alter system set compatible='10.2.0.4.0' scope=spfile
alter database mount standby database;
alter database recover managed standby database disconnect from session;
(7.2) 在B机上:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
lsnrctl start
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或者
SQL>Alter database commit to switchover to physical standby with session shutdown;
检查一下:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
(7.3) 在A机上:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
或
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown;
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
8.开始升级A机
sqlplus '/as sysdba'
SQL> startup upgrade; ---以upgrade方式打开数据库,否则无法打开实例
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> shutdown immediate
SQL> startup;
SQL> select count(1) from dba_objects where status='INVALID';
(8.1)编译无效对象
SQL> @?/rdbms/admin/utlrp.sql
(8.2) 再执行下面过程:
SQL>
declare
threads pls_integer:=&&1;
begin
utl_recomp.recomp_parallel(threads);
end;
最后验证一下:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
表明A机已切换回primary数据库了。
回到第 3 验证同步。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-750006/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-750006/