Oracle Dataguard（主库为双节点集群）配置详解（3）：配置主库
Oracle Dataguard（主库为双节点集群）配置详解（3）：配置主库一、开启归档
-- 查看主库归档状态
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 5
-- 停库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
-- 启动数据库到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area626327552 bytes
Fixed Size 2255832 bytes
Variable Size 297796648 bytes
Database Buffers 322961408 bytes
Redo Buffers 3313664 bytes
Database mounted.
-- 修改数据库为归档模式
SQL> alter database archivelog;
Database altered.
-- 打开数据库
SQL> alter database open;
Database altered.
-- 查看归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
二、开启欺压日记
SQL> alter database force logging;
Database altered.
三、添加 standby 日记
创建主库的standby日记文件，每个实例的组数要比logfile多一组。
1、查看主库的日记信息
-- 查看日志组
select * from v$log;
-- 查看日志文件
select member from v$logfile;
SQL> select * from v$log;
GROUP# THREAD#SEQUENCE# BYTESBLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 11104857600 512 2 NOCURRENT 986498 09-JAN-25 2.8147E+14
2 1 0104857600 512 2 YES UNUSED 0 0
3 1 0104857600 512 2 YES UNUSED 0 0
4 2 9104857600 512 2 NOCURRENT 986588 09-JAN-25 2.8147E+14
5 2 0104857600 512 2 YES UNUSED 0 0
6 2 0104857600 512 2 YES UNUSED 0 0
6 rows selected.
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------
+DATA/hisdb/onlinelog/group_1.266.1189965499
+BAK/hisdb/onlinelog/group_1.260.1189965507
+DATA/hisdb/onlinelog/group_2.265.1189965519
+BAK/hisdb/onlinelog/group_2.259.1189965527
+DATA/hisdb/onlinelog/group_3.262.1189965537
+BAK/hisdb/onlinelog/group_3.275.1189965547
+DATA/hisdb/onlinelog/group_4.261.1189965557
+BAK/hisdb/onlinelog/group_4.276.1189965567
+DATA/hisdb/onlinelog/group_5.272.1189965577
+BAK/hisdb/onlinelog/group_5.277.1189965587
+DATA/hisdb/onlinelog/group_6.273.1189965595
+BAK/hisdb/onlinelog/group_6.278.1189965603
12 rows selected.
2、添加 standby log
添加standby log必须和redo log巨细一样，数量为：standby logfile=(1+logfile组数)*thread=(1+3)*2=8。
alter database add standby logfile thread 1 group 11 size 100m;
alter database add standby logfile thread 1 group 12 size 100m;
alter database add standby logfile thread 1 group 13 size 100m;
alter database add standby logfile thread 1 group 14 size 100m;
alter database add standby logfile thread 2 group 15 size 100m;
alter database add standby logfile thread 2 group 16 size 100m;
alter database add standby logfile thread 2 group 17 size 100m;
alter database add standby logfile thread 2 group 18 size 100m;
3、重新查看日记信息
SQL> select * from v$log;
GROUP# THREAD#SEQUENCE# BYTESBLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 11104857600 512 2 NOCURRENT 986498 09-JAN-25 2.8147E+14
2 1 0104857600 512 2 YES UNUSED 0 0
3 1 0104857600 512 2 YES UNUSED 0 0
4 2 9104857600 512 2 NOCURRENT 986588 09-JAN-25 2.8147E+14
5 2 0104857600 512 2 YES UNUSED 0 0
6 2 0104857600 512 2 YES UNUSED 0 0
6 rows selected.
SQL> select * from v$Standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTESBLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAS
T_CHANGE# LAST_TIME---------- ---------------------------------------- ---------- ---------------
11 UNASSIGNED 1 0104857600 512 0 YES UNASSIGNED
12 UNASSIGNED 1 0104857600 512 0 YES UNASSIGNED
13 UNASSIGNED 1 0104857600 512 0 YES UNASSIGNED
14 UNASSIGNED 1 0104857600 512 0 YES UNASSIGNED
15 UNASSIGNED 2 0104857600 512 0 YES UNASSIGNED
16 UNASSIGNED 2 0104857600 512 0 YES UNASSIGNED
17 UNASSIGNED 2 0104857600 512 0 YES UNASSIGNED
18 UNASSIGNED 2 0104857600 512 0 YES UNASSIGNED
8 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------------
+DATA/hisdb/onlinelog/group_1.266.1189965499
+BAK/hisdb/onlinelog/group_1.260.1189965507
+DATA/hisdb/onlinelog/group_2.265.1189965519
+BAK/hisdb/onlinelog/group_2.259.1189965527
+DATA/hisdb/onlinelog/group_3.262.1189965537
+BAK/hisdb/onlinelog/group_3.275.1189965547
+DATA/hisdb/onlinelog/group_4.261.1189965557
+BAK/hisdb/onlinelog/group_4.276.1189965567
+DATA/hisdb/onlinelog/group_11.271.1189966083
+BAK/hisdb/onlinelog/group_11.266.1189966091
+DATA/hisdb/onlinelog/group_12.270.1189966101
+BAK/hisdb/onlinelog/group_12.265.1189966111
+DATA/hisdb/onlinelog/group_13.269.1189966121
+BAK/hisdb/onlinelog/group_13.263.1189966129
+DATA/hisdb/onlinelog/group_14.268.1189966139
+BAK/hisdb/onlinelog/group_14.262.1189966147
+DATA/hisdb/onlinelog/group_5.272.1189965577
+BAK/hisdb/onlinelog/group_5.277.1189965587
+DATA/hisdb/onlinelog/group_6.273.1189965595
+BAK/hisdb/onlinelog/group_6.278.1189965603
+DATA/hisdb/onlinelog/group_15.274.1189966155
+BAK/hisdb/onlinelog/group_15.281.1189966167
+DATA/hisdb/onlinelog/group_16.275.1189966175
+BAK/hisdb/onlinelog/group_16.282.1189966183
+DATA/hisdb/onlinelog/group_17.276.1189966193
+BAK/hisdb/onlinelog/group_17.283.1189966201
+DATA/hisdb/onlinelog/group_18.277.1189966209
+BAK/hisdb/onlinelog/group_18.284.1189966215
28 rows selected.
四、配置静态监听
1、配置 listener.ora 文件
切换到grid用户，在$ORACLE_HOME/network/admin/listener.ora文件中添加如下内容：
，GLOBAL_DBNAME配置为db_unique_name，SID_NAME配置为该节点ORACLE_SID
$ cd $ORACLE_HOME/network/admin
$ ls
endpoints_listener.oralistener2501091PM3305.baklistener.oralistener.ora.bak.rac01samplesshrept.lstsqlnet.ora
# 节点1
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = hisdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = his1)
)
)
# 节点2
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = hisdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = his2)
)
)
配置完成后使用如下下令重启监听：
lsnrctl stop
lsnrctl start
2、配置 tnsnames.ora 文件
切换到 oracle用户，在$ORACLE_HOME/network/admin/tnsnames.ora文件中添加如下内容。
$ cd $ORACLE_HOME/network/admin
$ ls
samplesshrept.lsttnsnames.ora
# 节点1与节点2都需要配置，且内容相同
# 其中 hisdb、hisdb1、hisdb2 的 host 配置分别为 rac-scan、节点1与节点2的IP地址，service_name配置为listener.ora文件中的GLOBAL_DBNAME也就是主库的db_unique_name
# hisdbdg 的 service_name 为备库 db_unique_name
$ vi tnsnames.ora
hisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisdb)
)
)
hisdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisdb)
(SID = his1)
)
)
hisdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisdb)
(SID = his2)
)
)
dghisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisdbdg)
)
)
3、测试连接
tnsping hisdb
tnsping hisdb1
tnsping hisdb2
tnsping hisdbdg
五、修改主库的初始化参数
在节点1实行如下操作：
1、根据 spfile 生成 pfile 文件
SQL> create pfile='/home/oracle/inithis.ora' from spfile;
File created.
2、修改 pfile 文件的内容如下
$ vi /home/oracle/inithis.ora
his2.__db_cache_size=385875968
his1.__db_cache_size=369098752
his1.__java_pool_size=4194304
his2.__java_pool_size=4194304
his1.__large_pool_size=8388608
his2.__large_pool_size=8388608
his1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
his2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
his1.__pga_aggregate_target=209715200
his2.__pga_aggregate_target=209715200
his1.__sga_target=629145600
his2.__sga_target=629145600
his1.__shared_io_pool_size=0
his2.__shared_io_pool_size=0
his2.__shared_pool_size=222298112
his1.__shared_pool_size=239075328
his1.__streams_pool_size=0
his2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/hisdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/hisdb/controlfile/current.260.1189953459','+BAK/hisdb/controlfile/current.256.1189953459'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='hisdb'
*.db_recovery_file_dest='+BAK'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisXDB)'
his2.instance_number=2
his1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=209715200
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=629145600
his2.thread=2
his1.thread=1
his1.undo_tablespace='UNDOTBS1'
his2.undo_tablespace='UNDOTBS2'
# 在参数文件中添加以下内容：
*.db_unique_name='hisdb' # 主数据库的唯一名，默认和数据库名一致
*.log_archive_config='dg_config=(hisdb,hisdbdg)' # 主库和备库在tnsname.ora中的连接名
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=hisdb' # 主数据库的唯一名
*.log_archive_dest_2='service=hisdbdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=hisdbdg' # 备用数据库的唯一名
*.db_file_name_convert='/usr/local/oradata/hisdbdg/','+DATA/hisdb/datafile/'
# 格式：
# 主库配置格式： *.db_file_name_convert= 备用数据库数据文件目录，主数据库数据文件目录
# 备库配置格式： *.db_file_name_convert= 主数据库数据文件目录，备用数据库数据文件目录
*.log_file_name_convert='/usr/local/oradata/hisdbdg/','+DATA/hisdb/onlinelog/'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
# fal_client用于接受日志，fal_server用于发送日志。
# 也即无论是主库或备库，fal_server=对方，fal_client=自己
# 主库设置如下
*.fal_server='hisdbdg'
*.fal_client='hisdb'
# 备库设置如下
# *.fal_server='hisdb'
# *.fal_client='hisdbdg'
3、重启数据库到 nomount，根据修改后的 pfile 文件重新生成 spfile 参数文件
-- 在节点1和节点2上关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-- 使用修改后的参数文件启动数据库到nomount
SQL> startup nomount pfile='/home/oracle/inithis.ora'
ORACLE instance started.
Total System Global Area626327552 bytes
Fixed Size 2255832 bytes
Variable Size 297796648 bytes
Database Buffers 322961408 bytes
Redo Buffers 3313664 bytes
-- 根据修改后的 pfile 文件重新生成 spfile 参数文件
SQL> create spfile='+DATA/hisdb/spfilehis.ora' from pfile='/home/oracle/inithis.ora';
File created.
4、重新启动数据库（两个节点同时操作）
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area626327552 bytes
Fixed Size 2255832 bytes
Variable Size 297796648 bytes
Database Buffers 322961408 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
六、复制主库暗码文件至备库
备库修改暗码文件名为orapwhisdg，备库的实例名为hisdg。
$ scp orapwhis1 oracle@racdg:/usr/local/oracle/product/11.2.0/db_1/dbs/orapwhisdg
oracle@racdg's password: 100% 1536 1.5KB/s 00:00
# 在备库查看密码文件
$ pwd
/usr/local/oracle/product/11.2.0/db_1/dbs
$ ll
总用量 8
-rw-r--r--. 1 oracle oinstall 2851 5月15 2009 init.ora
-rw-r-----1 oracle oinstall 1536 1月10 14:57 orapwhisdg
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]