【吃瓜每日大赛】反差大赛：谁才是那个人设崩塌后的“人间瑰宝”？

环境配置

主机环境类型主机名IP主库p19c

192.

168.

2

150备库p19cstd

192.

168.

2

151这里选择做两个19c单机环境tip数据库服务名与主机名一致19c的安装可以参考以下教程教程是以p19c为例在安装p19cstd时需要将所有的p19c替换成p19cstdOracle 19c静默安装教程

host文件配置配置hostname# 主库 hostnamectl set-hostname p19c # 备库 hostnamectl set-hostname p19cstd配置hosts文件cat EOF/etc/hosts #Public IP

192.

168.

2

150 p19c

192.

168.

2

151 p19cstd EOF

主库配置

开启归档查看oracle归档状态archive log list关闭数据库并启动到mount状态shutdown immediate; startup mount;修改归档位置mkdir -p /u01/app/archive chown -R oracle:oinstall /u01/app/ # 在sql中执行修改归档位置 alter system set log_archive_dest_1location/u01/app/archive;启用归档模式和强制归档alter database archivelog; alter database force logging; alter database open;查看修改后的归档状态archive log list;切换在线日志验证归档是否正常alter system switch logfile; !ls /u01/app/archive

redo日志查询现有redo日志的数量select group#,type,member from v$logfile;查询现有redo日志的容量select group#,bytes/1024/1024 as MB,status from v$log;创建standby日志组容量要和redo相同数量是redo当前数量1mkdir -p /u01/app/oradata/P19C chown -R oracle:oinstall /u01/app/alter database add standby logfile group 6 (/u01/app/oradata/P19C/standby_redo

log) size 200m reuse, group 7 (/u01/app/oradata/P19C/standby_redo

log) size 200m reuse, group 8 (/u01/app/oradata/P19C/standby_redo

log) size 200m reuse, group 9 (/u01/app/oradata/P19C/standby_redo

log) size 200m reuse;确认状态select group#,status,used from v$standby_log;

传输口令文件和参数文件root用户执行chown oracle:oinstall /opt口令文件cd $ORACLE_HOME/dbs cp orapwp19c /opt/orapwp19cstd参数文件:sqlplus / as sysdba create pfile from spfile; exitcd $ORACLE_HOME/dbs vim initp19c.ora *.db_unique_namep19c *.log_archive_configdg_config(p19c,p19cstd) *.fal_clientp19c *.fal_serverp19cstd *.log_archive_dest_1LOCATION/u01/app/archive valid_for(all_logfiles,all_roles) db_unique_namep19c *.log_archive_dest_2servicep19cstd lgwr async valid_for(online_logfiles,primary_role) db_unique_namep19cstd *.log_archive_format%t_%s_%r.arc *.db_file_name_convert/u01/app/oradata/P19CSTD/,/u01/app/oradata/P19C/ *.log_file_name_convert/u01/app/oradata/P19CSTD/,/u01/app/oradata/P19C/ STANDBY_FILE_MANAGEMENTAUTO启用新的参数文件:备份之前的spfile# 如果报错可能是没有opt下的创建文件的权限给一下权限就好 create pfile/opt/spfile.bak from spfile;启用新的shutdown immediate; create spfile from pfile; startup;传输文件到备库:备库执行chown oracle:oinstall /opt# 这一步报错一般也是目标服务器中ORACLE用户没有在opt下创建文件的权限 scp /opt/orapwp19cstd oraclep19cstd:/opt scp $ORACLE_HOME/dbs/initp19c.ora oraclep19cstd:/opt/initp19cstd.ora

配置TNS文件cd $ORACLE_HOME/network/admin/netmgr,配置tns,监听vim tnsnames.oraLISTENER_P19C (ADDRESS (PROTOCOL TCP)(HOST p19c)(PORT

) P19C (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST p19c)(PORT

) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME p19c) ) ) P19CSTD (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST p19cstd)(PORT

) ) (CONNECT_DATA (SERVICE_NAME p19cstd) ) )

静态监听cd $ORACLE_HOME/network/admin vi listener.ora LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST p19c)(PORT

) (ADDRESS (PROTOCOL IPC)(KEY EXTPROC

) ) ) SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME p19c) (ORACLE_HOME /u01/app/oracle/product/

19.

3.

(SID_NAME p19c) ) )监听重载lsnrctl reload测试监听是否配资成功tnsping p19c tnsping p19cstd

备库配置

