首页 > 代码库 > oracle单实例通过dataguard 迁移到RAC
oracle单实例通过dataguard 迁移到RAC
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。一般数据库默认就有密码文件，并且此参数默认为 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.
监听及tnsnames配置：
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 =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.30)(PORT = 1522))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
从库节点2：
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
TEST21 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.31)(PORT = 1522))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
主库：
test1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.205.0.35)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(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 =http://www.mamicode.com/
(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 修改为备库的相应唯一名（这里是 JED2）。
LOG_ARCHIVE_DEST_2参数，修改为主库对应的服务名和数据库唯一名（这里是 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‘;
注册第二个节点到 CRS
1. [oracle@srvrac2 dbs]$ catinittest22.ora
2. spfile=‘+DATA/test2/spfiletest2.ora
3. 从库的第二个节点上， $ 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 –d test2
在第三步startup mount时
由于参数文件是从单实例的主库生成的，作为RAC第二个节点启动时会先后报了几个错：
问题1：
ORA-00304: requested INSTANCE_NUMBER isbusy
解决：
alter system set instance_number=1scope=spfile sid=‘test11‘;
alter system set instance_number=2scope=spfile sid=‘test22‘;
然后重启
问题2：
ORA-01102: cannot mount database inEXCLUSIVE mode
解决:
SQL> show parameter cluster_databas
NAME TYPE VALUE
----------------------------------------------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> alter system setcluster_database=true scope=spfile;
alter system setcluster_database_instances=2 scope=spfile;
System altered.
select instance_name,status from gv$instance;
问题3：
ORA-01620: no public threads are availablefor mounting
Cause: The value of the initializationparameter THREAD is zero, its default value. There are no threads which havebeen publicly enabled, and not mounted.
Action: Shut down the instance, change thevalue of the initialization parameter to a thread which is privately enabledand not mounted. If the database is open in another instance, then a thread maybe publicly enabled.
SQL> show parameter thread
NAME TYPE VALUE
----------------------------------------------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 0
alter system set thread=1 scope=spfilesid=‘test11‘;
alter system set thread=2 scope=spfilesid=‘test22‘;
检查状态：
select * from V$DATAGUARD_STATUSorder by TIMESTAMP;
select STATUS, GAP_STATUS fromV$ARCHIVE_DEST_STATUS where DEST_ID = 2;
select SEQUENCE#, FIRST_TIME,NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = ‘test2‘ order byFIRST_TIME;
select DEST_ID, STATUS,DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID<=2;
Dataguard 主从切换
在 RAC 状态下，主从数据库的切换时主从库只能有一个实例在运行状态。在切换前需关掉其他节点，在切换完成后再启动其他节点。查询主从库状态
sql> select database_role,switchover_status from v$database;
将主库切换到从库状态
sql> alter database commit to switchover to physical standbywithsession shutdown;
如果主库状态为 “sessions active”，则需要在命令后加上 with sessionshutdown语句。状态。
重起原数据库到 mount 状态。
SQL>shutdown immediate;
SQL>startup mount;
在原从库上执行切换命令
sql> alter database commit to switchover to primary withsession shutdown;
SQL>alter database open;
完成主备切换后，在新备库上启用日志应用：
SQL> alter database recover managed standby databaseusing current logfile disconnect from session;
[root@srvrac2~]# srvctl start instance -d test2 -i test22
PRCR-1013: 无法启动资源ora.test2.db
PRCR-1064: 无法在节点 srvrac2上启动资源ora.test2.db
CRS-5017:The resource action "ora.test2.db start" encountered the followingerror:
ORA-01618:redo thread 2 is not enabled - cannot mount
. Fordetails refer to "(:CLSN00107:)" in"/bee/app/11.2.0/grid/log/srvrac2/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674:Start of ‘ora.test2.db‘ on ‘srvrac2‘ failed
ORA-01618:redo thread 2 is not enabled - cannot mount
http://tiany.blog.51cto.com/513694/1415383
alterdatabase add logfile thread 2 (‘+DATA‘) size 50m;
alterdatabase add logfile thread 2 (‘+DATA‘) size 50m;
alterdatabase add logfile thread 2 (‘+DATA‘) size 50m;
alterdatabase enable thread 2;
ORA-01092:ORACLE instance terminated. Disconnection forced
ORA-30013:undo tablespace ‘UNDOTBS1‘ is currently in use
SQL>alter system set db_create_file_dest=‘+DATA‘;
SQL>create undo tablespace undotbs2;
altersystem set undo_tablespace=‘UNDOTBS1‘ scope=spfile sid=‘test11‘;
altersystem set undo_tablespace=‘UNDOTBS2‘ scope=spfile sid=‘test22‘;
oracle单实例通过dataguard 迁移到RAC