怎样给不同的柱子上添加不同的标准误差线(怎么给柱形图加误差线)
792
2022-05-30
参考自:Data Guard Physical Standby Setup in Oracle Database 11g Release 2
MOS文档:Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE (Doc ID 1075908.1)
官方文档:Duplicating Databases
一、Active Database Duplicate步骤(Using Image Copies)
1.配置主库DG参数,备库根据主库的PFILE,设置参数值,生成备库SPFILE. 2.配置hosts文件,配置TNS,配置静态监听,添加standby log文件. 3.拷贝主库的密码文件至备库,备库创建PFILE中不存在的目录. 4.把备库启动到nomount状态. 5.RMAN同时连接主库与备库,执行duplicate命令.
注:由于Active Database Duplicate无需提前备份,而是通过网络在线copy数据库文件,对主库的CPU等负载要求较高,因此最好在空闲时间进行操作,对于TB级别的数据库,使用Active Duplicate进行DG搭建效率较高,节省空间,但是对网络要求较高;源库必须使用SPFILE。
二、环境准备
Notes:
1、db_unique_name主备库不能相同。
2、db_name主备库需保持一致。
3、主备库DB版本需保持一致。
三、搭建过程
1、Oracle软件安装
主库一键安装:
./AllOracleSilent.sh -i 192.168.56.120 -d 11g -n orcl -o orcl -b /u01/app -s AL32UTF8
备库一键安装:(备库仅安装ORACLE软件,不建库)
./AllOracleSilent.sh -i 192.168.56.121 -d 11g -w Y -n orcl_stby -o orcl -b /u01/app -s AL32UTF8
一键安装脚本可参考:ORACLE一键安装单机11G/12C/18C/19C并建库脚本
2、环境配置
a.配置hosts文件
主库:
cat <
备库:
cat <
b.配置静态监听和TNS
主库+备库:
Notes:注意这里的GLOBAL_DBNAME和service_name保持一致,即备库需要改为 orcl_stby。
##listener.ora su - oracle -c "cat <
c.主库配置参数
select log_mode,force_logging from gv$database; LOG_MODE FOR ------------ --- NOARCHIVELOG NO --开启归档模式 shutdown immediate startup mount alter database archivelog; alter database open; --开启强制日志模式 alter database force logging; --查看数据文件路径是否一致,OMF参数建议关闭 select name from v$datafile; show parameter db_create_file_dest alter system reset db_create_file_dest; --NOTES:如果数据文件路径不一致,duplicate将失败。 --设置DG参数 ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER; ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; ALTER SYSTEM SET FAL_SERVER=ORCL_STBY; ALTER SYSTEM SET FAL_CLIENT=ORCL; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
d.生成备库pfile文件并修改,复制参数文件和密码文件至备库
create pfile='/tmp/initorcl_stby.ora' from spfile; --修改的部分: *.db_unique_name=orcl_stby *.fal_client='ORCL_STBY' *.fal_server='ORCL' *.log_archive_config='DG_CONFIG=(ORCL_STBY,ORCL)' *.log_archive_dest_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL_STBY' *.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL' --复制参数文件至备库(备库执行) scp oracle@orcl:/tmp/initorcl_stby.ora /tmp --复制密码文件至备库(备库执行),要在oracle用户下复制 su - oracle scp oracle@orcl:/u01/app/oracle/product/11.2.0/db/dbs/orapworcl /u01/app/oracle/product/11.2.0/db/dbs
e.主库添加stanby log文件
set line222 col member for a60 select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from gv$logfile t1,gv$log t2 where t1.group#=t2.group#; THREAD# GROUP# MEMBER T2.BYTES/1024/1024 ---------- ---------- ------------------------------------------------------------ ------------------ 1 3 /oradata/orcl/redo03.log 120 1 2 /oradata/orcl/redo02.log 120 1 1 /oradata/orcl/redo01.log 120 --需要注意: --1.stanby log日志大小与redo log日志保持一致 --2.stanby log数量: standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile. --3.thread要与redo log保持一致,如果是rac,需要增加多个thread对应的standby log ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 4 ('/oradata/orcl/standby_redo04.log') SIZE 120M, group 5 ('/oradata/orcl/standby_redo05.log') SIZE 120M, group 6 ('/oradata/orcl/standby_redo06.log') SIZE 120M, group 7 ('/oradata/orcl/standby_redo07.log') SIZE 120M;
f.备库创建db目录,开启到nomount状态
su - oracle -c "mkdir -p /oradata/orcl" su - oracle -c "mkdir -p /u01/app/oracle/fast_recovery_area/orcl" su - oracle -c "mkdir -p /u01/app/oracle/admin/orcl/adump" sqlplus / as sysdba create spfile from pfile='/tmp/initorcl_stby.ora'; startup nomount
3、 RMAN DUPLICATE
rman target sys/oracle@orcl AUXILIARY sys/oracle@orcl_stby run { allocate channel prmy1 type disk; allocate channel prmy2 type disk; allocate auxiliary channel aux1 type disk; allocate auxiliary channel aux2 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; }
4、备库开启日志应用
alter database open read only; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY SQL> SELECT protection_mode FROM v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE
5、主库开启LOG_ARCHIVE_DEST_STATE_2
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
6、测试同步情况
set line222 col member for a60 --查看是否存在RFS和MRP进程 select process,group#,thread#,sequence# from gv$managed_standby; --查看standby日志status是否存在active select t1.group#,t1.thread#,t1.bytes/1024/1024,t1.status,t2.member from gv$standby_log t1,gv$logfile t2 where t1.group#=t2.group#; --主库建表空间,建用户,建表,增删改测试 create tablespace TEST datafile '/oradata/orcl/test01.dbf' size 100M autoextend off; create user test identified by test; grant dba to test; conn test/test create table test(id number); insert into test values (1); insert into test values (2); commit; --备库查看是否同步 SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEST'; TABLESPACE_NAME ------------------------------ TEST SQL> select username,account_status,created from dba_users where username='TEST'; USERNAME ACCOUNT_STATUS CREATED ------------------------------ -------------------------------- ------------------ TEST OPEN 17-APR-21 SQL> select * from test.test; ID ---------- 1 2
Image
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。