单机Physical Standby

在Oracle 10g下单机Physical Standby

操作系统: Windows
Oracle版本:10.2.0.1

主库SID: ALEX
从库SID: ALUOCP

一、创建过程:

1、在主库执行force logging

SQL> alter database force logging;

2、设置主库的参数文件和从库的参数文件:
主库确认以下参数:

db_name='alex'
DB_UNIQUE_NAME='alex'
control_files='D:\oracle\oradata\alex\control01.ctl','D:\oracle\oradata\alex\control02.ctl','D:\oracle\oradata\alex\control03.ctl'
FAL_CLIENT='alex'
FAL_SERVER='aluocp'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,aluocp)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\alex\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=alex'
LOG_ARCHIVE_DEST_2='SERVICE=aluocp LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=aluocp'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=5
STANDBY_ARCHIVE_DEST='D:\oracle\oradata\alex\standbyarchive'
STANDBY_FILE_MANAGEMENT='AUTO'
DB_FILE_NAME_CONVERT='D:\oracle\oradata\aluocp','D:\oracle\oradata\alex'
LOG_FILE_NAME_CONVERT='D:\oracle\oradata\aluocp\archive','D:\oracle\oradata\alex\archive'

从库确认以下参数:

db_name='alex'
DB_UNIQUE_NAME='aluocp'
control_files='D:\oracle\oradata\aluocp\control01.ctl','D:\oracle\oradata\aluocp\control02.ctl','D:\oracle\oradata\aluocp\control03.ctl'
FAL_CLIENT='aluocp'
FAL_SERVER='alex'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,aluocp)'
LOG_ARCHIVE_DEST_1='LOCATION=D:\oracle\oradata\aluocp\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=aluocp'
LOG_ARCHIVE_DEST_2='SERVICE=alex LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=alex'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=5
STANDBY_ARCHIVE_DEST='D:\oracle\oradata\aluocp\standbyarchive'
STANDBY_FILE_MANAGEMENT='AUTO'
DB_FILE_NAME_CONVERT='D:\oracle\oradata\alex','D:\oracle\oradata\aluocp'
LOG_FILE_NAME_CONVERT='D:\oracle\oradata\alex\archive','D:\oracle\oradata\aluocp\archive'

3、在主库开启archivelog和flashback:

SQL> create spfile from pfile;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;

4、在主库添加standby redo log:
考虑添加的公式:(maximum number of logfiles for each thread + 1) * maximum number of threads
standby redo log至少比online redo log多一组

SQL> alter database add standby LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\ALEX\REDO04.log') size 50m; 
SQL> alter database add standby LOGFILE GROUP 5 ('D:\ORACLE\ORADATA\ALEX\REDO05.log') size 50m; 
SQL> alter database add standby LOGFILE GROUP 6 ('D:\ORACLE\ORADATA\ALEX\REDO06.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:\ORACLE\ORADATA\ALEX\REDO07.log') size 50m;

5、Copy主库所有数据文件和redo log文件到从库目录

6、在主库Rename数据文件和日志文件:

                          
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\aluocp\SYSTEM01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\aluocp\UNDOTBS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\aluocp\SYSAUX01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\USERS01.DBF' to 'D:\ORACLE\ORADATA\aluocp\USERS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\TEMP01.DBF' to 'D:\ORACLE\ORADATA\aluocp\TEMP01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO01.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO01.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO02.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO02.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO03.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO03.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO04.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO04.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO05.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO05.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO06.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO06.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\ALEX\REDO07.LOG' to 'D:\ORACLE\ORADATA\aluocp\REDO07.LOG';

7、在主库生成standby control file:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\STANDBY.CTL';

8、在主库Rename数据文件和日志文件到原始:

SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\SYSTEM01.DBF' to 'D:\ORACLE\ORADATA\alex\SYSTEM01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\alex\UNDOTBS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\SYSAUX01.DBF' to 'D:\ORACLE\ORADATA\alex\SYSAUX01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\USERS01.DBF' to 'D:\ORACLE\ORADATA\alex\USERS01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\TEMP01.DBF' to 'D:\ORACLE\ORADATA\alex\TEMP01.DBF';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO01.LOG' to 'D:\ORACLE\ORADATA\alex\REDO01.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO02.LOG' to 'D:\ORACLE\ORADATA\alex\REDO02.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO03.LOG' to 'D:\ORACLE\ORADATA\alex\REDO03.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO04.LOG' to 'D:\ORACLE\ORADATA\alex\REDO04.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO05.LOG' to 'D:\ORACLE\ORADATA\alex\REDO05.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO06.LOG' to 'D:\ORACLE\ORADATA\alex\REDO06.LOG';
SQL> alter database rename file 'D:\ORACLE\ORADATA\aluocp\REDO07.LOG' to 'D:\ORACLE\ORADATA\alex\REDO07.LOG';

