ORACLE 10G DATAGUARD实战步骤
具体步骤如下：
一、主库操作
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' ###必须 定义每个数据库的唯一标识
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ###必须
*.log_archive_dest_1='location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='10gpri' ###必须 本地的归档路径
*.LOG_ARCHIVE_DEST_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby' ###必须(远程服务器端的归档日志)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gpri ### 定义FAL服务器的Oracle Net服务的名称
一、主库操作
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' ###必须 定义每个数据库的唯一标识
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ###必须
*.log_archive_dest_1='location=/oracle2/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='10gpri' ###必须 本地的归档路径
*.LOG_ARCHIVE_DEST_2='SERVICE=10gstandby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby' ###必须(远程服务器端的归档日志)
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gpri ### 定义FAL服务器的Oracle Net服务的名称
*.FAL_CLIENT=10gstandby ### 定义备数据库的Oracle Net服务名（这两个参数在主库可有可无，但备库必须有。ORACLE 老外工程师说这个必须有^_^）
*.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 ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行.
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
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)
)
)
8、对主库进行全库备份
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup database format '/tmp/full_%s';
release channel t1;
release channel t2;
}
*.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 ###设置为AUTO,使得当数据文件添加到主数据库或者从主数据库删除的时候,对应的修改能够在备用数据库中自动执行.
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
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)
)
)
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'
*.DB_UNIQUE_NAME='10gstandby' ##
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ##
*.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' ##
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gstandby ##
*.FAL_CLIENT=10gpri ##
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'
*.DB_UNIQUE_NAME='10gstandby' ##
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' ##
*.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' ##
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=10gstandby ##
*.FAL_CLIENT=10gpri ##
*.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)
)
)
用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;
然后观察主备库日志,如果正常的话会看到备库会自动应用日志.
*.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)
)
)
用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;
然后观察主备库日志,如果正常的话会看到备库会自动应用日志.
