时间：2021-07-01 10:21:17 帮助过：28人阅读
standby.__db_cache_size=339738624 standby.__java_pool_size=4194304 standby.__large_pool_size=4194304 standby.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment standby.__pga_aggregate_target=339738624 standby.__sga_target=503316480 standby.__shared_io_pool_size=0 standby.__shared_pool_size=146800640 standby.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/standby/adump‘ *.audit_trail=‘db‘ *.compatible=‘11.2.0.0.0‘ *.control_files=‘/u01/app/oracle/oradata/standby/control01.ctl‘,‘/u01/app/oracle/fast_recovery_area/standby/control02.ctl‘ *.db_block_size=8192 *.db_domain=‘‘ *.db_name=‘primary‘ *.db_recovery_file_dest=‘/u01/app/oracle/fast_recovery_area‘ *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest=‘/u01/app/oracle‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=standbyXDB)‘ *.memory_target=839909376 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.undo_tablespace=‘UNDOTBS1‘
在参数文件后追加，standby所需的参数
*.db_file_name_convert=‘/primary/‘,‘/standby/‘ *.log_file_name_convert=‘/primary/‘,‘/standby/‘ *.standby_file_management=‘auto‘ *.log_archive_config=‘dg_config=(primary,standby)‘ *.fal_server=‘primary‘ *.db_unique_name=‘primary‘
10. 建立目录
11gdg1-> mkdir -p /u01/app/oracle/admin/standby/adump 11gdg1-> mkdir -p /u01/app/oracle/oradata/standby 11gdg1-> mkdir -p /u01/app/oracle/fast_recovery_area/standby
11. 恢复从数据库
11gdg1-> export | grep ORACLE declare -x ORACLE_BASE="/u01/app/oracle" declare -x ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1" declare -x ORACLE_HOSTNAME="11gdg1.localdomain.com" declare -x ORACLE_SID="standby" declare -x ORACLE_TERM="xterm" declare -x ORACLE_UNQNAME="standby"
数据库启动到nomount
SQL> startup nomount ORA-00845: MEMORY_TARGET not supported on this system
将/dev/shm调大。将主库关闭，并修改/etc/fstab
tmpfs /dev/shm tmpfs defaults,size=2G 0 0 #size指定大小
卸载shm并重新mount，或者重启机器。
SQL> startup nomount ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 494931288 bytes Database Buffers 339738624 bytes Redo Buffers 2379776 bytes SQL>
恢复控制文件
11gdg1-> rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jul 11 13:05:18 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: STANDBY (not mounted) RMAN> restore standby controlfile from ‘/u01/standby_04qbpatu_1_1.ctl‘; Starting restore at 2015/07/11 13:09:40 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/standby/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/standby/control02.ctl Finished restore at 2015/07/11 13:09:42
启动到mount，并还原数据库。（注意：不要恢复数据库）
RMAN> startup mount Oracle instance started database mounted Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 494931288 bytes Database Buffers 339738624 bytes Redo Buffers 2379776 bytes
RMAN> restore database; Starting restore at 2015/07/11 13:15:18 Starting implicit crosscheck backup at 2015/07/11 13:15:18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 2015/07/11 13:15:19 Starting implicit crosscheck copy at 2015/07/11 13:15:19 using channel ORA_DISK_1 Finished implicit crosscheck copy at 2015/07/11 13:15:19 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/standby/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/standby/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/standby/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/standby/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/FULL_01qbpam2_1_1.bak channel ORA_DISK_1: piece handle=/u01/FULL_01qbpam2_1_1.bak tag=TAG20150711T120834 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 2015/07/11 13:16:05
12. 在从库上建立standby logfile
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/standby/sredo01.log‘ size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/standby/sredo02.log‘ size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/standby/sredo03.log‘ size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/standby/sredo04.log‘ size 50M;
13. 启动主从库的监听，并配置相互连接的tnsname
11gdg1-> cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = primary) ) (SID_DESC = (GLOBAL_DBNAME = standby) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = standby) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gdg1.localdomain.com)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle 11gdg1-> cat 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. PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.216)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primary) ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.216)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby) ) )
14. 修改主库参数
SQL> alter system set log_archive_dest_2=‘service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby‘; System altered. SQL> alter system set log_archive_config=‘dg_config=(primary,standby)‘; System altered. SQL>
15. 保证两个实例的sysdba角色用户的密码一样，并都有密码文件。
11gdg1-> cp orapwprimary orapwstandby
16. 启动备库日志应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ; Database altered.
17. 测试DataGuard数据同步功能
主库上创建一个t1表。
SQL> ALTER USER SCOTT ACCOUNT UNLOCK; User altered. SQL> ALTER USER SCOTT IDENTIFIED BY tiger; User altered. SQL> conn scott/tiger Connected. SQL> create table t1 as select * from emp; Table created.
查看从库是否有数据
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> alter database open read only; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ; Database altered. SQL> conn scott/tiger Connected. SQL> select count(*) from t1; COUNT(*) ---------- 14
DataGuard已经配置成功！
注：如果希望日后做switchover或者Failover。现在的primary数据库要配置从数据库所需的参数，而现在的standby数据库需要配置上主数据库所需的参数。
如下：
在primary库添加standby角色参数
ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/primary/sredo01.log‘ size 50M; ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/primary/sredo02.log‘ size 50M; ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/primary/sredo03.log‘ size 50M; ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/oradata/primary/sredo04.log‘ size 50M; alter system set db_file_name_convert=‘/standby/‘,‘/primary/‘ scope=spfile; alter system set log_file_name_convert=‘/standby/‘,‘/primary/‘ scope=spfile; alter system set standby_file_management=‘auto‘ scope=spfile; alter system set fal_server=‘standby‘ scope=spfile;
在standby库添加primary角色参数
SQL> create spfile from pfile; File created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 494931288 bytes Database Buffers 339738624 bytes Redo Buffers 2379776 bytes Database mounted. Database opened. SQL> alter system set log_archive_dest_2=‘service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=primary‘; System altered. SQL>
本文出自 “叮咚” 博客，请务必保留此出处http://lqding.blog.51cto.com/9123978/1682604
Oracle学习之DATAGUARD(四) 在同台机器上搭建DG
标签：手工创建dataguard