Oracle数据库主从切换

Oracle主从切换​

1. 主从查看打开状态：[root@odb1 ~]# su - oracle
   export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
   [oracle@odb1 ~]$ source .bash_profile
   [oracle@odb1 ~]$ sqlplus / as sysdba
   SQL> select open_mode from v$database;
2. 校验主数据库执行切换的前提条件：主库执行：
   ALTER SYSTEM ARCHIVE LOG CURRENT;
   ALTER SYSTEM ARCHIVE LOG CURRENT;
   主备库查看日志状态：
   SQL> select THREAD#,sequence#,FIRST_TIME, NEXT_TIME from v$archived_log where DEST_ID=1 order by FIRST_TIME;
   
   备库查看日志状态：
   SQL> Set linesize 200
   SQL> select THREAD#,sequence#,FIRST_TIME, NEXT_TIME from v$archived_log where DEST_ID=1 order by FIRST_TIME;
   
   

核对主备输出结果，关闭主库的一个RAC实例​

1. 在主库上执行：（在此之前关闭所以数据库连接）SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   期望输出结果为：TO STANDBY 或 SESSIONS ACTIVE
   

说明：如果该列值为"TO STANDBY"则表示primary 数据库支持转换为standby 角色，否则的话你就需要重新检查一下Data Guard 配置，比如看看LOG_ARCHIVE_DEST_n 之类参数值是否正确有效等等​

1. 在备库执行：SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   期望输出结果：NOT ALLOWED
   
2. 在主库上发起切换：首先将PRIMARY转换为standby 的角色SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
   

说明：PRIMARY进行转换完毕后，查看状态会变成RECOVERY NEEDED;​

1. 关闭主库实例并重启到SQL> shutdown abort;
   SQL> startup nomount;
   SQL> alter database mount standby database;
   
   
2. 主备库查看状态信息SQL> select database_role,switchover_status from v$database;
   
   
3. 备库查看切换状态：期望结果为：TO PRIMARYSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   
   
4. 备库切换数据库角色到主角色，将standby 切换到 primary 角色​SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;​
   
5. 备库查看日志​[oracle@odb2 trace]$ cd /u01/app/oracle/diag/rdbms/odb2/orcl/trace​
   [oracle@odb2 trace]$ tail -f alert_orcl.log -n 100​
   
6. 主备查看日志情况SQL> show parameter log_archive_dest_1;
   
   

SQL> show parameter log_archive_dest_2;​

1. 主备库查看连接的名称：SQL> show parameter fal;
   
   alter system set fal_server=’odb1’

alter system set fal_client=’odb2’​

1. 备库打开，确认备库接收日志SQL> alter database open;
   SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=odb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=odb1';
   
   SQL> alter system switch logfile;
   
2. 原主库启动恢复模式，并启动追加数据：SQL> alter database open;
   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   
   

附：取消日志应用SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ​

在open状态下执行日志即使运用SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;​

1. 现在的主数据库启动​SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;​
   
   
   
   
2. 确认数据库日志应用到备数据库​

现主库​

SQL> select database_role,switchover_status from v$database;​

现备库​

SQL> select database_role,switchover_status from v$database;​

附加主从切换验证：​

主从库状态查看：现主库为：READ WRITE/ 现从库为：READ ONLY WITH APPLY​

SQL> select open_mode from v$database;​

创建测试用户测试：​

SQL> create user aa22 identified by 123456;​

原主库因为改为备库而无法写入数据​

SQL> select * from all_users;​

主备同步状态查看：新主库略大于备库，并处于实时更新状态​

SQL> col current_scn for 9999999999999999​

SQL> select scn_to_timestamp(current_scn),current_scn from v$database;​

第3台切换主库​

cd $ORACLE_BASE​

cd diag/rdbms/orcl/trace​

less alert_oracle.log​

lqlplus / as sysdba​

show parameter fal;​

show parameter db_uni​