Oracle 12C DG 搭建(RAC-RAC/RAC-单机）

2019-11-09 13:33:27

字体：大中小

来源：转载

供稿：网友

主库上操作1.开启RAC的 force loggingSQL> alter database force logging;SQL> 2.修改RAC初始化参数文件SQL> alter system set log_archive_config='DG_CONFIG=(eisoo,eisoos)';SQL> alter system set log_archive_dest_2='SERVICE=eisoos ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoos' scope=spfile;SQL> alter system set log_archive_dest_state_1=ENABLE;SQL> alter system set log_archive_dest_state_2=ENABLE;SQL> alter system set fal_server=eisoos;SQL> alter system set db_file_name_convert='eisoos','eisoo' scope=spfile;SQL> alter system set db_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/DATAFILE' scope=spfile; //如果备库没有使用asmSQL> alter system set log_file_name_convert='eisoos','eisoo' scope=spfile;SQL> alter system set log_file_name_convert='/data/oradata/eisoos','+DATA/EISOO/ONLINELOG' scope=spfile;//如果备库没有使用asmSQL> alter system set standby_file_management='AUTO';SQL> alter system set log_archive_max_processes=30;3.开启归档模式srvctl stop database -d eisoosrvctl start database -d eisoo-i eisoo -o mountSQL>alter database archivelog;SQL>alter database open;4.创建standby logfile；SQL> select thread#,group#,bytes/1024/1024 from v$log; THREAD# GROUP# BYTES/1024/1024---------- ---------- --------------- 1 1 50 1 2 50 2 3 50 2 4 50SQL> alter database add standby logfile thread 1 group 10 size 50M;SQL> alter database add standby logfile thread 1 group 11 size 50M;SQL> alter database add standby logfile thread 1 group 12 size 50M;SQL> alter database add standby logfile thread 2 group 13 size 50M;SQL> alter database add standby logfile thread 2 group 14 size 50M;SQL> alter database add standby logfile thread 2 group 15 size 50M;SQL> select thread#,group#,bytes/1024/1024 from v$standby_log; THREAD# GROUP# BYTES/1024/1024---------- ---------- --------------- 1 10 50 1 11 50 1 12 50 2 13 50 2 14 50 2 15 505.创建备库参数文件SQL> show parameter spfile;NAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DATA/EISOO/PARAMETERFILE/spfile.281.923255053SQL> create pfile='/tmp/initeisoos.ora' from spfile='+DATA/EISOO/PARAMETERFILE/spfile.281.923255053';[Oracle@rac1 tmp]$ scp initeisoos.ora 192.168.180.48:$ORACLE_HOME/dbs备库上操作：1.修改参数文件使用asm：*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'*.control_files='/data/oradata/eisoos/control01.ctl'*.db_block_size=8192*.db_create_file_dest='/data/oradata/eisoos'*.db_domain=''*.db_file_name_convert='eisoo','eisoos'*.db_name='eisoo'*.db_unique_name='eisoos'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'*.fal_server='EISOOS'*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='eisoo','eisoos'*.open_cursors=300*.pga_aggregate_target=453m*.processes=300*.remote_login_passWordfile='exclusive'*.sga_target=1361m*.standby_file_management='AUTO'eisoos.undo_tablespace='UNDOTBS1'单机未使用asm：*.audit_file_dest='/u01/app/oracle/admin/eisoos/adump'*.audit_trail='db'*.compatible='12.1.0.2.0'*.control_files='/data/oradata/eisoos/control01.ctl'#Restore Controlfile*.db_block_size=8192*.db_create_file_dest='/data/oradata/eisoos'*.db_domain=''*.db_file_name_convert='+DATA/EISOO/DATAFILE','/data/oradata/eisoos','+DATA/EISOO/TEMPFILE','/data/oradata/eisoos'*.db_name='eisoo'*.db_unique_name='eisoos'*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=eisoosXDB)'*.fal_server='EISOOS'*.log_archive_config='DG_CONFIG=(eisoo,eisoos)'*.log_archive_dest_1='LOCATION=/data/oradata/eisoos/archivelog'*.log_archive_dest_2='SERVICE=eisoo ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eisoo'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='+DATA/EISOO/ONLINELOG','/data/oradata/eisoos'*.open_cursors=300*.pga_aggregate_target=453m*.processes=300*.remote_login_passwordfile='exclusive'*.sga_target=1361m*.standby_file_management='AUTO'eisoos.undo_tablespace='UNDOTBS1'2.启动到 nomout状态SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initeisoos.ora';ORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 486539360 bytesDatabase Buffers 939524096 bytesRedo Buffers 13852672 bytesSQL> SQL>ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=false;备注：SEC_CASE_SENSITIVE_LOGON参数是决定密码文件是否可以在本地创建，否则只能从主库拷贝到备库。默认值是“true”,3.在本地创建密码文件ocrl:/u01/app/oracle/product/12.1.0/db_1/dbs@oracle1>orapwd file=orapweisoos password=oracle entries=10 ignorecase=y force=y4.配置监听文件，保证primary和standby能够互连备库:LISTENER = (DESCRipTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = eisoos) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = eisoos) ) )或者SID_LIST_LISTENER_EISOOS = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = eisoos) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1) (SID_NAME = eisoos) ) )主库和备库是tnsname.ora 配置如下：eisoo = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.51)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = eisoo) ) )eisoos = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.180.41)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = eisoos) ) )主备分别验证：[oracle@rac1 ~]$ sqlplus sys/oracle@eisoo as sysdba[oracle@rac1 ~]$ sqlplus sys/oracle@eisoos as sysdba[oracle@rac2 ~]$ sqlplus sys/oracle@eisoo as sysdba[oracle@rac2 ~]$ sqlplus sys/oracle@eisoos as sysdba5.备份恢复数据eisoos:/home/oracle@oracle1>rman target sys/oracle@eisoo auxiliary sys/oracle@eisoosRecovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 26 16:50:42 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: EISOO (DBID=3774196505)connected to auxiliary database: EISOO (not mounted)RMAN> duplicate target database for standby from active database;Starting Duplicate Db at 2016/09/26 16:52:126.开启实时同步SQL> alter database recover managed standby database using current logfile disconnect from session;验证:1）SQL> select dest_name,error from v$archive_dest;//通过查看archive_log_dest_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题2）查询主库最大归档序号和备库最大归档序号select max(sequence#) from v$archived_log;然后在主库切换日志：alter system switch logfile;再次查询备库最大归档序号，一致即归档同步成功。主库：SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 133SQL> alter system switch logfile;System altered.SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 134SQL> 备库：SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 134SQL> 3) 主库验证SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；备库验证SQL>select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG；备注：观察主备库日志是否同步，如一致则表示日志CDP同步正常。主备切换1.检查DG是否同步是否正常主库： SQL> select switchover_status,database_role from gv$database; SWITCHOVER_STATUS DATABASE_ROLE-------------------- ----------------TO STANDBY PRIMARYTO STANDBY PRIMARY备库:SQL> select switchover_status,database_role from gv$database; SWITCHOVER_STATUS DATABASE_ROLE-------------------- ----------------NOT ALLOWED PHYSICAL STANDBY2.准备切换工作:关闭RAC库，并把rac1起到open 状态[oracle@rac1 ~]$ srvctl stop database -d eisoo[oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 09:12:25 2016Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 553648224 bytesDatabase Buffers 872415232 bytesRedo Buffers 13852672 bytesDatabase mounted.Database opened.SQL> 3.开始切换主库：SQL> alter database commit to switchover to physical standby with session shutdown;重启数据库到mount状态SQL> startup mountORACLE instance started.Total System Global Area 1442840576 bytesFixed Size 2924448 bytesVariable Size 553648224 bytesDatabase Buffers 872415232 bytesRedo Buffers 13852672 bytesDatabase mounted.SQL> 查看数据库角色与状态SQL> select status from v$instance;STATUS------------MOUNTEDSQL> select database_role from v$database;DATABASE_ROLE----------------PHYSICAL STANDBYSQL> 此时rac1已变成备库备库：SQL> alter database commit to switchover to primary with session shutdown;Database altered.SQL> alter database open;Database altered.

上一篇：oracle 系统权限和对象权限

下一篇：MySQL一次更新多条不同的记录