9、把STANDBY.CTL移至从库目录,重命名并做多功

10、创建从库的服务和密码文件:

cmd> oradim -new -sid ALUOCP -intpwd aluocp -startmode m

11、设置主库和从库的listener和tnsnames,并启动

12、启动从库,自动建立online redo log:

SQL> create spfile from pfile;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;

13、在主库切换一次日志,使备库中记录的原主库的standby redo log信息被清除:

SQL> alter system switch logfile;

稍等一会,等在主库中查询v$standby_log没有原来主库standby log的信息返回

14、在从库建立standby redo log

SQL> alter database recover managed standby database cancel;
SQL> alter database add standby LOGFILE GROUP 4 ('D:\ORACLE\ORADATA\ALUOCP\REDO04.log') size 50m; 
SQL> alter database add standby LOGFILE GROUP 5 ('D:\ORACLE\ORADATA\ALUOCP\REDO05.log') size 50m; 
SQL> alter database add standby LOGFILE GROUP 6 ('D:\ORACLE\ORADATA\ALUOCP\REDO06.log') size 50m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:\ORACLE\ORADATA\ALUOCP\REDO07.log') size 50m;
SQL> alter database recover managed standby database disconnect from session; 

15、检查主库和从库的状态:
检查数据库角色和状态

SQL> select database_role, switchover_status from v$database;

检查archive log的应用状态

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

检查archive log的遗失

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

检查archive log的路径是否可用

SQL> select dest_name,status,error from v$archive_dest;

二、测试过程

1、模式切换
1.1 启动standby database到recover manage模式

SQL>alter database recover managed standby database disconnect from session;

1.2 启动到实时日志应用模式

SQL>alter database recover managed standby database using current logfile;

(之前需要先创建standby logfile,使用命令,同时还需要修改主库的参数LOG_ARCHIVE_DEST_2= ‘SERVICE=ALUOCP LGWR’)

1.3 切换standby database到read only模式

SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;

1.4 切换回recover manage模式

SQL> alter database recover managed standby database disconnect from session;

2、Switchover
2.1 在主库上

SQL>select switchover_status from v$database

2.2 switchover_status的值如果是To standby,可以直接switchover:

SQL> alter database commit to switchover to physical standby;
SQL> shutdowm immediate;
SQL> startup mount;

如果是sessions active,则需要在switchover的命令后面加上with session shutdown:

SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdowm immediate;
SQL> startup mount;

2.3 在目标从库上

SQL> select switchover_status from v$database

switchover_status的值入股是To primary,可以直接switchover:

SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup;

如果是sessions active,则需要在switchover的命令后面加上with session shutdown:

SQL> alter database commit to switchover to primary with session shutdown;
SQL> shutdown immediate;
SQL> startup;

3、Failover
3.1 在目标从库上,检查遗失的archive log:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

3.2 如有遗失,Copy到从库目录并应用:

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

3.3 再次检查遗失的archive log:

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST FROM V$ARCHIVED_LOG;

3.4 关闭从库的recover模式:

SQL> alter database recover managed standby database finish;
OR
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
OR
SQL> alter database recover managed standby database finish skip standby logfile;

3.5 切换从库为主库:

SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup;

4、使用Flashback恢复failover的主库
4.1 在新的主库上查询切换时候的SCN号:

SQL> SELECT standby_became_primary_scn FROM v$database;

4.2 恢复旧主库后,Mount:

SQL> STARTUP MOUNT;

4.3 Flashback旧主库到步骤4.1查询得的SCN:

SQL> FLASHBACK DATABASE TO SCN ;

4.4 在旧主库上,转换control file为standby control file:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

4.5 恢复旧主库为新从库:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4.6 在新主库上,设置日志传输到旧主库(新从库),并确认状态:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE,DESTINATION, ERROR, SRL FROM V$ARCHIVE_DEST_STATUS;

4.7 在新主库上,归档日志到新从库:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

4.8 在新从库上开启recover模式:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

如果是real-time应用:

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

no comment untill now

Add your comment now

切换到手机版