Dbabc.Net

SELECT SWITCHOVER_STATUS FROM V$DATABASE;查看状态
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;主库Primary DB 切换为备库Standby DB
RECOVER STANDBY DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;备库Standby DB切换为主库Primary DB
一、主库Primary DB 切换为备库Standby DB
1.1、重启Primary DB，为了防止重启之后有新用户链接上来，先把用户锁定。
由于这个库是09年6月4日新建的，将这之后的新建的用户进行锁定。
锁定用户:
SELECT ‘ALTER USER ‘ || username || ‘ ACCOUNT LOCK;’
FROM dba_users
WHERE created > TO_DATE (’20090604′, ‘yyyymmdd’);
运行以上sql的执行出来的结果，将用户锁定。
同时为了更快的关掉主机，kill掉Oracle的链接，可以参考：Linux下kill掉所有的Oracle远程连接进程：http://dbabc.net/archives/2010/02/08/linux_kill_all_oracle_process.shtml
kill掉所有的进程
ps -efww|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9
然后进行重启库的操作。
SYS@dbabc.net>shutdown immediate
SYS@dbabc.net>startup
1.2、在Primary DB检查是否可以进行switch over
SYS@dbabc.net> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE
如果状态为 SESSIONS ACTIVE,使用with session shutdown 选项。
SYS@dbabc.net> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
否则使用
SYS@dbabc.net> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 进行切换。
然后执行以下:
SYS@dbabc.net> SHUTDOWN IMMEDIATE;
SYS@dbabc.net> STARTUP NOMOUNT;
SYS@dbabc.net> ALTER DATABASE MOUNT STANDBY DATABASE;
二、Standby DB切换为Primary DB
2.1、Standby DB检查是否可以进行switch over
SYS@sty.dbabc.net> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
如果是SESSIONS ACTIVE 同样适用with session shutdown 选项。
SYS@sty.dbabc.net> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
如果状态不是 TO PRIMARY 通常是由于归档没有应用完成引起的，可以通过
SYS@sty.dbabc.net> select name,creator,sequence#,applied,completion_time from v$archived_log where applied=’NO’;查询未应用的归档。如果有未应用的归档，可以使用
SYS@sty.dbabc.net> recover standby database;
手动应用归档
应用完归档之后进行切换
SYS@sty.dbabc.net> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SYS@sty.dbabc.net> SHUTDOWN IMMEDIATE;
SYS@sty.dbabc.net> STARTUP;
确认是否ok，若正常，然后关库，修改ip、监听信息。
ip修改完成之后，进行如下操作进行check：
在新primary上执行
ALTER SYSTEM ARCHIVE LOG CURRENT;
查看归档是否可以正常传输应用
在应用归档的时候可能报
ORA-16145: archival for thread# 1 sequence# 30746 in progress
这个错误，重启 db重新应用可解决。
正常之后解锁用户
解锁刚才锁定的用户
SELECT ‘ALTER USER ‘ || username || ‘ ACCOUNT UNLOCK;’
FROM dba_users
WHERE created > TO_DATE (’20090604′, ‘yyyymmdd’);
附：
切换日志
Tue Apr 25 23:04:38 2010
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
Tue Apr 25 23:04:38 2010
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (orcl)
Tue Apr 25 23:04:40 2010
Thread 1 cannot allocate new log, sequence 98762
Checkpoint not complete
Current log# 1 seq# 30739 mem# 0: /app/product/oradata/orcl/redo01.log
Tue Apr 25 23:04:47 2010
Thread 1 advanced to log sequence 98762 (LGWR switch)
Current log# 2 seq# 98762 mem# 0: /app/product/oradata/orcl/redo02.log
Tue Apr 25 23:04:57 2010
Thread 1 advanced to log sequence 98763 (LGWR switch)
Current log# 3 seq# 98763 mem# 0: /app/product/oradata/orcl/redo03.log
Tue Apr 25 23:04:57 2010
ARCH: Standby redo logfile selected for thread 1 sequence 98762 for destination LOG_ARCHIVE_DEST_2
Tue Apr 25 23:04:58 2010
Stopping background process CJQ0
Tue Apr 25 23:04:58 2010
SMON: disabling tx recovery
Tue Apr 25 23:04:58 2010
Stopping background process QMNC
Tue Apr 25 23:05:00 2010
Stopping Job queue slave processes, flags = 27
Tue Apr 25 23:05:03 2010
Waiting for Job queue slaves to complete
Tue Apr 25 23:05:21 2010
Job queue slave processes stopped
Waiting for dispatcher ‘D000′ to shutdown
All dispatchers and shared servers shutdown
Tue Apr 25 23:05:23 2010
SMON: disabling cache recovery
Tue Apr 25 23:05:23 2010
Shutting down archive processes
Archiving is disabled
Tue Apr 25 23:05:28 2010
ARCH shutting down
ARC1: Archival stopped
Tue Apr 25 23:05:33 2010
ARCH shutting down
ARC0: Archival stopped
Tue Apr 25 23:05:34 2010
Thread 1 closed at log sequence 98763
Successful close of redo thread 1
Tue Apr 25 23:05:34 2010
ARCH: Noswitch archival of thread 1, sequence 98763
ARCH: End-Of-Redo Branch archival of thread 1 sequence 98763
ARCH: Archiving is disabled due to current logfile archival
Clearing standby activation ID 1219516766 (0x48b0595e)
The primary database controlfile was created using the
‘MAXLOGFILES 16′ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 52428800;
Archivelog for thread 1 sequence 98763 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
MRP0 started with pid=13, OS id=25699
Tue Apr 25 23:05:35 2010
MRP0: Background Managed Standby Recovery process started (orcl)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 7 processes
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /ora_log/orcl/arch/1_98763_691760075.dbf
Identified End-Of-Redo for thread 1 sequence 98763
Tue Apr 25 23:05:42 2010
Media Recovery End-Of-Redo indicator encountered
Tue Apr 25 23:05:42 2010
Media Recovery Applied until change 5978867672
Tue Apr 25 23:05:42 2010
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
Resetting standby activation ID 1219516766 (0x48b0595e)
Tue Apr 25 23:05:44 2010
MRP0: Background Media Recovery process shutdown (orcl)
Tue Apr 25 23:05:45 2010
Switchover: Complete – Database shutdown required (orcl)
Tue Apr 25 23:05:45 2010
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN