ITPub博客

首页 > 数据库 > Oracle > 创建测试物理Standby日志

创建测试物理Standby日志

原创 Oracle 作者:aluocp 时间:2008-09-18 15:42:02 0 删除 编辑

操作系统: Windows
Oracle版本:10.2.0.1

主库SID: ALEX (192.168.217.1)
从库SID: MYDG (192.168.217.201)


一、创建过程:

1、在主库执行force logging
SQL> alter database force logging;


2、设置主库的参数文件和从库的参数文件:
主库确认以下参数:
--主库必要
db_name='alex'
DB_UNIQUE_NAME='alex'
SERVICE_NAMES='alex'
INSTANCE_NAME='alex'
control_files='D:/oracle/oradata/alex/control01.ctl','D:/oracle/oradata/alex/control02.ctl','D:/oracle/oradata/alex/control03.ctl'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,mydg)'
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=MYDG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mydg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
--主库备库可互换
FAL_CLIENT='alex'
FAL_SERVER='mydg'
STANDBY_FILE_MANAGEMENT='AUTO'
STANDBY_ARCHIVE_DEST='D:/oracle/oradata/alex/standbyarchive'
--主备库不同路径选要
--DB_FILE_NAME_CONVERT='D:/oracle/oradata/mydg','D:/oracle/oradata/alex'
--LOG_FILE_NAME_CONVERT='D:/oracle/oradata/mydg/archive','D:/oracle/oradata/alex/archive'

[@more@]

从库确认以下参数:
--备库必要
db_name='alex'
DB_UNIQUE_NAME='mydg'
SERVICE_NAMES='mydg'
INSTANCE_NAME='mydg'
control_files='D:/oracle/oradata/alex/control01.ctl','D:/oracle/oradata/alex/control02.ctl','D:/oracle/oradata/alex/control03.ctl'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(alex,mydg)'
LOG_ARCHIVE_DEST_1='LOCATION=D:/oracle/oradata/alex/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mydg'
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='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_CLIENT='mydg'
FAL_SERVER='alex'
STANDBY_FILE_MANAGEMENT='AUTO'
STANDBY_ARCHIVE_DEST='D:/oracle/oradata/alex/standbyarchive'
--主备库不同路径选要
--DB_FILE_NAME_CONVERT='D:/oracle/oradata/alex','D:/oracle/oradata/mydg'
--LOG_FILE_NAME_CONVERT='D:/oracle/oradata/alex/archive','D:/oracle/oradata/mydg/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 20m;
SQL> alter database add standby LOGFILE GROUP 5 ('D:/ORACLE/ORADATA/ALEX/REDO05.log') size 20m;
SQL> alter database add standby LOGFILE GROUP 6 ('D:/ORACLE/ORADATA/ALEX/REDO06.log') size 20m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:/ORACLE/ORADATA/ALEX/REDO07.log') size 20m;


5、RMAN备份主库并将文件转到备机:
rman target /
RMAN> backup device type disk format 'd:/full_%U' database plus archivelog;
RMAN> backup device type disk format 'd:/ctrl_%U' current controlfile for standby;


6、配置主库和备库的的listener.ora和tnsnames.ora:
--主库listener.ora的配置
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = alex)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(SID_NAME = alex)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.1)(PORT = 1521))
)
)

--主库tnsnames.ora的配置
ALEX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = alex)
)
)

MYDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = alex)
)
)

LISTENER_ALEX =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.1)(PORT = 1521))

--备库listener.ora的配置
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = alex)
(ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
(SID_NAME = mydg)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.201)(PORT = 1521))
)
)

--备库tnsnames.ora的配置
ALEX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = alex)
)
)

MYDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydg)
)
)

LISTENER_MYDG =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.201)(PORT = 1521))


7、创建从库的服务和密码文件:
cmd> oradim -new -sid MYDG -intpwd alex -startmode m


8、在主机上用RMAN恢复从库
$ rman target /
RMAN> connect auxiliary sys/alex@mydg
RMAN> duplicate target database for standby nofilenamecheck;


9、在从库执行:
SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01100: database already mounted

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


10、在主库切换一次日志,使备库中记录的原主库的standby redo log信息被清除:
SQL> alter system switch logfile;
稍等一会,等在主库中查询v$standby_log没有原来主库standby log的信息返回


11、在从库建立standby redo log
SQL> alter database recover managed standby database cancel;
SQL> alter database add standby LOGFILE GROUP 4 ('D:/ORACLE/ORADATA/ALEX/REDO04.log') size 20m;
SQL> alter database add standby LOGFILE GROUP 5 ('D:/ORACLE/ORADATA/ALEX/REDO05.log') size 20m;
SQL> alter database add standby LOGFILE GROUP 6 ('D:/ORACLE/ORADATA/ALEX/REDO06.log') size 20m;
SQL> alter database add standby LOGFILE GROUP 7 ('D:/ORACLE/ORADATA/ALEX/REDO07.log') size 20m;
SQL> alter database recover managed standby database disconnect from session;


12、检查主库和从库的状态:
检查数据库角色和状态
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=MYDG 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;

追加flashback打开Dataguard:

操作前:
备库:
alter database recover managed standby database cancel;
create restore point restore_point_test guarantee flashback database;
主库:
alter system archive log current;
alter system set log_archive_dest_state_3=defer;
备库:
alter database activate standby database;
alter database open;

操作后:
备库:
startup mount force;
flashback database to restore point restore_point_test;
alter database convert to physical standby;
startup mount force;
alter database recover managed standby database disconnect from session;
主库:
alter system set log_archive_dest_state_3=enable;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7319461/viewspace-1010797/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle开发路线
请登录后发表评论 登录
全部评论

注册时间:2007-12-04

  • 博文量
    59
  • 访问量
    813507