One instance primary to RAC standbyDataGuard Configuration:
|
|
Primary |
standby |
|
Clusterware |
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit |
11g R2 Grid Infrastructure (11.2.0.4) |
|
Cluster Nodes |
Cltrac1 |
Srvrac1,srvrac2 |
|
DB_UNIQUE_NAME |
Test1 |
Test2 |
|
DB_NAME |
TEST1 |
Test1 |
|
DB_instance |
Test1 |
Test11,test22 |
|
DB_listener |
listener |
Listener2 |
|
DB storage |
ASM |
Linux file sys |
|
ASM diskgroup for DB files |
DATA |
|
|
ORACLE_HOME |
/bee/app/oracle/product/11.2.0/db_1 |
/bee/app/oracle/product/11.2.0/db_1 |
|
OS |
CentOS release 6.4 (Final) |
CentOS release 6.4 (Final) |
1. 主库必须为归档模式，修改主库为归档模式的方法：
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
2. 启用主库的强迫日志功能
SQL> alter database force logging;
3. 当主库添加或删除数据文件时，这些文件也会在备库添加或删除。启用此功能的方法以下：
SQL> alter system set standby_file_management = 'AUTO';
4. 给主库添加standby日志：
alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby01.log' size50M;
alterdatabase add standby logfile '/bee/app/oracle/oradata/test1/standby02.log' size50M;
alter database add standby logfile'/bee/app/oracle/oradata/test1/standby03.log' size 50M;
5. 建密码文件，并且设置参数 REMOTE_LOGIN_PASSWORDFILE 为 EXCLUSIVE 或 SHARED。1般数据库默许就有密码文件，并且此参数默许为 EXECUSIVE。先检查下这两项，如果不是默许，设置方法以下：
SQL>alter system set remote_login_passwordfile=exclusive scope=spfile;
OS> orapwd password=<sys 用户密码>
Scp mvorapwSID
6. 检查数据库的 db_unique_name 参数是不是设置。如果没有，使用 alter system 进行设置：
SQL> show parameter db_unique_name;
SQL> alter system set db_unique_name=some_namescope=spfile;
7. 配置归档位置：
alter system set log_archive_dest_1 ='location=/bee/app/oracle/archivelog valid_for=(all_logfiles, all_roles)db_unique_name=test1';
alter system set log_archive_dest_2 = 'service=test21 asyncvalid_for=(online_logfile,primary_role) db_unique_name=test2';
8. SQL> alter system setfal_server = 'test2';
SQL> alter system set log_archive_config ='dg_config=(test1,test2)';
9. 设置文件转换方式:后面为本地寄存位置
altersystem set DB_FILE_NAME_CONVERT='+DATA/test2/datafile/','/bee/app/oracle/oradata/test1/'scope=spfile;
alter system setLOG_FILE_NAME_CONVERT='+DATA/test2/onlinelog/','/bee/app/oracle/oradata/test1/'scope=spfile;
10. createpfile='/tmp/test2_pfile.ora' from spfile;
11.
1. 我们要用 RMAN 的 duplicate from active database 命令创建备库，需要配置静态监听和 TNS 名,黄色部份是我手动添加的静态监听，其余部份为数据库原来的动态监听，两节点都要添加：
[grid@srvrac2admin]$ cat listener.ora
LISTENER_TT=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_TT)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_TT=ON # line added by Agent
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1522))
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME =/bee/app/oracle/product/11.2.0/db_1)
(SID_NAME = test22)
)
)
启动时要带上监听名：
[grid@srvrac2 admin]$ lsnrctl start LISTENER2
2. 添加tnsnames.ora
从库节点1：
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
从库节点2：
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
主库：
test1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
test21 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = test1)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES = 200)
(DELAY = 5)
)
)
)
红色的网络服务名对应配置log_archive_dest_2中的service
测试方法：
sqlplus sys/oracle@test21 as sysdba
3.
1. 建好了主库的 pfile 后，将其复制到备库服务器的相同位置，使用备库的 SID 修改其名字。需要对 pfile 做以下修改：
根据你备库的配置和文件位置，你可能需要修改AUDIT_FILE_DEST，CONTROL_FILES 和 DISPATCHERS 参数（或许还有其他需要修改的参数）。
LOG_ARCHIVE_DEST_1参数中的 db_unique_name 修改成备库的相应唯1名（这里是 JED2）。
LOG_ARCHIVE_DEST_2参数，修改成主库对应的服务名和数据库唯1名（这里是 JED）。
FAL_SERVER参数修改指向主库的服务名。
增加以下参数：
db_unique_name=JED2
altersystem set standby_file_management = 'AUTO';
db_file_name_convert和 log_file_name_convert。如果主备库的数据文件、日志文件位置不同，需要设置这两个参数。
然后在备库服务器上创建所需目录结构和修改相干文件
2. 如：
[oracle@srvrac1dbs]$ cat test2_pfile.ora
test1.__db_cache_size=883027968
test1.__java_pool_size=23554432
test1.__large_pool_size=30331648
test1.__pga_aggregate_target=673741824
test1.__sga_target=922122547
test1.__shared_io_pool_size=0
test1.__shared_pool_size=203979776
test1.__streams_pool_size=11777216
*.audit_file_dest='/bee/app/oracle/admin/test2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA/test2/controlfile/control01.ctl','+DATA/test2/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'
*.log_file_name_convert='/bee/app/oracle/oradata/test1/','+DATA/test2/datafile/'
*.db_name='test1'
*.db_unique_name='test2'
*.diagnostic_dest='/bee/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=test2XDB)'
*.fal_server='test1'
*.log_archive_config='dg_config=(test1,test2)'
*.log_archive_dest_1='location=+DATA/test2/archivelogvalid_for=(all_logfiles, all_roles) db_unique_name=test2'
*.log_archive_dest_2='service=test1async valid_for=(online_logfile,primary_role) db_unique_name=test1'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1072693248
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3218079744
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
3. 启动备库export ORACLE_SID=test11
startup nomount pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/test2_pfile.ora'
4. 创建备库,在从库 RMAN 恢复：
[oracle@srvrac1~]$ export ORACLE_SID=test11
[oracle@srvrac1 ~]$ rman target sys/oracle@test1 auxiliary sys/oracle@test21
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASENOFILENAMECHECK;
5. 启动重做利用：
sql> alter database recover managed standbydatabase disconnect from session
或实时同步ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENTLOGFILE DISCONNECT FROM SESSION;
6. 测试：
在主库更新：
SQL>insert into test.test_table values (17,'jhpcc');
1 rowcreated.
SQL>commit;
Commitcomplete.
SQL>alter system archive log current;
System altered.
在备库查看是不是同步过来，分析问题看两边的alert.log
sql> alter database recover managed standbydatabase cancel;
sql>alter database open read only;
sql>SQL> select * from test.test_table;
ID NAME
---------- ----------
17 jhpcc
7. 11g已支持活动备库，可让数据库在只读状态下打开，同时启动日志利用:
alter database recover managed standby databasedisconnect
8. createspfile='+DATA/cltdbhz1/spfilecltdbhz1.ora' from pfile='/bee/app/oracle/product/11.2.0/db_1/dbs/cltdbhz1_pfile.ora';
1. [oracle@srvrac2 dbs]$ catinittest22.ora
2. spfile='+DATA/test2/spfiletest2.ora
3. 从库的第2个节点上， $ export ORACLE_SID=test22
$ sqlplus / as sysdba sql> startup mount;
4. srvctl add database -d test2 -ntest1 -o /bee/app/oracle/product/11.2.0/db_1 -p +DATA/test2/spfiletest2.ora -rphysical_standby -a DATA（oracle用户履行）
5. srvctl add instance -d test2 -itest11 -n srvrac1
6. srvctl add instance -d test2 -itest22 -n srvrac2
7. srvctl start database -d test2
8. srvctl modify database -d test2-s mount
9. srvctl status database