1. primary库设置dataguard相关参数
1.1. 强制primay库在任何状态下必须记录日志
SYS@userdata>select FORCE_LOGGING from v$database; FORCE_LOG --------- NO SYS@userdata>alter database force logging; Database altered. SYS@userdata>select FORCE_LOGGING from v$database; FORCE_LOG --------- YES
1.2. 设置primay库在归档模式
SYS@userdata>shutdown immediate; SYS@userdata>startup mount; SYS@userdata>alter database archivelog; SYS@userdata>alter database open; SYS@userdata>archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4
1.3. 设置primary和standby库的tnsnames
$ cat $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. userdata1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ec2t-userdata-01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = userdata1) ) ) userdata2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ec2t-userdata-02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = userdata2) ) )
1.4. 根据spfile创建pfile文件
SYS@userdata>create pfile='/home/oracle/pfile.ora' from spfile; File created.
1.5. 更新pfile文件,添加dataguard相关参数
$ mkdir /u01/app/oracle/arch $ cat /home/oracle/pfile.ora userdata.__db_cache_size=620756992 userdata.__java_pool_size=16777216 userdata.__large_pool_size=33554432 userdata.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment userdata.__pga_aggregate_target=620756992 userdata.__sga_target=905969664 userdata.__shared_io_pool_size=0 userdata.__shared_pool_size=218103808 userdata.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/userdata/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/userdata/control01.ctl','/u01/app/oracle/fast_recovery_area/userdata/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='userdata' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4294967296 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=userdataXDB)' *.java_jit_enabled=FALSE *.memory_target=1526726656 *.open_cursors=600 *.processes=600 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_unique_name='userdata1' *.log_archive_config='DG_CONFIG=(userdata1,userdata2)' *.log_archive_dest_1='location=/u01/app/oracle/arch' *.log_archive_dest_2='SERVICE=userdata2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=userdata2' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.standby_file_management='AUTO' *.fal_client='userdata1' *.fal_server='userdata2'
1.6. 导入新的参数文件,使dataguard配置生效
SYS@userdata>shutdown immediate; SYS@userdata>create spfile from pfile='/home/oracle/pfile.ora'; SYS@userdata>startup;
同时修改.bash_profile的ORACLE_UNQNAME的值
2. standby库设置dataguard相关参数
2.1. 在primary库生成pfile,传到standby库
SYS@userdata>create pfile='/home/oracle/pfile.ora.bak' from spfile; $ scp /home/oracle/pfile.ora.bak oracle@ec2t-userdata-02:/home/oracle/
2.2. 修改standby库的dataguard相关参数
$ cat /home/oracle/pfile.ora.bak userdata.__db_cache_size=603979776 userdata.__java_pool_size=16777216 userdata.__large_pool_size=33554432 userdata.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment userdata.__pga_aggregate_target=620756992 userdata.__sga_target=905969664 userdata.__shared_io_pool_size=0 userdata.__shared_pool_size=234881024 userdata.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/userdata/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/userdata/control01.ctl','/u01/app/oracle/fast_recovery_area/userdata/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='userdata' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4294967296 *.db_unique_name='userdata2' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=userdataXDB)' *.fal_client='userdata2' *.fal_server='userdata1' *.java_jit_enabled=FALSE *.log_archive_config='DG_CONFIG=(userdata1,userdata2)' *.log_archive_dest_1='location=/u01/app/oracle/arch' *.log_archive_dest_2='SERVICE=userdata2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=userdata2' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.memory_target=1526726656 *.open_cursors=600 *.processes=600 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1'$ mkdir -p /u01/app/oracle/admin/userdata/adump $ mkdir -p /u01/app/oracle/oradata/userdata/ $ mkdir -p /u01/app/oracle/fast_recovery_area/userdata $ mkdir -p /u01/app/oracle/arch
同时修改.bash_profile的ORACLE_UNQNAME的值
2.3. 设置primary和standby库的tnsnames(从主库复制即可)
$ scp oracle@ec2t-userdata-01:$ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/
2.4. 设置standby库的远程登录验证(从主库复制即可)
$ scp oracle@ec2t-userdata-01:$ORACLE_HOME/dbs/orapwuserdata $ORACLE_HOME/dbs/
3. 对primary库做RMAN备份,并将备份传至standby库
$ mkdir /home/oracle/dataset $ rman target / RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> run { allocate channel ch1 device type disk; sql 'alter system archive log current'; backup database format '/home/oracle/dataset/data_%U.bak'; backup archivelog all format '/home/oracle/dataset/arch_%U.bak'; backup current controlfile for standby format '/home/oracle/dataset/ctl_%U.bak'; release channel ch1; }
allocated channel: ch1 channel ch1: SID=45 device type=DISK sql statement: alter system archive log current Starting backup at 2017-06-23 04:24:02 channel ch1: starting full datafile backup set channel ch1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/userdata/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/userdata/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/userdata/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/userdata/users01.dbf channel ch1: starting piece 1 at 2017-06-23 04:24:03 channel ch1: finished piece 1 at 2017-06-23 04:25:28 piece handle=/home/oracle/dataset/data_01s7g3b3_1_1.bak tag=TAG20170623T042402 comment=NONE channel ch1: backup set complete, elapsed time: 00:01:25 Finished backup at 2017-06-23 04:25:28 Starting backup at 2017-06-23 04:25:29 current log archived channel ch1: starting archived log backup set channel ch1: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=1 STAMP=947322924 input archived log thread=1 sequence=5 RECID=2 STAMP=947322932 input archived log thread=1 sequence=6 RECID=3 STAMP=947391842 input archived log thread=1 sequence=7 RECID=4 STAMP=947391929 channel ch1: starting piece 1 at 2017-06-23 04:25:30 channel ch1: finished piece 1 at 2017-06-23 04:25:37 piece handle=/home/oracle/dataset/arch_02s7g3dq_1_1.bak tag=TAG20170623T042529 comment=NONE channel ch1: backup set complete, elapsed time: 00:00:07 Finished backup at 2017-06-23 04:25:37 Starting backup at 2017-06-23 04:25:37 channel ch1: starting full datafile backup set channel ch1: specifying datafile(s) in backup set including standby control file in backup set channel ch1: starting piece 1 at 2017-06-23 04:25:39 channel ch1: finished piece 1 at 2017-06-23 04:25:40 piece handle=/home/oracle/dataset/ctl_03s7g3e2_1_1.bak tag=TAG20170623T042537 comment=NONE channel ch1: backup set complete, elapsed time: 00:00:01 Finished backup at 2017-06-23 04:25:40 Starting Control File and SPFILE Autobackup at 2017-06-23 04:25:40 piece handle=/u01/app/oracle/fast_recovery_area/USERDATA1/autobackup/2017_06_23/o1_mf_s_947391940_dnsyonvd_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-06-23 04:25:43 released channel: ch1
$ scp -r /home/oracle/dataset oracle@ec2t-userdata-02:/home/oracle/
4. 还原standby库
4.1. 还原参数文件
SYS@userdata>create spfile from pfile='/home/oracle/pfile.ora.bak'; SYS@userdata>startup nomount;
4.2. 还原控制文件
$ rman target / RMAN> restore standby controlfile from '/home/oracle/dataset/ctl_03s7g3e2_1_1.bak'; RMAN> alter database mount;
4.3. 还原数据文件
RMAN> restore database; RMAN> recover database;
5. 创建standby联机日志
5.1. 在primary库创建
SYS@userdata>SELECT GROUP#, BYTES/1024/1024 as MB FROM V$LOG; GROUP# MB ---------- ---------- 1 100 2 100 3 100 SYS@userdata>select GROUP#,MEMBER from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/userdata/redo01.log 2 /u01/app/oracle/oradata/userdata/redo02.log 3 /u01/app/oracle/oradata/userdata/redo03.log SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo01.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo02.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo03.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo04.log') size 100M; Database altered. SYS@userdata>select GROUP#,BYTES/1024/1024 as MB from v$standby_log; GROUP# MB ---------- ---------- 4 100 5 100 6 100 7 100 SYS@userdata>select GROUP#,MEMBER,TYPE from v$logfile; GROUP# MEMBER TYPE ---------- -------------------------------------------------- --------------------- 1 /u01/app/oracle/oradata/userdata/redo01.log ONLINE 2 /u01/app/oracle/oradata/userdata/redo02.log ONLINE 3 /u01/app/oracle/oradata/userdata/redo03.log ONLINE 4 /u01/app/oracle/oradata/userdata/stredo01.log STANDBY 5 /u01/app/oracle/oradata/userdata/stredo02.log STANDBY 6 /u01/app/oracle/oradata/userdata/stredo03.log STANDBY 7 /u01/app/oracle/oradata/userdata/stredo04.log STANDBY 7 rows selected.
5.2. 在standby库创建
SYS@userdata>SELECT GROUP#, BYTES/1024/1024 as MB FROM V$LOG; GROUP# MB ---------- ---------- 1 100 3 100 2 100 SYS@userdata>select GROUP#,MEMBER from v$logfile; GROUP# MEMBER ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/userdata/redo01.log 2 /u01/app/oracle/oradata/userdata/redo02.log 3 /u01/app/oracle/oradata/userdata/redo03.log SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo01.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo02.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo03.log') size 100M; Database altered. SYS@userdata>alter database add standby logfile ('/u01/app/oracle/oradata/userdata/stredo04.log') size 100M; Database altered. SYS@userdata>select GROUP#,BYTES/1024/1024 as MB from v$standby_log; GROUP# MB ---------- ---------- 4 100 5 100 6 100 7 100 SYS@userdata>select GROUP#,MEMBER,TYPE from v$logfile; GROUP# MEMBER TYPE ---------- -------------------------------------------------- --------------------- 1 /u01/app/oracle/oradata/userdata/redo01.log ONLINE 2 /u01/app/oracle/oradata/userdata/redo02.log ONLINE 3 /u01/app/oracle/oradata/userdata/redo03.log ONLINE 4 /u01/app/oracle/oradata/userdata/stredo01.log STANDBY 5 /u01/app/oracle/oradata/userdata/stredo02.log STANDBY 6 /u01/app/oracle/oradata/userdata/stredo03.log STANDBY 7 /u01/app/oracle/oradata/userdata/stredo04.log STANDBY 7 rows selected.
6. 启动standby监听
$ lsnrctl start
7. 开启stanby库恢复进程
SYS@userdata>alter database recover managed standby database disconnect from session using current logfile;
8.primary库归档,查看dataguard数据同步是否成功
SYS@userdata>select name,archived,applied,sequence# from v$archived_log; NAME ARCHIVED APPLIED SEQUENCE# -------------------------------------------------- --------- --------------------------- ---------- /u01/app/oracle/arch/1_4_947274260.dbf YES NO 4 /u01/app/oracle/arch/1_5_947274260.dbf YES NO 5 /u01/app/oracle/arch/1_6_947274260.dbf YES NO 6 /u01/app/oracle/arch/1_7_947274260.dbf YES NO 7 /u01/app/oracle/arch/1_8_947274260.dbf YES NO 8 userdata2 YES YES 8 6 rows selected. SYS@userdata>alter system archive log current; System altered. SYS@userdata>alter system archive log current; System altered. SYS@userdata>select name,archived,applied,sequence# from v$archived_log; NAME ARCHIVED APPLIED SEQUENCE# -------------------------------------------------- --------- --------------------------- ---------- /u01/app/oracle/arch/1_4_947274260.dbf YES NO 4 /u01/app/oracle/arch/1_5_947274260.dbf YES NO 5 /u01/app/oracle/arch/1_6_947274260.dbf YES NO 6 /u01/app/oracle/arch/1_7_947274260.dbf YES NO 7 /u01/app/oracle/arch/1_8_947274260.dbf YES NO 8 userdata2 YES YES 8 userdata2 YES YES 9 /u01/app/oracle/arch/1_9_947274260.dbf YES NO 9 /u01/app/oracle/arch/1_10_947274260.dbf YES NO 10 userdata2 YES YES 10 userdata2 YES NO 11 /u01/app/oracle/arch/1_11_947274260.dbf YES NO 11 12 rows selected.
252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
