主库:ecology1 10.1.1.95 Centos 7.9
ecology2 10.1.1.96 Centos 7.9
备库:ecologydg 192.168.16.3 Centos 7.9
1.首先,主库开启归档(两节点需要重启),force login
两节点:
SQL>shutdown immediate;
SQL>startup mount
SQL>alter database archivelog #开启归档
SQL>alter database force logging; #开启force login
SQL>alter system set log_archive_dest_1='location=+DATA' sid='*' scope=spfile;#设置本地归档
SQL>startup;
SQL> select log_mode, force_logging from v$database; #查看数据库模式
LOG_MODE FORCE_LOG
------------------------------------ ---------
ARCHIVELOG YES
2.主库配置dataguard 参数
SQL>alter system set log_archive_config='dg_config=(ecology,ecologydg)' sid='*';#打开dataguard配置
SQL>alter system set log_archive_dest_3='service=ecologydg valid_for=(online_logfiles,primary_role) db_unique_name=ecologydg' sid='*'; #设置远程归档
scp $ORACLE_HOME/dbs/orapw$ORACLE_SID 192.168.16.3:$ORACLE_HOME/dbs/orapwecologydg #拷贝主库密码文件至备库
3.备库配置dataguard参数。并创建相应目录
直接拷贝init.ora为initecologydg.ora 编辑
*.audit_file_dest='/u01/app/oracle/admin/ecologydg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ecologydg/controlfile/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ecology'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='ecologydg'
*.fal_server='ecology'
*.log_archive_config='dg_config=(ecology,ecologydg)'
*.log_archive_dest_2='location=/oracle/dataguard/ecologydg/archivelog/ valid_for=(standby_logfiles,standby_role) db_unique_name=ecologydg'
*.memory_target=4G
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'
standby_file_management=auto
db_unique_name='ecologydg'
db_file_name_convert='+DATA/ecology/datafile/','/oracle/dataguard/ecologydg/datafile/','+DATA/ecology/tempfile/','/u01/app/oracle/oradata/ecologydg/tempfile/'
log_file_name_convert='+DATA/ecology/onlinelog/','/oracle/dataguard/ecologydg/onlinelog/'
mkdir -p /u01/app/oracle/admin/ecologydg/adump
mkdir -p /u01/app/oracle/oradata/ecologydg/controlfile
mkdir -p /u01/app/oracle/oradata/ecologydg/tempfile
mkdir -p /oracle/dataguard/ecologydg/onlinelog
mkdir -p /oracle/dataguard/ecologydg/datafile
mkdir -p /oracle/dataguard/ecologydg/archivelog
4.主库配置tnsname,备库修改为静态监听。
主库两个节点都配置
vim $ORALCE_HOME/network/admin/tnsname.ora
ecologydg=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ecologydg)
)
)
备库
vim $ORALCE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ecologydg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = ecologydg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.16.3)(PORT = 1521))
)
lsnrctl status #状态为unknow 开启成功,静态监听的作用是在duplicate的时候备库状态为nomount依然可以连接。
5.duplicate 复制主库,并创建standby_log
sqlplus sys/password@ecologydg as sysdba #测试主库至备库的连通性。
登录rman ,开始duplicate,主库备库都可以执行
rman target / auxiliary sys/password@ecologydg log=/opt/dublicate230905_log append <<EOF
run {
duplicate target database for standby from active database;
}
EOF
mkdir -p /oracle/dataguard/ecologydg/standbylog/
#数量比节点一节点二加起来多两组
alter database add standby logfile thread 1 group 21 '/oracle/dataguard/ecologydg/standbylog/std01.log' size 50M;
alter database add standby logfile thread 1 group 22 '/oracle/dataguard/ecologydg/standbylog/std02.log' size 50M;
alter database add standby logfile thread 1 group 23 '/oracle/dataguard/ecologydg/standbylog/std03.log' size 50M;
alter database add standby logfile thread 2 group 24 '/oracle/dataguard/ecologydg/standbylog/std04.log' size 50M;
alter database add standby logfile thread 2 group 25 '/oracle/dataguard/ecologydg/standbylog/std05.log' size 50M;
alter database add standby logfile thread 2 group 26 '/oracle/dataguard/ecologydg/standbylog/std06.log' size 50M;
6.备库应用日志
alter database recover managed standby database disconnect from session; #先同步日志
alter database recover managed standby database cancel; #取消应用
alter database open; #起库
alter database recover managed standby database using current logfile disconnect from session;
#开启实时应用
---查看dg信息
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
---查看进程状态
select process,client_process,sequence#,status,THREAD# from v$managed_standby;
#redo情况
select thread#,max(sequence#) from v$log group by thread# order by 1;
#日志应用情况
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
备库切换主库下次再写