软件安装
主库安装数据库,备库只安装软件
修改db_unique_name参数
- 主库db_unique_name: primary
- 备库db_unique_name: standby
-- 查看现在的db_unique_name
show parameter db_unique_name; -- orcl
-- 在线修改db_unique_name(分别修改主库和备库)
1. alter system set db_unique_name='primary' scope=spfile;
2. alter system set db_unique_name='standby' scope=spfile;
-- 重新启动数据库可见
主库配置
开启归档模式与force logging flashback
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
alter database flashback on; -- 开启闪回
为备用库增加日志组
set wrap off;
select * from v$logfile order by group#;
alter database add standby logfile group 4 ('D:\ORADATA\ORCL\REDO04.LOG') size 50m;
alter database add standby logfile group 5 ('D:\ORADATA\ORCL\REDO05.LOG') size 50m;
alter database add standby logfile group 6 ('D:\ORADATA\ORCL\REDO06.LOG') size 50m;
alter database add standby logfile group 7 ('D:\ORADATA\ORCL\REDO07.LOG') size 50m;
修改系统参数spfile文件(13个参数)
前面已经修改了db_unique_name
1. alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;
-- 需要确认目录存在D:\ORADATA\flash\orcl\archivelog\
2. alter system set log_archive_dest_1='location=D:\ORADATA\flash\orcl\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile;
3. alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;
4. alter system set log_archive_dest_state_1='enable' scope=spfile;
5. alter system set log_archive_dest_state_2='enable' scope=spfile;
6. alter system set fal_server='standby' scope=spfile;
7. alter system set fal_client='primary' scope=spfile;
8. alter system set archive_lag_target=1800 scope=spfile;
9. alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
10. alter system set standby_file_management=auto scope=spfile;
11. alter system set db_file_name_convert='standby','primary' scope=spfile;
12. alter system set log_file_name_convert='standby','primary' scope=spfile;
创建密码文件
如无需手工创建密码文件
orapwd file=D:\app\admin\product\11.2.0\dbhome_1\database\PWDorcl.ora password=123456 entries=10;
配置主库网络监听(备用库同理)
- listener.ora (D:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora)
# 以下为主库完整的listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\app\admin\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.122)(PORT = 1521))
)
ADR_BASE_LISTENER = D:\app\admin
- tnsnames.ora (D:\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora)
# 增加primary 和 standby配置
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.122)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.123)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
- 重启监听,可能出现无权限启动监听,需要使用管理员权限
参考文档:https://blog.csdn.net/breaker892902/article/details/17607479
也可以考虑在c:\windows\system32\cmd.exe文件直接右键以管理员身份执行,再cmd,进入普通用户的cmd
-- 重启监听
lsnrctl stop
lsnrctl start
-- 重启数据库,注意本次启动db_unique_name=standby时会出现报错
shutdown immediate
startup
-- 再测试监听
tnsping primary
手工删除数据库
--关闭监听
lsnrctl stop
-- 关闭数据库,开启受限模式,删除数据库
shutdown immediate;
startup mount;
alter system enable restricted session;
drop database;
-- 删除实例名
oradim -delete -sid orcl
- 可使用下列命令得到需要删除的文件
SET SERVEROUTPUT ON;
DECLARE
TYPE string_arr IS TABLE OF VARCHAR2(1024);
file_list string_arr;
BEGIN
SELECT t.file_path BULK COLLECT
INTO file_list
FROM (SELECT NAME file_path
FROM V$DATAFILE
UNION
SELECT MEMBER file_path
FROM V$LOGFILE
UNION
SELECT NAME file_path
FROM v$controlfile
UNION
SELECT VALUE file_path
FROM v$parameter
WHERE NAME LIKE '%dest'
UNION
SELECT VALUE file_path
FROM v$parameter2
WHERE NAME = 'utl_file_dir'
UNION
SELECT '$ORACLE_BASE/admin/$ORACLE_SID' file_path
FROM dual
) t;
FOR i IN file_list.FIRST .. file_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('rm -f ' || file_list(i));
END LOOP;
END;
/
复制文件到备库
方案1
- 复制d:\app\admin下的admin,cfgtoollogst,diag和d:\oradata\flash到备库相同路径
- 复制主库的listener.ora和tnsname.ora到备库相同路径(注意修改listener.ora中的ip)
- 在备机建立文件夹存放rmn文件
- 复制主库创建的pfile参数文件initorcl.ora到备库
- 修改相应参数,详见参考文档1,要修改的内容见#备注
方案2
手工创所需目录
mkdir -p D:\app\admin\admin\orcl\adump
mkdir -p D:\app\admin\admin\orcl\dbdump
mkdir -p D:\app\admin\admin\orcl\pfile
mkdir -p D:\app\admin\flash_recovery_area\orcl
mkdir -p D:\ORADATA\orcl
mkdir -p D:\ORADATA\flash\orcl\ARCHIVELOG
配置文件清单
orcl.__db_cache_size=6744440832
orcl.__java_pool_size=33554432
orcl.__large_pool_size=33554432
orcl.__oracle_base='D:\app\admin'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=5502926848
orcl.__sga_target=8220835840
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1308622848
orcl.__streams_pool_size=0
*.archive_lag_target=1800
*.audit_file_dest='D:\app\admin\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\ORADATA\orcl\control01.ctl','D:\app\admin\flash_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
#db_file_name_convert 修改为'primary','standby'
*.db_file_name_convert='primary','standby'
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\admin\flash_recovery_area'
*.db_recovery_file_dest_size=4102029312
#db_unique_name 修改为 'standby'
*.db_unique_name='standby'
*.diagnostic_dest='D:\app\admin'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
#fal_client 修改为 'standby'
*.fal_client='standby'
#fal_server 修改为 'primary'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
#db_unique_name 修改为 standby
*.log_archive_dest_1='location=D:\ORADATA\flash\orcl\archivelog\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
#db_unique_name 修改为 primary
*.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
#log_file_name_convert 修改为 'primary','standby'
*.log_file_name_convert='primary','standby'
*.memory_target=13697548288
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
备机新建实例并启动为nomount
-- 如果有即删除该sid
oradim -delete -sid orcl
-- 创建新实例
oradim -new -sid orcl
-- 启动监听
lsnrctl start
-- 启动数据库到nomount模式(使用主机的pfile)
sqlplus /nolog
> conn sys/123456 as sysdba;
> startup nomount pfile='D:\app\admin\product\11.2.0\dbhome_1\database\INITorcl.ORA'
主库使用RMAN备份
rman target /
backup full database format='d:\oradata\tmp\FOR_STANDBY_%u%p%s.RMN' include current controlfile for standby;
-- 可不执行
sql'alter system archive log current';
- 将本次rman备份文件复制到备库相同路径
- 复制数据库
connect auxiliary sys/123456@standby
-- ps: connect有可能失败,要能连到(未加载的数据库)
-- 可能需要修改主库的listener.ora文件,去掉以下几行:
# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
# (PROGRAM = extproc)
# (ENVS = "EXTPROC_DLLS=ONLY:D:\app\admin\product\11.2.0\dbhome_1\bin\oraclr11.dll")
-- 再执行下面这句复制数据库的命令
-- 方案1 采用
duplicate target database for standby nofilenamecheck;
-- 方案2 采用
duplicate target database for standby from active database nofilenamecheck;
备库启动standby
sqlplus / as sysdba
---------mode 1 ===========
> startup nomount;
> alter database mount standby database;
---------mode 2 ===========
> startup mount;
> alter database open read only;
---------step 2 ===========
> alter database recover managed standby database disconnect from session;
---- 另一种方式
> alter database recover managed standby database using current logfile disconnect from session;
至此data guard 搭建完成
验证是否搭建成功
- 查看日志祖是否相同,若主备库sequence#一致,表示已经同步
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;
- 在主库切换活动日志,后查看日志同步情况
alter system switch logfile;
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;
- 检验主备切换是否正常
select switchover_status from v$database;
-- 结果为 TO STANDBY 表示可以切换
- 主库切到physical standby
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
-- startup nomount;
-- alter database mount standby database;
startup mount;
alter database open read only;
alter database recover managed standby database disconnect from session;
- 备库切到primary
alter database commit to switchover to primary;
shutdown immediate;
startup;
alter system switch logfile;
- 在新主库和新备库查看日志组在切换后sequence#是否相同
select name,sequence#,applied,completion_time from v$archived_log order by completion_time desc, sequence# desc;
alter system switch logfile;
fail over 步骤
- 模拟主库宕机 shutdown abort
- 备库取消recover managed,切换为主库
--- 在备库执行,待测试
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
--- 以下步骤经过测试
shutdown immediate;
startup mount;
alter database open;
备用命令
-- 必须是一个字符串,用双引号括起来
delete archivelog all completed before 'sysdate-7';
delete archivelog all completed before "to_date('20200820163000','yyyymmddhh24miss')";
参考文档
扫一扫
1487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
