一、主库操作
1、修改主库属性：
alter system force logging;
##查看状态
select FORCE_LOGGING from v$database;
2、修改数据库为归档模式：
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;(alter database noarchivelog;关掉归档模式)
archive log list;
3、添加standby logfile(也可以不加)
为主数据库添加"备用联机日志文件"，这里要保证备日志文件与主库联机日志文件相同大小。
添加备用日志文件是规则：
备用日志最少应该比redo log 多一个。推荐的备重做日志数依赖于主数据库上的线程数。
(每线程日志文件最大数目 + 1 ) * 线程数
alter database add standby logfile
group 4 ('/oracle2/app/oracle/oradata/std_redo04a.log','/oracle2/app/oracle/oradata/std_redo04b.log') size 50m,
group 5 ('/oracle2/app/oracle/oradata/std_redo05a.log','/oracle2/app/oracle/oradata/std_redo05b.log') size 50m,
group 6 ('/oracle2/app/oracle/oradata/std_redo06a.log','/oracle2/app/oracle/oradata/std_redo06b.log') size 50m,
group 7 ('/oracle2/app/oracle/oradata/std_redo07a.log','/oracle2/app/oracle/oradata/std_redo08b.dbf') size 50m;
否则备库在应用时报如下信息：
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/oracle2/arch/1_30_633287861.dbf'
在主库添加完standby logfile后，当主库切换后备库后会自动使用备库的redo logfile，具体应用信息如下：
RFS[1]: Successfully opened standby log 4: '/oracle2/app/oracle/oradata/10g/redo04.log'
RFS[1]: Successfully opened standby log 4: '/oracle2/app/oracle/oradata/10g/redo04.log'
4、修改主库参数文件：
10g.__db_cache_size=1207959552
10g.__java_pool_size=16777216
10g.__large_pool_size=16777216
10g.__shared_pool_size=352321536
10g.__streams_pool_size=0
*.audit_file_dest='/oracle2/app/oracle/admin/10g/adump'
*.background_dump_dest='/oracle2/app/oracle/admin/10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle2/app/oracle/oradata/10g/control01.ctl','/oracle2/app/oracle/oradata/10g/control02.ctl','/oracle2/app/oracle/oradata/10g/control03.ctl'
*.core_dump_dest='/oracle2/app/oracle/admin/10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='10g'
*.DB_UNIQUE_NAME='10gpri' [color=Red]###必须 定义每个数据库的唯一标识[/color]
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' [color=Red]###必须[/color]
*.log_archive_dest_1='location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='10gpri' [color=Red]###必须 本地的归档路径[/color]
*.LOG_ARCHIVE_DEST_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby' [color=Red]###必须(远程服务器端的归档日志)[/color]
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gpri [color=Red] ### 定义FAL服务器的Oracle Net服务的名称[/color]*.FAL_CLIENT=10gstandby [color=Red]### 定义备数据库的Oracle Net服务名 （这两个参数在主库可有可无，但备库必须有。ORACLE 老外工程师说这个必须有^_^） [/color]
*.db_recovery_file_dest='/oracle2/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1707081728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle2/app/oracle/admin/10g/udump'
*.STANDBY_FILE_MANAGEMENT=AUTO [color=Red] ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行.[/color]
5、用pfile启动，再重新创建spfile.
shutdown immediate;
startup pfile='./pfile.pra';
create spfile from pfile='./pfile.ora';
shutdown immediate;
startup;
6、在主库创建密码文件、以及控制文件。
orapwd file=orapw10gstandby.ora password=change_on_install entries=10
=======================================================
orapwd file=orapworcl password=system entries=10
orapwd file=orapworclpri password=system entries=10
orapwd file=orapworclstan password=system entries=10
密码文件目录$ORACLE_HOME/dbs
=======================================================
alter database create standby database controlfile '/tmp/standby.ctl';
7、TNS信息如下：
主库
10g=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gpri=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.224)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
==========================================
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(SID = orcl)
)
)
orclpri=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpri)
(SID = orcl)
)
)
orclstan=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstan)
(SID = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
==========================================
8、对主库进行全库备份
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup database format '/tmp/full_%s';
release channel t1;
release channel t2;
}
二、备库操作如下：
1、依照主库的数据文件位置，在备库上创建相应的目录结构（最好与主库一致）；
mkdir -p /oracle/app/oracle/oradata/....
2、通过FTP把在主库创建的密码文件、standby controlfile、full backup database文件到备库主机上。
3、备份的参数文件内容：
10g.__db_cache_size=1207959552
10g.__java_pool_size=16777216
10g.__large_pool_size=16777216
10g.__shared_pool_size=352321536
10g.__streams_pool_size=0
*.audit_file_dest='/oracle2/app/oracle/admin/10g/adump'
*.background_dump_dest='/oracle2/app/oracle/admin/10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle2/app/oracle/oradata/10g/control01.ctl','/oracle2/app/oracle/oradata/10g/control02.ctl','/oracle2/app/oracle/oradata/10g/control03.ctl'
*.core_dump_dest='/oracle2/app/oracle/admin/10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='10g'
[color=Lime]*.DB_UNIQUE_NAME='10gstandby' ##
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ##[/color]
[color=Lime]*.log_archive_dest_1='location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='10gstandby' ##
*.LOG_ARCHIVE_DEST_2='SERVICE=10gpri arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri' ##[/color]
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
[color=Lime]*.FAL_SERVER=10gstandby ##
*.FAL_CLIENT=10gpri ##[/color]*.db_recovery_file_dest='/oracle2/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1707081728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle2/app/oracle/admin/10g/udump'
*.STANDBY_FILE_MANAGEMENT=AUTO ##
4、分别对备库进行全库恢复，并启动到standby database mount状态下。
用PFILE文件起动到nomount状态下，恢复控制文件;起动到mount状态下，然后再恢复全库。
5、TNS信息如下：
10g=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gpri=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.224)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
10gstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.169.1.204)(PORT = 1921))
)
(CONNECT_DATA =
(SID = 10g)
)
)
=========================================================
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(SID = orcl)
)
)
orclpri=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpri)
(SID = orcl)
)
)
orclstan=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DG2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclstan)
(SID = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
====================================================
用TNSPING 对方是否通。
6、修改备库处于应用归档状态
alter database recover managed standby database disconnect from session;
如果主库从不过来归档，可以通过在主库侧手工修改参数如下：
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY;
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
7、测试
通过在主库执行alter system switch logfile;切换日志可以观察到备库会自动应用通过主库传过来的日志。
三、切换测试
1、在主库端
select switchover_stats from v$database;
如果是to 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;
2、在备库
在备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
如果是to_primary 表可以正常切换.
执行:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
否则执行: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
shutdown immediate;
startup;
然后观察主备库日志,如果正常的话会看到备库会自动应用日志.
====================================================================
物理dataguard 正常切换 脚色转换，switchover_status 状态改变：
正常切换
切换前：
主库：
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;
COUNT(*)
----------
1
在切换前，杀掉所有的数据库连接 观察SWITCHOVER_STATUS，如果是 TO STANDBY，则可以直接切换
如果SESSIONS ACTIVE ，则用
备用库 ：
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
说明：
主库需要注意事项
A 如果switchover_status为TO_STANDBY说明可以转换
直接转换
alter database commit to switchover to physical standby;
B 如果switchover_status为SESSIONS ACTIVE 则关闭会话
SQL>alter database commit to switchover to physical standby with session shutdown;
在备库中操作，查看备库
SQL> select switchover_status from v$database;
A 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库
SQL>alter database commit to switchover to primary
B 如果switchover_status为SESSION ACTIVE 就应该断开活动会话
SQL>alter database commit to switchover to primary with session shutdown;
C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到，此时不能
执行转换。
切换中 ：
备用库
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE
切换后备用的状态，模式：
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
2. 检查环境：
确认主库和从库间网络连接通畅；
确认没有活动的会话连接在数据库中；
PRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；
确保STANDBY数据库处于ARCHIVELOG模式；
如果设置了REDO应用的延迟，那么将这个设置去掉；
确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。
如果是最大保护模式，先变成最大性能模式：
3. 切换的顺序： 先从主库到备用，再从备库到主库
主切备：
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY
SQL>
SQL>
SQL> alter database commit to switchover to physical standby ;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY
备切主
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> SQL> SQL>
SQL>
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
Database mounted.
Database opened.
修改主 备库 的 tnsnames.ora
主库SESSIONS ACTIVE是正常primary状态下的结果。
DataGuard:Physical Standby Failover
接physical standby switchover
primary db :db2
standby db :db1
一般情况下failover都是表示primary数据库瘫痪，最起码也是起不来了，因此这种类型的切换基本上不需要primary数据库做什么操作。所以下列步骤中如果有提到primary和standby执行的，只是建议你如果primary还可以用，那就执行一下,即使不去执行，对failover来说也没有关系
1.检查归档文件是否连续，是否有gap
在standby库执行
SQL> select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;
2.如果步骤1查询出来纪录，则在primary库上执行，否则跳过此步骤
在主库上执行语句，按步骤1查询出来的纪录找出归档文件
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN XX AND XX;
--如果primary存在，拷贝相应的归档到STANDBY数据库，并注册.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'xxx';
3.检查归档文件是否完整
分别在primary/standby执行下列语句：
SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
把相差的归档复制到待转换的standby服务器,并手工register
4.开始做failover
察看standby进程状态
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 30 CLOSING
ARCH ARCH 0 CONNECTED
RFS LGWR 31 IDLE
RFS ARCH 0 IDLE
RFS N/A 0 IDLE
MRP0 N/A 31 APPLYING_LOG
SQL> alter database recover managed standby database finish force ;
FORCE关键字将会停止当前活动的RFS进程，以便立刻执行failover。
或
SQL> alter database recover managed standby database finish skip standby logfile;
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- ----------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
Failover切换成功
===========================================
常用命令
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
alter system switch logfile;
select switchover_status from v$database;
select dest_name,status,error from v$archive_dest;
select DATABASE_ROLE from v$database;
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
alter database commit to switchover to physical standby with session shutdown;