rac+dg搭建

最新推荐文章于 2023-12-20 09:31:15 发布

cisi5452

最新推荐文章于 2023-12-20 09:31:15 发布

阅读量149

点赞数

文章标签：网络数据库

#### rac(asm) + single physcial standby

1. 在rac主库进行全备，以及创建standby所需的controlfile（在任何一个节点执行均可）

---首先在rac主机启用force loggin
alter database FORCE LOGGING;

---进行rman全库备份

[oracle@db203 data]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue May 17 18:05:28 2011

connected to target database: SIGNDB (DBID=3223446298)

RMAN> backup database format '/data/backup/signdb_full%u_%s_%p';

Starting backup at 17-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1053 instance=signdb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+ORADATA/signdb/datafile/system.256.750888807
input datafile fno=00003 name=+ORADATA/signdb/datafile/sysaux.257.750888807
input datafile fno=00002 name=+ORADATA/signdb/datafile/undotbs1.258.750888807
input datafile fno=00005 name=+ORADATA/signdb/datafile/undotbs2.264.750888881
input datafile fno=00004 name=+ORADATA/signdb/datafile/users.259.750888807
channel ORA_DISK_1: starting piece 1 at 17-MAY-11
channel ORA_DISK_1: finished piece 1 at 17-MAY-11
piece handle=/data/backup/signdb_full0rmcisjc_27_1 tag=TAG20110517T180531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-MAY-11
channel ORA_DISK_1: finished piece 1 at 17-MAY-11
piece handle=/data/backup/signdb_full0smcisjr_28_1 tag=TAG20110517T180531 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-MAY-11

SQL> alter database create standby controlfile as '/data/backup/standby.ctl';

Database altered.

SQL>

2. 传输备份文件和standby controlfile 到standby主机

可以使用scp或ftp的方式，均可。

3. 在standby主机创建密码文件

