| Oracle11g 搭建DataGuard | 您所在的位置：网站首页 › dataguard安装 › Oracle11g 搭建DataGuard |
Oracle11g 搭建DataGuard
|
一、安装环境： 名称主库备库主机名ora11gora11gs操作系统Redhat6.5_64位Redhat6.5_64位IP192.168.186.134192.168.186.137数据库版本Oracle 11.2.0.4.0Oracle 11.2.0.4.0ORACLE_BASE/u01/app/oracle/u01/app/oracleORACLE_HOME/u01/app/oracle/product/11.2.0/dbhome_1/u01/app/oracle/product/11.2.0/dbhome_1ORACLE_SIDorclorcl归档模式是否数据库安装安装数据库软件，创建监听，并建库安装数据库软件，创建监听，但不建库二、主库配置： 1、开启归档并强制日志模式： 开归档步骤不在这里赘述，归档开启后如下图：
强制日志模式： alter database force logging; SQL> select name,log_mode,force_logging from v$database; NAME LOG_MODE FOR --------- ------------ --- ORCL ARCHIVELOG YES2、创建standby redolog日志组： 原则： 1：standby redo log的文件大小与primary 数据库online redo log 文件大小相同 2：standby redo log日志文件组的个数依照下面的原则进行计算： Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数 假如只有一个节点，这个节点有三组redolog， 所以Standby redo log组数>=(3+1)*1 == 4 所以至少需要创建4组Standby redo log 查看当前线程与日志组的对应关系及日志组的大小： SQL> select thread#,group#,bytes/1024/1024 from v$log; THREAD# GROUP# BYTES/1024/1024 ---------- ---------- --------------- 1 1 50 1 2 50 1 3 50如上，这里有三组redo log，所以至少需要创建4组Standby redo log，大小均为50M： alter database add standby logfile group 4('/u01/app/oracle/oradata/orcl/standbyredo01.log') size 50m; alter database add standby logfile group 5('/u01/app/oracle/oradata/orcl/standbyredo02.log') size 50m; alter database add standby logfile group 6('/u01/app/oracle/oradata/orcl/standbyredo03.log') size 50m; alter database add standby logfile group 7('/u01/app/oracle/oradata/orcl/standbyredo04.log') size 50m;查看standby 日志组的信息： SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log; GROUP# SEQUENCE# STATUS BYTES/1024/1024 ---------- ---------- ---------- --------------- 4 0 UNASSIGNED 50 5 0 UNASSIGNED 50 6 0 UNASSIGNED 50 7 0 UNASSIGNED 503、创建主库密码文件： su - oracle orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y4、 配置spfile文件： 查看spfile的路径： SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfileorcl.ora用spfile创建一个pfile,用于修改： create pfile='/tmp/initorcl.ora' from spfile;修改pfile文件： vim /tmp/initorcl.ora --------------------------------------------------------------------------------------------------------------------------------------------------------- orcl.__db_cache_size=637534208 orcl.__java_pool_size=16777216 orcl.__large_pool_size=33554432 orcl.__oracle_base='/u01/app/oracle' orcl.__pga_aggregate_target=671088640 orcl.__sga_target=989855744 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=268435456 orcl.__streams_pool_size=16777216 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain=''*.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=1653604352 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'*.db_unique_name='orclpr'*.fal_client='orclpr' *.fal_server='orcldg'*.standby_file_management='AUTO'*.log_archive_config='DG_CONFIG=(orclpr,orcldg)'*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/archivelog' *.log_archive_dest_2='SERVICE=orcldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' ------------------------------------------------------------------------------------------------------------------------------------------------------------- 复制pfile文件到spfile： shutdown immediate; create spfile from pfile='/tmp/initorcl.ora'; startup;5、修改监听文件，添加静态监听： vi $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.134)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) ADR_BASE_LISTENER = /u01/app/oracle SAVE_CONFIG_ON_STOP_LISTENER = ON重启监听服务： lsnrctl stop lsnrctl start6、编辑网络服务名配置文件tnsnames.ora： vi $ORACLE_HOME/network/admin/tnsnames.ora orcldg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.137)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) orclpr = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.134)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )tnsping测试：
三、备库配置： 1、将主库中的密码文件、pfile文件、监听文件复制到备库中： cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs scp orapworcl 192.168.186.137:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ scp /tmp/initorcl.ora 192.168.186.137:/tmp/ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin scp listener.ora 192.168.186.137:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ scp tnsnames.ora 192.168.186.137:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/2、手工创建所需的目录： su - oracle mkdir -p /u01/app/oracle/admin/orcl/adump mkdir -p /u01/app/oracle/admin/orcl/dbdump mkdir -p /u01/app/oracle/admin/orcl/pfile mkdir -p /u01/app/oracle/oradata/orcl mkdir -p /u01/app/oracle/fast_recovery_area/orcl mkdir -p /u01/app/oracle/oradata/orcl/archivelog3、配置spfile文件： 修改pfile文件： vim /tmp/initorcl.ora --------------------------------------------------------------------------------------------------------------------------------------------------------- orcl.__db_cache_size=637534208 orcl.__java_pool_size=16777216 orcl.__large_pool_size=33554432 orcl.__oracle_base='/u01/app/oracle' orcl.__pga_aggregate_target=671088640 orcl.__sga_target=989855744 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=268435456 orcl.__streams_pool_size=16777216 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain=''*.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=1653604352 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'*.db_unique_name='orcldg'*.fal_client='orcldg' *.fal_server='orclpr'*.standby_file_management='AUTO' *.log_archive_config='DG_CONFIG=(orclpr,orcldg)' *.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/archivelog' *.log_archive_dest_2='SERVICE=orclpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclpr' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' --------------------------------------------------------------------------------------------------------------------------------------------------------- 复制pfile文件到spfile： create spfile from pfile='/tmp/initorcl.ora'; shutdown immediate; startup nomount;4、修改监听文件： vi $ORACLE_HOME/network/admin/listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.186.137)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle 重启监听服务： lsnrctl stop lsnrctl start5、tnsping测试： 6、启动备库到nomount： shutdown immediate; startup nomount;7、利用RMAN在主库上执行，将主库恢复到备库上： rman target sys/password@orclpr auxiliary sys/password@orcldg duplicate target database for standby from active database nofilenamecheck;恢复过程如下： [oracle@ora11gs ~]$ rman target sys/123.com@orclpr auxiliary sys/123.com@orcldg Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 8 15:30:39 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1509509845) connected to auxiliary database: ORCL (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 08-NOV-18 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=134 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' ; } executing Memory Script Starting backup at 08-NOV-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK Finished backup at 08-NOV-18 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl/control01.ctl'; restore clone controlfile to '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' from '/u01/app/oracle/oradata/orcl/control01.ctl'; } executing Memory Script Starting backup at 08-NOV-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20181108T153017 RECID=3 STAMP=991668618 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 08-NOV-18 Starting restore at 08-NOV-18 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 08-NOV-18 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/orcl/temp01.dbf"; set newname for tempfile 2 to "/u01/app/oracle/oradata/orcl/db_temp.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/orcl/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/orcl/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/orcl/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/orcl/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/orcl/example01.dbf"; set newname for datafile 6 to "/u01/app/oracle/oradata/orcl/db_test.dbf"; set newname for datafile 7 to "/u01/app/oracle/oradata/orcl/db_test1.dbf"; set newname for datafile 8 to "/u01/app/oracle/oradata/orcl/db_test11.dbf"; set newname for datafile 9 to "/u01/app/oracle/oradata/orcl/db_test12.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/orcl/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/orcl/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/orcl/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/orcl/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/orcl/example01.dbf" datafile 6 auxiliary format "/u01/app/oracle/oradata/orcl/db_test.dbf" datafile 7 auxiliary format "/u01/app/oracle/oradata/orcl/db_test1.dbf" datafile 8 auxiliary format "/u01/app/oracle/oradata/orcl/db_test11.dbf" datafile 9 auxiliary format "/u01/app/oracle/oradata/orcl/db_test12.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/orcl/temp01.dbf in control file renamed tempfile 2 to /u01/app/oracle/oradata/orcl/db_temp.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 08-NOV-18 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf output file name=/u01/app/oracle/oradata/orcl/system01.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:16 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf output file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:29 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf output file name=/u01/app/oracle/oradata/orcl/example01.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:56 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf output file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/db_test1.dbf output file name=/u01/app/oracle/oradata/orcl/db_test1.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/db_test.dbf output file name=/u01/app/oracle/oradata/orcl/db_test.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/db_test11.dbf output file name=/u01/app/oracle/oradata/orcl/db_test11.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=/u01/app/oracle/oradata/orcl/users01.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/db_test12.dbf output file name=/u01/app/oracle/oradata/orcl/db_test12.dbf tag=TAG20181108T153029 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 08-NOV-18 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=3 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=4 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=7 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=8 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/db_test.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/db_test1.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/db_test11.dbf datafile 9 switched to datafile copy input datafile copy RECID=11 STAMP=991669293 file name=/u01/app/oracle/oradata/orcl/db_test12.dbf Finished Duplicate Db at 08-NOV-18 恢复完成！ 过程中若报错如下： DBGSQL: TARGET> begin :fhdbi := dbms_rcvcat.getDbid; end; DBGSQL: sqlcode = 6550 DBGSQL: B :fhdbi = 32767 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 11/08/2018 15:22:28 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script ORA-06550: line 1, column 17: PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored 说明使用了catalog,但是在连接的时候没有指定catalog，需要用下面的连接方式： rman target sys/123.com@orclpr auxiliary sys/123.com@orcldg nocatalog8、 登陆备库并查看数据库当前状态： [oracle@ora11gs ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 8 15:45:34 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select status from v$instance; STATUS ------------MOUNTED RMAN恢复完直接就是mount状态。 9、备库启动日志应用： SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> set pagesize 100; SQL> select sequence#,applied from v$archived_log order by 1; SEQUENCE# APPLIED ---------- --------- 76 YES 77 YES 78 YES 10、分别查看主库和备库的归档序列号是否一致： 先在主库手动切换一下日志再查看： SQL> alter system switch logfile; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/orcl/archivelogOldest online log sequence 78 Next log sequence to archive 80Current log sequence 80 再在备库上查看： SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/oradata/orcl/archivelogOldest online log sequence 78 Next log sequence to archive 0Current log sequence 80 11、查看备库中各文件如下： [root@ora11gs orcl]# ll total 2001444 drwxr-xr-x. 2 oracle oinstall 4096 Nov 8 15:47 archivelog -rw-r-----. 1 oracle oinstall 9781248 Nov 8 15:48 control01.ctl -rw-r-----. 1 oracle oinstall 10493952 Nov 8 15:47 db_test11.dbf -rw-r-----. 1 oracle oinstall 5251072 Nov 8 15:47 db_test12.dbf -rw-r-----. 1 oracle oinstall 52436992 Nov 8 15:47 db_test1.dbf -rw-r-----. 1 oracle oinstall 33562624 Nov 8 15:47 db_test.dbf -rw-r-----. 1 oracle oinstall 328343552 Nov 8 15:47 example01.dbf -rw-r-----. 1 oracle oinstall 692068352 Nov 8 15:47 sysaux01.dbf -rw-r-----. 1 oracle oinstall 786440192 Nov 8 15:47 system01.dbf -rw-r-----. 1 oracle oinstall 125837312 Nov 8 15:47 undotbs01.dbf -rw-r-----. 1 oracle oinstall 5251072 Nov 8 15:47 users01.dbf [root@ora11gs orcl]# ll archivelog/ total 5848 -rw-r-----. 1 oracle oinstall 5109248 Nov 8 15:47 1_76_981729112.dbf -rw-r-----. 1 oracle oinstall 530432 Nov 8 15:47 1_77_981729112.dbf -rw-r-----. 1 oracle oinstall 344064 Nov 8 15:47 1_78_981729112.dbf 至此，过程完成！
|
【本文地址】