正式生产库，配置双节点的RAC + 单实例的 DATAGUARD
来源：互联网 发布：怎么用c语言表白 编辑：程序博客网 时间：2024/04/29 18:14
正式生产库，配置DATAGUARD
RAC+单实例DATAGUARD 配置
RAC 两节点：
192.1.0.101 rac1
192.1.0.102 rac2
存储：ASM
DB_UNIQUE_NAME= racdb
实例：racdb1; racdb2;
DG:
192.1.0.103 dg1
存储 ：ASM
数据库名： racdb
DB_UNIQUE_NAME=stbdb;
一、系统准备
1.1 DG 磁盘分区：
本地磁盘，磁盘分区ASM DISK 对照表
磁盘分区 磁盘大小 RAID ASM NAME ASM GROUPDISKNAME
-----------------------------------------------------------------
/dev/sdb
/dev/sdb1 300 GB RAID5 RECOVERY1 +RECOVERY
/dev/sdb2 300 GB RAID5 RECOVERY2 +RECOVERY
/dev/sdb3 4023.1 GB RAID5 RECOVERY3 +RECOVERY
/dev/sdc1 899.5 MB RAID0 DATA1 +DATA
/dev/sdd1 899.5 GB RAID0 DATA2 +DATA
/dev/sde1 899.5 GB RAID0 DATA3 +DATA
/dev/sdf1 899.5 GB RAID0 DATA4 +DATA
/dev/sdg1 899.5 GB RAID0 DATA5 +DATA
/dev/sdb 为多个小盘合并成为一个RAID5的磁盘，因考虑到多个小盘（8*146G）多分区不好管理，
同时这几个多个小盘已使用了2年左右，合并一起做RAID5也可以做个容错处理。
1.2 做一个SSH对待访问
#在dg1上执行建立私匙，以方便拷贝文件
# su - oracle
$ mkdir ~/.ssh
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
#将dg1的加入
$ ssh dg1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
$ ssh dg1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
$ scp ~/.ssh/authorized_keys dg1:~/.ssh/authorized_keys
（遇到一个问题：
[oracle@rac1 .ssh]$ ssh dg1 date
Warning: the RSA host key for 'dg1' differs from the key for the IP address '192.1.0.103'
Offending key for IP in /home/oracle/.ssh/known_hosts:15
Matching host key in /home/oracle/.ssh/known_hosts:17
Are you sure you want to continue connecting (yes/no)? yes
2013年 10月 12日 星期六 11:39:08 CST
解决方法：
查看known_hosts 内容，发现'192.1.0.103' 以前指定了其它的服务器，直接删除对应的其它服务器行。
问题解决。
）
还有一个有趣的问题，在RAC1中配置了ssh 对等访问，系统会自动更新到其它的RAC节点中，比如我这里有RAC2；
在单机上先安装一个GRID（ASM),再安装ORACLE.
1.3 安装GRID
【】为独立服务器配置ORACLE GRID INFRASTRUCTURE
安装此项选择，还要选择一个ASM磁盘，是开始时没想到的。后来另外分出两个分区做ASM特征盘，
有点象RAC中的RAC表决盘(或OCR磁盘）。
1.3，1.4 两个安装参考：
http://blog.csdn.net/miyatang/article/details/6659839
先前测试的配置日志（http://blog.csdn.net/miyatang/article/details/9979579）
二、数据库配置
2.1 监听/ASM 配置
2.1.1 listener.ora
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
)
)
2.1.2 tnsnames.ora
[oracle@localhost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.102)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
stbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbdb)
)
)
把stbdb 部分插入到两个节点的 tnsnames.ora 文件中。
2.1.3 grid 用户下，使用asmca 建立 +DATA,+RECOVERY 两个ASM 磁盘组。
测试查看ASM 磁盘组：
[grid@localhost grid]$ asmcmd
Connected to an idle instance.
ASMCMD> ls
ASMCMD-8102: no connection to ASM; command requires ASM to run
修改grid 用户下的.bash_profile
ORACLE_SID=+ASM; export ORACLE_SID (原为：ORACLE_SID=+ASM1; 此内容为RAC中拷贝而来有两个实例ASM1,ASM2.)
PATH=$ORACLE_HOME/bin:$PATH; export PATH
修改后问题解决：
[grid@localhost grid]$ asmcmd
ASMCMD> ls
CRS/
DATA/
RECOVERY/
ASMCMD> exit
2.2.配置参数：
2.2.1、打开Forced Logging模式
SQL> alter database force logging;
Database altered.
2.2.2
SQL> alter system set log_archive_config='DG_CONFIG=(stbdb,racdb)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET FAL_SERVER=stbdb;
SQL> alter system set FAL_CLIENT=racdb;
SQL> alter system set standby_file_management=auto;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stbdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbdb' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
--先设置成不活动的，后面再修改成： ENABLE;
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope=both;
#添加STANDBY LOGFILE:
select group#,THREAD#,l.MEMBERS,l.STATUS from v$log l
GROUP# THREAD# MEMBERS STATUS
1 1 1 2 INACTIVE
2 2 1 2 CURRENT
3 3 2 2 CURRENT
4 4 2 2 ACTIVE
5 5 1 2 ACTIVE
6 6 2 2 ACTIVE
alter database add STANDBY logfile thread 1 group 7 ;
alter database add STANDBY logfile thread 1 group 8 ;
alter database add STANDBY logfile thread 1 group 9 ;
alter database add STANDBY logfile thread 2 group 10 ;
alter database add STANDBY logfile thread 2 group 11 ;
alter database add STANDBY logfile thread 2 group 12 ;
#2.2 生成pfile，并修改。
SQL> create pfile='/tmp/initstbdb.ora' from spfile;
1.修改
*.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
修改后：
*.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'
建立目录 ：
mkdir -p /opt/app/oracle/admin/stbdb/adump
chown oracle:oinstall /opt/app/oracle/admin/stbdb/adump
2.添加以下内容:
*.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'
修改前的 initstbdb.ora
racdb1.__db_cache_size=13623099392
racdb2.__db_cache_size=13623099392
racdb2.__java_pool_size=67108864
racdb1.__java_pool_size=67108864
racdb2.__large_pool_size=67108864
racdb1.__large_pool_size=67108864
racdb1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=9462349824
racdb1.__pga_aggregate_target=9462349824
racdb2.__sga_target=17582522368
racdb1.__sga_target=17582522368
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb1.__shared_pool_size=3556769792
racdb2.__shared_pool_size=3690987520
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=67108864
*.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_flashback_retention_target=1440
*.db_name='racdb'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=3221225472000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.fal_client='RACDB'
*.fal_server='DGDB'
racdb2.instance_number=2
racdb1.instance_number=1
*.log_archive_config='DG_CONFIG=(dgdb,racdb)'
*.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
racdb1.memory_max_target=29360128000
*.memory_target=27031240704
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=5000
*.processes=8000
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=100
*.sessions=5000
*.standby_file_management='MANUAL'
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDO_TBS01'
[oracle@localhost dbs]$
修改后的initstbdb.ora
[oracle@localhost dbs]$ cat initstbdb.ora
stbdb.__db_cache_size=13623099392
stbdb.__java_pool_size=67108864
stbdb.__large_pool_size=67108864
stbdb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
stbdb.__pga_aggregate_target=9462349824
stbdb.__sga_target=17582522368
stbdb.__shared_io_pool_size=0
stbdb.__shared_pool_size=3556769792
stbdb.__streams_pool_size=67108864
*.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_flashback_retention_target=1440
*.db_name='racdb'
db_unique_name='stbdb'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=900000000000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbdb)'
*.fal_client='STBDB'
*.fal_server='RACDB1,RACDB2'
stbdb.instance_number=1
*.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'
*.log_archive_config='DG_CONFIG=(dgdb,racdb)'
*.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
stbdb.memory_max_target=29360128000
*.memory_target=27031240704
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=5000
*.processes=8000
*.remote_listener='stbdb:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=100
*.sessions=5000
*.standby_file_management='AUTO'
thread=1
undo_tablespace='UNDOTBS1'
#2.3创建控制文件 ：
切换两次日志及做两次归档
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
#2.4创建密码文件
[oracle@ract1 ~]$ orapwd file=/tmp/orapwstbdb.ora password=sa force=y ignorecase=y
#2.5把文件拷贝到 dg1 服务器：
scp /tmp/orapwstbdb.ora dg1:/opt/app/oracle/product/11.2.0/dbs/orapwstbdb.ora
scp /tmp/inittdb.ora dg1:/tmp/inittdb.ora
#2.6 在STANDBY 端
SQL> create spfile from pfile='/tmp/inittdb.ora';
File created.
SQL> startup nomount;
[oracle@dg1 dbs]$ rman target sys/oracle@racdb1 auxiliary sys/oracle@stbdb nocatalog
rman>duplicate target database for standby from active database nofilenamecheck;
三、DATAGUARD 开启
SQL> alter database recover managed standby database disconnect from session;
Database altered.
# 开启STANDBY 数据库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
#启用STANDBY数据库，进入RECOVER模式：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
#启动到只读模式：
alter database open read only;
#转换到REAL-TIME模式下：
--alter database recover managed standby database cancel;
--ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
四、测试验证
#PRIMARY 端：
SQL>
SQL> select thread#,sequence#,status from v$log;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 57930 CURRENT
1 57929 INACTIVE
2 43251 INACTIVE
2 43252 ACTIVE
1 57928 INACTIVE
2 43253 CURRENT
6 rows selected.
SQL>
#STANDBY 端
SQL> select archived_thread#,archived_seq#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
2 43252 2 43252
RAC+单实例DATAGUARD 配置
RAC 两节点：
192.1.0.101 rac1
192.1.0.102 rac2
存储：ASM
DB_UNIQUE_NAME= racdb
实例：racdb1; racdb2;
DG:
192.1.0.103 dg1
存储 ：ASM
数据库名： racdb
DB_UNIQUE_NAME=stbdb;
一、系统准备
1.1 DG 磁盘分区：
本地磁盘，磁盘分区ASM DISK 对照表
磁盘分区 磁盘大小 RAID ASM NAME ASM GROUPDISKNAME
-----------------------------------------------------------------
/dev/sdb
/dev/sdb1 300 GB RAID5 RECOVERY1 +RECOVERY
/dev/sdb2 300 GB RAID5 RECOVERY2 +RECOVERY
/dev/sdb3 4023.1 GB RAID5 RECOVERY3 +RECOVERY
/dev/sdc1 899.5 MB RAID0 DATA1 +DATA
/dev/sdd1 899.5 GB RAID0 DATA2 +DATA
/dev/sde1 899.5 GB RAID0 DATA3 +DATA
/dev/sdf1 899.5 GB RAID0 DATA4 +DATA
/dev/sdg1 899.5 GB RAID0 DATA5 +DATA
/dev/sdb 为多个小盘合并成为一个RAID5的磁盘，因考虑到多个小盘（8*146G）多分区不好管理，
同时这几个多个小盘已使用了2年左右，合并一起做RAID5也可以做个容错处理。
1.2 做一个SSH对待访问
#在dg1上执行建立私匙，以方便拷贝文件
# su - oracle
$ mkdir ~/.ssh
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
#将dg1的加入
$ ssh dg1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
$ ssh dg1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
$ scp ~/.ssh/authorized_keys dg1:~/.ssh/authorized_keys
（遇到一个问题：
[oracle@rac1 .ssh]$ ssh dg1 date
Warning: the RSA host key for 'dg1' differs from the key for the IP address '192.1.0.103'
Offending key for IP in /home/oracle/.ssh/known_hosts:15
Matching host key in /home/oracle/.ssh/known_hosts:17
Are you sure you want to continue connecting (yes/no)? yes
2013年 10月 12日 星期六 11:39:08 CST
解决方法：
查看known_hosts 内容，发现'192.1.0.103' 以前指定了其它的服务器，直接删除对应的其它服务器行。
问题解决。
）
还有一个有趣的问题，在RAC1中配置了ssh 对等访问，系统会自动更新到其它的RAC节点中，比如我这里有RAC2；
在单机上先安装一个GRID（ASM),再安装ORACLE.
1.3 安装GRID
【】为独立服务器配置ORACLE GRID INFRASTRUCTURE
安装此项选择，还要选择一个ASM磁盘，是开始时没想到的。后来另外分出两个分区做ASM特征盘，
有点象RAC中的RAC表决盘(或OCR磁盘）。
安装完成GRID后，查看状态如下：
[grid@dg1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE dg1
ora.DATA.dg ora....up.type ONLINE ONLINE dg1
ora....ER.lsnr ora....er.type ONLINE ONLINE dg1
ora....VERY.dg ora....up.type ONLINE ONLINE dg1
ora.asm ora.asm.type ONLINE ONLINE dg1
ora.cssd ora.cssd.type ONLINE ONLINE dg1
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE dg1
ora.ons ora.ons.type OFFLINE OFFLINE
[grid@dg1 ~]$
(红色部分为添加的ASM 磁盘组)
1.3，1.4 两个安装参考：
http://blog.csdn.net/miyatang/article/details/6659839
先前测试的配置日志（http://blog.csdn.net/miyatang/article/details/9979579）
二、数据库配置
2.1 监听/ASM 配置
2.1.1 listener.ora
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
)
)
2.1.2 tnsnames.ora
[oracle@localhost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.102)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
stbdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.0.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbdb)
)
)
把stbdb 部分插入到两个节点的 tnsnames.ora 文件中。
2.1.3 grid 用户下，使用asmca 建立 +DATA,+RECOVERY 两个ASM 磁盘组。
测试查看ASM 磁盘组：
[grid@localhost grid]$ asmcmd
Connected to an idle instance.
ASMCMD> ls
ASMCMD-8102: no connection to ASM; command requires ASM to run
修改grid 用户下的.bash_profile
ORACLE_SID=+ASM; export ORACLE_SID (原为：ORACLE_SID=+ASM1; 此内容为RAC中拷贝而来有两个实例ASM1,ASM2.)
PATH=$ORACLE_HOME/bin:$PATH; export PATH
修改后问题解决：
[grid@localhost grid]$ asmcmd
ASMCMD> ls
CRS/
DATA/
RECOVERY/
ASMCMD> exit
2.2.配置参数：
2.2.1、打开Forced Logging模式
SQL> alter database force logging;
Database altered.
2.2.2
SQL> alter system set log_archive_config='DG_CONFIG=(stbdb,racdb)';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET FAL_SERVER=stbdb;
SQL> alter system set FAL_CLIENT=racdb;
SQL> alter system set standby_file_management=auto;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stbdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbdb' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;
--先设置成不活动的，后面再修改成： ENABLE;
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope=both;
#添加STANDBY LOGFILE:
select group#,THREAD#,l.MEMBERS,l.STATUS from v$log l
GROUP# THREAD# MEMBERS STATUS
1 1 1 2 INACTIVE
2 2 1 2 CURRENT
3 3 2 2 CURRENT
4 4 2 2 ACTIVE
5 5 1 2 ACTIVE
6 6 2 2 ACTIVE
alter database add STANDBY logfile thread 1 group 7 ;
alter database add STANDBY logfile thread 1 group 8 ;
alter database add STANDBY logfile thread 1 group 9 ;
alter database add STANDBY logfile thread 2 group 10 ;
alter database add STANDBY logfile thread 2 group 11 ;
alter database add STANDBY logfile thread 2 group 12 ;
#2.2 生成pfile，并修改。
SQL> create pfile='/tmp/initstbdb.ora' from spfile;
1.修改
*.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
修改后：
*.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'
建立目录 ：
mkdir -p /opt/app/oracle/admin/stbdb/adump
chown oracle:oinstall /opt/app/oracle/admin/stbdb/adump
2.添加以下内容:
*.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'
修改前的 initstbdb.ora
racdb1.__db_cache_size=13623099392
racdb2.__db_cache_size=13623099392
racdb2.__java_pool_size=67108864
racdb1.__java_pool_size=67108864
racdb2.__large_pool_size=67108864
racdb1.__large_pool_size=67108864
racdb1.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=9462349824
racdb1.__pga_aggregate_target=9462349824
racdb2.__sga_target=17582522368
racdb1.__sga_target=17582522368
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb1.__shared_pool_size=3556769792
racdb2.__shared_pool_size=3690987520
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=67108864
*.audit_file_dest='/opt/app/oracle/admin/racdb/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_flashback_retention_target=1440
*.db_name='racdb'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=3221225472000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.fal_client='RACDB'
*.fal_server='DGDB'
racdb2.instance_number=2
racdb1.instance_number=1
*.log_archive_config='DG_CONFIG=(dgdb,racdb)'
*.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
racdb1.memory_max_target=29360128000
*.memory_target=27031240704
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=5000
*.processes=8000
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=100
*.sessions=5000
*.standby_file_management='MANUAL'
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDO_TBS01'
[oracle@localhost dbs]$
修改后的initstbdb.ora
[oracle@localhost dbs]$ cat initstbdb.ora
stbdb.__db_cache_size=13623099392
stbdb.__java_pool_size=67108864
stbdb.__large_pool_size=67108864
stbdb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
stbdb.__pga_aggregate_target=9462349824
stbdb.__sga_target=17582522368
stbdb.__shared_io_pool_size=0
stbdb.__shared_pool_size=3556769792
stbdb.__streams_pool_size=67108864
*.audit_file_dest='/opt/app/oracle/admin/stbdb/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/racdb/controlfile/current.267.812303153','+RECOVERY/racdb/controlfile/current.3283.812303153'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_flashback_retention_target=1440
*.db_name='racdb'
db_unique_name='stbdb'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=900000000000
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbdb)'
*.fal_client='STBDB'
*.fal_server='RACDB1,RACDB2'
stbdb.instance_number=1
*.db_file_name_convert='+DATA/racdb/datafile','+DATA/stbdb/datafile','+DATA/racdb/tempfile','+DATA/stbdb/tempfile'
*.LOG_FILE_NAME_CONVERT='+DATA/racdb/onlinelog','+DATA/stbdb/onlinelog','+RECOVERY/racdb/onlinelog' ,'+RECOVERY/stbdb/onlinelog'
*.log_archive_config='DG_CONFIG=(dgdb,racdb)'
*.log_archive_dest_2='SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
stbdb.memory_max_target=29360128000
*.memory_target=27031240704
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=5000
*.processes=8000
*.remote_listener='stbdb:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=100
*.sessions=5000
*.standby_file_management='AUTO'
thread=1
undo_tablespace='UNDOTBS1'
#2.3创建控制文件 ：
切换两次日志及做两次归档
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter system archive log current;
System altered.
SQL> alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
#2.4创建密码文件
[oracle@ract1 ~]$ orapwd file=/tmp/orapwstbdb.ora password=sa force=y ignorecase=y
#2.5把文件拷贝到 dg1 服务器：
scp /tmp/orapwstbdb.ora dg1:/opt/app/oracle/product/11.2.0/dbs/orapwstbdb.ora
scp /tmp/inittdb.ora dg1:/tmp/inittdb.ora
#2.6 在STANDBY 端
SQL> create spfile from pfile='/tmp/inittdb.ora';
File created.
SQL> startup nomount;
#2.7复制数据库
（执行命令要在PRIMARY 中的某一个节点中进行，不然会出错）
[oracle@dg1 dbs]$ rman target sys/oracle@racdb1 auxiliary sys/oracle@stbdb nocatalog
rman>duplicate target database for standby from active database nofilenamecheck;
三、DATAGUARD 开启
SQL> alter database recover managed standby database disconnect from session;
Database altered.
# 开启STANDBY 数据库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
#启用STANDBY数据库，进入RECOVER模式：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
#启动到只读模式：
alter database open read only;
#转换到REAL-TIME模式下：
--alter database recover managed standby database cancel;
--ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT from session;
四、测试验证
#PRIMARY 端：
SQL>
SQL> select thread#,sequence#,status from v$log;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 57930 CURRENT
1 57929 INACTIVE
2 43251 INACTIVE
2 43252 ACTIVE
1 57928 INACTIVE
2 43253 CURRENT
6 rows selected.
SQL>
#STANDBY 端
SQL> select archived_thread#,archived_seq#,APPLIED_THREAD#,APPLIED_SEQ# from v$archive_dest_status;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
2 43252 2 43252
- 正式生产库，配置双节点的RAC + 单实例的 DATAGUARD
- RAC对单实例的DATAGUARD配置
- RAC (双节点） + 单实例 DATAGUARD 安装遇到的问题处理
- RAC+单实例DATAGUARD 配置
- 主库为oracle 10G ASM RAC,备库为单实例ASM的DATAGUARD
- rac双节点级连dataguard的实施手记备忘
- DataGuard-RAC到单实例
- ORACLE 11G双节点RAC配置单实例DG
- oracle 单实例+文件管理 +dataguard ---------------------》》 oracle rac+asm+ dataguard
- 3节点rac基础上配置goldengate(单实例)
- rac双节点 双dataguard实施
- HA RAC Dataguard的区别
- ORACLE 11g rac+单实例数据库构建dataguard
- oracle单实例通过dataguard 迁移到RAC
- 11g RAC to 单实例 dataguard ASM TO ASM
- 使用dataguard将单实例数据库转换为rac数据库
- oracle rac与oracle单实例做dataguard
- rac和单实例的dg创建
- Zend API：深入 PHP 内核（十四） 返回函数值
- linux下java开启80端口无效分析和解决方案
- SQLiteOpenHelper 创建，更新，打开
- 线程如何关闭测试
- 安卓自测试题——第七期
- 正式生产库，配置双节点的RAC + 单实例的 DATAGUARD
- 利用Log4Net将日志插入至oracle数据库！
- linux shell 自定义函数(定义、返回值、变量作用域）介绍
- Entity Framework 简单查询
- 安卓自测试题——第八期
- 手机的imei号的获取
- LESS详解之混合(Mixins)高级
- java 对象序列化
- Oracle数据库，字段乱码问题