cd $ORACLE_HOME/dbs
orapwd file=orapwSIGNDB password=signdb ( 此处密码必须跟rac主机的密码相同）

4.   手工编辑standby pfile，并启动实例到nomount状态

   ---在rac主机生成pfile，然后手工拷贝到standby主机进行编辑
   ---启动pfile之前，需要先根据pfile的内容创建相应的目录，如下：

   mkdir -p /oracle/product/admin/signdb/adump
   mkdir -p /oracle/product/admin/signdb/bdump
   mkdir -p /oracle/product/admin/signdb/cdump
   mkdir -p /oracle/product/admin/signdb/udump

pfile内容如下：

*.audit_file_dest='/oracle/product/admin/signdb/adump'
*.background_dump_dest='/oracle/product/admin/signdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata2/signdb/oradata/signdb.ctl'
*.core_dump_dest='/oracle/product/admin/signdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='signdb'
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='+ORADATA/signdb/datafile','/oradata2/signdb/oradata','+ORADATA/signdb/tempfile','/oradat2/signdb/tempfile'
*.db_unique_name='signdb_standby'
fal_client='signdb_standby'
*.FAL_SERVER='signdb1','signdb2'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(signdb_standby,signdb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata1/standby/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=signdb_standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=signdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=signdb'
*.LOG_FILE_NAME_CONVERT='+ORADATA/signdb/onlinelog','/oradata2/signdb/onlinelog'
*.open_cursors=300
*.pga_aggregate_target=891289600
*.processes=150
*.remote_login_passwordfile='exclusive'
*.service_names='racdb_standby'
*.sga_target=2684354560
*.STANDBY_FILE_MANAGEMENT='AUTO'
racdb1.thread=1
*.undo_management='AUTO'
racdb1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/product/admin/signdb/udump'
STANDBY_ARCHIVE_DEST='/data/arch1'

---根据pfile创建spfile
---启动standby到nomount状态
[oracle@db205 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 17 08:29:03 2011

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2684354560 bytes
Fixed Size                  2098688 bytes
Variable Size             570427904 bytes
Database Buffers         2097152000 bytes
Redo Buffers               14675968 bytes
SQL>

5. 在rac主机进行复制，操作如下：

[oracle@db203 dbs]$ rman target / auxiliary sys/signdb@signdb_standby

Recovery Manager: Release 10.2.0.5.0 - Production on Tue May 17 20:09:20 2011

connected to target database: SIGNDB (DBID=3223446298)
connected to auxiliary database: SIGNDB (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 17-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=154 devtype=DISK

contents of Memory Script.:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 17-MAY-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=/data/backup/standby.ctl
output filename=/data/backup/standby.ctl
Finished restore at 17-MAY-11

sql statement: alter database mount standby database

contents of Memory Script.:
{
   set newname for tempfile 1 to
"/oradat2/signdb/tempfile/temp.263.750888865";
   switch clone tempfile all;
   set newname for datafile 1 to
"/oradata2/signdb/oradata/system.256.750888807";
   set newname for datafile 2 to
"/oradata2/signdb/oradata/undotbs1.258.750888807";
   set newname for datafile 3 to
"/oradata2/signdb/oradata/sysaux.257.750888807";
   set newname for datafile 4 to
"/oradata2/signdb/oradata/users.259.750888807";
   set newname for datafile 5 to
"/oradata2/signdb/oradata/undotbs2.264.750888881";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /oradat2/signdb/tempfile/temp.263.750888865 in control file

executing command: SET NEWNAME

Starting restore at 17-MAY-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata2/signdb/oradata/system.256.750888807
restoring datafile 00002 to /oradata2/signdb/oradata/undotbs1.258.750888807
restoring datafile 00003 to /oradata2/signdb/oradata/sysaux.257.750888807
restoring datafile 00004 to /oradata2/signdb/oradata/users.259.750888807
restoring datafile 00005 to /oradata2/signdb/oradata/undotbs2.264.750888881
channel ORA_AUX_DISK_1: reading from backup piece /data/backup/0vmcj2nv_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/data/backup/0vmcj2nv_1_1 tag=TAG20110517T195023
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 17-MAY-11

contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=9 stamp=751370971 filename=/oradata2/signdb/oradata/system.256.750888807
datafile 2 switched to datafile copy
input datafile copy recid=10 stamp=751370971 filename=/oradata2/signdb/oradata/undotbs1.258.750888807
datafile 3 switched to datafile copy
input datafile copy recid=11 stamp=751370971 filename=/oradata2/signdb/oradata/sysaux.257.750888807
datafile 4 switched to datafile copy
input datafile copy recid=12 stamp=751370971 filename=/oradata2/signdb/oradata/users.259.750888807
datafile 5 switched to datafile copy
input datafile copy recid=13 stamp=751370971 filename=/oradata2/signdb/oradata/undotbs2.264.750888881
Finished Duplicate Db at 17-MAY-11

RMAN>

6. 在rac主机上操作，创建standby logfile（为了切换，故需要是（3+1）x2 个logfile）

----创建standby log

SQL> alter database add standby logfile thread 1 group 7 '/oradata2/signdb/onlinelog/group_7.log' size 50M;
alter database add standby logfile thread 1 group 8 '/oradata2/signdb/onlinelog/group_8.log' size 50M;
alter database add standby logfile thread 1 group 9 '/oradata2/signdb/onlinelog/group_9.log' size 50M;
alter database add standby logfile thread 1 group 10 '/oradata2/signdb/onlinelog/group_10.log' size 50M;
alter database add standby logfile thread 2 group 11 '/oradata2/signdb/onlinelog/group_11.log' size 50M;
alter database add standby logfile thread 2 group 12 '/oradata2/signdb/onlinelog/group_12.log' size 50M;
alter database add standby logfile thread 2 group 13 '/oradata2/signdb/onlinelog/group_13.log' size 50M;
alter database add standby logfile thread 2 group 14 '/oradata2/signdb/onlinelog/group_14.log' size 50M;
Database altered.

SQL>
Database altered.

SQL>

Database altered.

SQL>
SQL>
SQL>
SQL>

7. 修改rac主机的pfile（随后根据pfile创建spfile）

修改后，rac主机pfile如下：

--create pfile='/oracle/rac_pfile.ora' from spfile;
--vi /oracle/rac_pfile.ora
--添加如下内容到 rac_pfile.ora中

#add below parameter for standy
*.service_names=signdb
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(signdb,signdb_standby)'
##*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=signdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=racdb_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=signdb_standby'
*.FAL_SERVER='signdb_standby'
*.STANDBY_ARCHIVE_DEST='/oradata1/standby/arch'
*.FAL_CLIENT=signdb1,signdb2
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/oradata2/signdb/oradata','+ORADATA/signdb/datafile','/oradata2/signdb/tempfile','+ORADATA/signdb/tempfile'
*.LOG_FILE_NAME_CONVERT='/oradata2/signdb/onlinelog','+ORADATA/signdb/onlinelog'

---所有的rac节点pfile内容相同

8. 修改rac主机的tnsnames.ora

--在tnsnmaes.ora中添加如下内容(添加standby主机的信息）:

signdb_standby =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.205)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = signdb)
    )
)

--所有rac主机的tnsnames.ora配置相同

9. 修改standby主机的listener.ora, /etc/hosts

内容如下：

SID_LIST_LISTENER =
(SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = signdb)
      (ORACLE_HOME = /oracle/product/10.2/db)
      (SID_NAME = signdb)
    )
     (SID_DESC =
      (GLOBAL_DBNAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/product/10.2/db)
      (SID_NAME = PLSExtProc)
    )
)
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.15.205)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

/etc/hosts中加入如下信息：
192.168.15.160 db203
192.168.15.203 db203-vip
192.168.15.161 db204
192.168.15.204 db204-vip

10. 启动rac主机，进行standby同步

---根据前面修改的pfile，启动rac主机
---在standby主机启动MRP进程，进行同步，命令如下：

alter database recover managed standby database disconnect from session;
select process,status from v$managed_standby;

11. 测试同步情况

在rac端：手工切换redo

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /data/arch2
Oldest online log sequence     20
Next log sequence to archive   21
Current log sequence           21
SQL> alter system switch logfile;

System altered.

SQL>

在rac每个节点检查：

col DEST_NAME format a20
select dest_name,status,error from v$archive_dest;

--检查standby alert log，如下：
Tue May 17 21:17:15 CST 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Successfully opened standby log 12: '/oradata2/signdb/onlinelog/group_12.log'
Tue May 17 21:18:36 CST 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 8: '/oradata2/signdb/onlinelog/group_8.log'
Tue May 17 21:18:41 CST 2011
Media Recovery Log /oradata1/standby/arch/1_21_750888861.dbf
Media Recovery Log /oradata1/standby/arch/2_21_750888861.dbf
Media Recovery Waiting for thread 2 sequence 22 (in transit)

12. 同步成功，rac+dg搭建完毕！

########### 给standby添加temp文件

SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）
alter tablespace temp add tempfile '/oradata/racdb/tempfile/temp.271.719166789' reset 100M;
--从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13048648/viewspace-758125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13048648/viewspace-758125/