创建必要文件夹mkdir -p /u01/app/archive chown -R oracle:oinstall /u01/app/ mkdir -p /u01/app/oradata/P19CSTD chown -R oracle:oinstall /u01/app/ mkdir -p /u01/app/oracle/admin/p19cstd/adump

配置参数文件cd /opt cp orapwp19cstd initp19cstd.ora $ORACLE_HOME/dbs cd $ORACLE_HOME/dbs vim initp19cstd.ora *.audit_file_dest/u01/app/oracle/admin/p19cstd/adump *.control_files/u01/app/oradata/P19CSTD/control

ctl,/u01/app/oradata/P19CSTD/control

ctl *.db_unique_namep19cstd *.log_archive_configdg_config(p19cstd,p19c) *.fal_clientp19cstd *.fal_serverp19c *.log_archive_dest_1LOCATION/u01/app/archive valid_for(all_logfiles,all_roles) db_unique_namep19cstd *.log_archive_dest_2servicep19c lgwr async valid_for(online_logfiles,primary_role) db_unique_namep19c *.log_archive_format%t_%s_%r.arc *.db_file_name_convert/u01/app/oradata/P19C/,/u01/app/oradata/P19CSTD/ *.log_file_name_convert/u01/app/oradata/P19C/,/u01/app/oradata/P19CSTD/

启动备库到nomountexport ORACLE_SIDp19cstd sqlplus / as sysdba shutdown immediate create spfile from pfile; startup nomount; # 修改参数standby_file_management alter system set standby_file_managementMANUAL;

配置TNS文件cd $ORACLE_HOME/network/admin/tnetmgr,配置tns,监听vim tnsnames.ora# 文件内容如下 LISTENER_P19CSTD (ADDRESS (PROTOCOL TCP)(HOST p19cstd)(PORT

) P19CSTD (DESCRIPTION (ADDRESS_LIST (ADDRESS (PROTOCOL TCP)(HOST p19cstd)(PORT

) ) (CONNECT_DATA (SERVER DEDICATED) (SERVICE_NAME p19cstd) ) ) P19C (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST p19c)(PORT

) (CONNECT_DATA (SERVICE_NAME p19c) ) )

配置静态监听cd $ORACLE_HOME/network/admin vi listener.ora # 文件内容如下 LISTENER (DESCRIPTION_LIST (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST p19cstd)(PORT

) (ADDRESS (PROTOCOL IPC)(KEY EXTPROC

) ) ) SID_LIST_LISTENER (SID_LIST (SID_DESC (GLOBAL_DBNAME p19cstd) (ORACLE_HOME /u01/app/oracle/product/

19.

3.

(SID_NAME p19cstd) ) )监听重载lsnrctl reload测试监听是否配资成功tnsping p19c tnsping p19cstd

构建DG

验证监听主库sqlplus sys/123p19c as sysdba sqlplus sys/123p19cstd as sysdba备库sqlplus sys/123p19c as sysdba sqlplus sys/123p19cstd as sysdba

主库登入rman复制数据库至备库rman target sys/123p19c auxiliary sys/123p19cstd构建备库run { allocate channel ch001 type disk; allocate channel ch002 type disk; allocate channel ch003 type disk; allocate channel ch004 type disk; allocate auxiliary channel ch005 type disk; allocate auxiliary channel ch006 type disk; duplicate target database for standby from active database nofilenamecheck using compressed backupset; release channel ch001; release channel ch002; release channel ch003; release channel ch004; release channel ch005; release channel ch006; }

检验并开启实时同步备库执行# 查询备库状态是否已经mount select open_mode from v$database;# 打开备库 alter database open;检查文件select name from v$datafile; select name from v$tempfile; select group#,type,member from v$logfile order by 2,1;开启日志应用即开启实时同步:alter database recover managed standby database using current logfile disconnect;检查数据同步状态:通过判断transport lag和apply lag两个参数的DATUM_TIME值来判断备库是否在应用主库的日志文件如果多次查询DATUM_TIME值保持不变则说明备库已经停止从主库接收数据了select name,value,TIME_COMPUTED,DATUM_TIME from v$dataguard_stats;检查MRP进程状态:select process,status from v$managed_standby;查看数据库打开状态:READ ONLY WITH APPLY代表ADG启动成功select open_mode from v$database;到这里已经完成了ADG的搭建了

测试同步随便创建一个表在插入一些数据然后用备库查询看是否能查询到注意主库数据插入之后需要提交之后备库才能查看到备库进行查看同步成功

小红莓视频直播-小红莓视频直播应用