oracle数据库切换步骤笔记

数据库切换的操作步骤：
1. 察看主库状态 select switchover_status from v$database;
收集主库上的临时表空间的情况，原因是备库激活后临时文件可能丢失，需要手工建上去： col file_name format a40 select file_name,tablespace_name,bytes/1024/1024 from dba_temp_files;
FILE_NAME                                TABLESPACE_NAME      BYTES/1024/1024 ---------------------------------------- -------------------- --------------- /data/oradata/alihr/temp01.dbf           TEMP                            2048

2.切换主库到standby alter database commit to switchover to physical standby; 或： ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
shutdown immediate; startup nomount; alter database mount standby database;

3.验证要被切换的standby是否接收到switch to 通知 SELECT SWITCHOVER_STATUS FROM V$DATABASE;  以前的状态就是SESSIONS ACTIVE,现在就变为TO PRIMARY

4.切换物理standby到主用模式，检查redo log是否创建好 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; shutdown immediate; startup;
5. standby数据库切换成主库后，检查是否需要、对临时表空间增加临时文件: 先检查临时文件是否丢失： col file_name format a60 select file_name,tablespace_name from dba_temp_files; 把结果前面原主库上的临时文件进行对比，如有丢失则使用如下命令增加： alter tablespace temp add tempfile '/data/oradata/alihr/temp02.dbf' size 2048M reuse; 正常情况下，如果db_file_name_convert参数设置正确的话，11g会自动建立temp file
6.在新的standby机器上 alter system set log_archive_dest_state_2='defer'; alter database recover managed standby database disconnect from session;
7.检查主备库中fal参数 fal_server服务名是在standby机器的tnsnames中，指向主库，fal_client是在主库上的tnsnames中，指向standby。 fal参数只在standby机器上生效，所以在standby机器上fal_server指向主库，fal_client机器指向备库. 而主库上的fal参数虽然不生效，但为了避免主库切换成standby时，我们还要修改fal参数，所以现在就设置好， 让fal_server指向现在的standby数据库，fal_client指向自己。
8.在新的主库 alter system set log_archive_dest_state_2='enable'; alter system archive log current;  然后到备库上检查备库机器上日志是否正常的传过来了。

9.检查和调整主备库两台机器中的crontab中的数据库备份脚本和删除归档脚本。

=================oracle11g的最大保护和最大可用模式下的切换================== 如果主库是意外宕机的,则直接把备库切换成主库：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; shutdown immediate; connect / as sysdba; startup mount; ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; shutdown immediate; connect / as sysdba; startup mount; ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; alter database open;
注意：上面操作中是手工的把数据库重新置成MAXIMIZE AVAILABILITY，否则数据库起来后是最大性能模式。
检查数据库的角色： select database_role from v$database;
原有主库启动后，如果不是硬盘坏，主库上的数据还存在，则把主库转换成standby就可以了： 主库上： startup mount; recover automatic database; ALTER DATABASE CONVERT TO PHYSICAL STANDBY; shutdown immediate;
注意千万不要把主库打开了，否则会导致主库的SCN发生变化，无法转换成standby数据库了。

在把原先的主库转化为standby时，有时可能报如下错误： SQL> alter database recover managed standby database finish; alter database recover managed standby database finish * ERROR at line 1: ORA-00283: recovery session canceled due to errors ORA-19909: datafile 1 belongs to an orphan incarnation ORA-01110: data file 1: '/opt/oracle/oradata/oratest/system01.dbf'
这里把到rman中： list incarnation of database; reset database to incarnation 1; recover database; reset database to incarnation 2;
========================================================= failover在物理standby的切换
1.检查standby看是否使用了standby log 2.有standby log,执行下面的命令 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 2.1 没有standby log则不执行上面的 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE; 3. 切换 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3.1 如果上面3步骤失败,则 ALTER DATABASE ACTIVATE STANDBY DATABASE; 4. 重启db shutdown immediate startup

switchover的方法：
主库和物理standby的切换 1.察看主库状态 SELECT SWITCHOVER_STATUS FROM V$DATABASE; 2.切换 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 3.原主库 shutdown immediate; startup nomount alter database mount standby database;
4.验证要被切换的standby是否接收到switch to 通知 SELECT SWITCHOVER_STATUS FROM V$DATABASE;  增加online redo日志 5.切换物理standby到主 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; shutdown immediate; startup; 6.在新的standby机器（也就是老的主库） ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7.在新的主库 alter system archive log current; 


========active standby database=========== 当: alter database activate standby database; 原来的主库只能通过闪回转化成standby database，但要求数据库的flashback打开。
在新主库上： select to_char(standby_became_primary_scn) from v$database;
在旧主库上： SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO SCN standby_became_primary_scn; ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

select max(sequence#) from v$log_history where RESETLOGS_TIME=(select max(RESETLOGS_TIME) from v$log_history); 


实例恢复： recover managed standby database disconnect using current logfile;
=================lgwr设置==================================== alter system set log_archive_dest_3 = 'location=/disk3/arch/bopscha reopen=2 MAX_FAILURE=3'; alter system set log_archive_dest_state_3 = alternate;
alter system set log_archive_dest_1 = 'location=/disk2/arch/bopscha alternate=log_archive_dest_3 reopen=60 MAX_FAILURE=5' ;
*.log_archive_dest_2='SERVICE=DTMRT LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DTMRT';
alter database add standby logfile group 4 ('/oracle/u02/ORA10GDG/STANDBYRD01.LOG') size 200M; select group#,thread#,sequence#,archived,status from v$standby_log;

alter database set standby database to maximize {availability | performance | protection};
select protection_mode from v$database;

ORA-19527: LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/oract/','/u02/oracle/oradata/oract/'
alter system set log_archive_dest_2='SERVICE=bopsteststb lgwr sync affirm';

select frequency, duration from v$redo_dest_resp_histogram where dest_id=2 and frequency>1;


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 


alter database convert to snapshot standby; alter database convert to physical standby;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
alter database commit to switchover to physical standby;
alter database activate standby database finish apply;

alter database recover managed standby database finish; 

旧主库转换成standby的步骤： 在新主库上： select to_char(standby_became_primary_scn) from v$database;
在旧主库上： SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO SCN standby_became_primary_scn; ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;