11g RAC to 单实例 dataguard ASM TO ASM

1.RAC 主库环境                                                   Oracle Restart备库环境

hostname           frac01                                          hostname            fdg
hostname           frac02
db_name            f                                               db_name             f
instance_name     f1
instance_name     f2                                               instance_name       fstd
db_unique_name     f                                               db_unique_name      fstd
service_name       f                                               service_name        fstd
netname            f                                               netname             fstd

OS USER : oracle,grid                                              OS USER : oracle,grid  
                        
存储方式：ASM                                                      存储方式：ASM  

2.所有主机hosts文件

#Public IP
192.168.30.171    frac01
192.168.30.172    frac02

#PRIVATE IP
192.168.122.171  frac01-priv
192.168.122.172  frac02-priv

#VIP
192.168.30.178   frac01-vip
192.168.30.179   frac02-vip

#SCAN
192.168.30.180   db-scan


#dg
192.168.10.10     fdg

数据库软件安装不再详述，主库备库的路径参考自己的安装手册，配置DG要注意TNS名称配置与密码文件的权限问题

3.磁盘组环境

主库 DISKGROUP                                                 备库DISKGROUP

OCR                                                                 DATA01
DATA01                                                              FRA
FRA

4.主库主要路径                                                     备库主要路径

+DATA01/f/controlfile/current.260.870886863                         +DATA01/fstd/datafile/users  
+DATA01/f/datafile/users.259.870886787                              +DATA01/fstd/datafile/undotbs1 
+DATA01/f/datafile/undotbs1.258.870886787                           +DATA01/fstd/datafile/sysaux  
+DATA01/f/datafile/sysaux.257.870886785                             +DATA01/fstd/datafile/system  
+DATA01/f/datafile/system.256.870886785                             +DATA01/fstd/controlfile/control01.ctl
+DATA01/f/datafile/undotbs2.264.870886947       

5.添加TNS

  用oracle用户主库两节点添加备库的TNS
                                                                               
fstd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = fstd)
    )
  )

  用oracle用户在备库节点添加主库的TNS
F =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = f)
    )
  )

  
6.复制口令文件

用oracle用户从主库拷贝口令文件到备库      

$scp $ORACLE_HOME/dbs/orapwf1   oracle@fstd:$ORACLE_HOME/dbs/orapwfstd



7.修改参数

sysdba身份在主库一个节点执行

$sqlplus / as sysdba

alter system set log_archive_config='dg_config=(f,fstd)' scope=both  sid='*';

alter system set log_archive_dest_1='location=+FRA valid_for=(all_logfiles,all_roles) db_unique_name=f' scope=both sid='*';

alter system set log_archive_dest_2='service=fstd   valid_for=(online_logfiles,primary_role) db_unique_name=fstd' scope=both sid='*';

alter system set log_archive_dest_state_1='enable' scope=both sid='*';

alter system set log_archive_dest_state_2='enable' scope=both sid='*';

alter system set fal_server='fstd' scope=both ;

alter system set db_file_name_convert='+DATA01/f','+DATA01/fstd' scope=spfile sid='*';

alter system set log_file_name_convert='+DATA01/f','+DATA01/fstd' scope=spfile sid='*';

alter system set standby_file_management='AUTO' scope=both sid='*';    



8.添加监听

grid用户执行备库添加静态监听
#su - grid
$cd $ORACLE_HOME/network/admin
$vi listener.ora
添加以下内容

SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC =
      (SID_NAME = PLSExtProc) 
      (ORACLE_HOME = /u01/app/oracle/11.2/db_1) 
      (PROGRAM = extproc)
    ) 
    (SID_DESC =
      (GLOBAL_DBNAME = fstd)
      (ORACLE_HOME = /u01/app/oracle/11.2/db_1) 
      (SID_NAME = fstd)
    ) 
  ) 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521)) 
    )
  )
  
重启监听程序


9.补充路径
 
备库oracle用户执行

$mkdir -p /u01/app/oracle/admin/fstd/adump

备库grid用户执行

#su - grid
$asmcmd
ASMCMD>mkdir fstd
ASMCMD>cd fstd
ASMCMD>mkdir controlfile

10.启动备库实例

启动实例nomount状态

备库oracle用户执行

$cd $ORACLE_HOME/dbs
$vi initfstd.ora

添加一行内容即可

 db_name=f

$sqlplus / as sysdba
SQL>STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2.7726E+10 bytes
Fixed Size                  2266464 bytes
Variable Size            1.4227E+10 bytes
Database Buffers         1.3489E+10 bytes
Redo Buffers                7606272 bytes


11.克隆数据库

主库oracle用户执行              
                                                                 
$rman target / auxiliary sys/oracle@fstd

RMAN>duplicate target database for standby from active database
spfile
parameter_value_convert 'f','fstd','f','fstd'
set db_unique_name='fstd'
set db_file_name_convert='+DATA01/f/datafile','+DATA01/fstd/datafile','+DATA01/f/tempfile','+DATA01/fstd/tempfile'
set log_file_name_convert='+DATA01/f','+DATA01/fstd'
set control_files='+DATA01/fstd/controlfile/control01.ctl'
set log_archive_max_processes='5'
set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
set fal_server='f'
SET cluster_database='false'
set db_create_file_dest  = '+DATA01'
set db_recovery_file_dest_size = '10000M'
set db_recovery_file_dest  = '+FRA'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(f,fstd)'
set log_archive_dest_2='service=f valid_for=(online_logfiles,primary_role) db_unique_name=f'
set log_archive_dest_1='location=+FRA  valid_for=(all_logfiles,all_roles) db_unique_name=fstd'
set audit_file_dest ='/u01/app/oracle/admin/fstd/adump'
set diagnostic_dest='/u01/app/oracle'
reset REMOTE_LISTENER
reset local_listener;                        

开始输出以下内容
Starting Duplicate Db at 2015-02-05 23:15:26
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=477 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/11.2/db_1/dbs/orapwf1' auxiliary format 
 '/u01/app/oracle/11.2/db_1/dbs/orapwfstd'   targetfile 
 '+DATA01/f/spfilef.ora' auxiliary format 
 '/u01/app/oracle/11.2/db_1/dbs/spfilefstd.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/11.2/db_1/dbs/spfilefstd.ora''";
}

.....................省略

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=870909558 file name=+DATA01/fstd/datafile/system.260.870909377
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=870909558 file name=+DATA01/fstd/datafile/sysaux.259.870909473
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=870909558 file name=+DATA01/fstd/datafile/undotbs1.258.870909537
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=870909558 file name=+DATA01/fstd/datafile/users.272.870909557
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=870909558 file name=+DATA01/fstd/datafile/undotbs2.257.870909553
Finished Duplicate Db at 2015-02-05 23:19:36

出现以上输出后表示数据库克隆执行成功


12.编辑参数文件

克隆后备库的参数文件中有f1,f2实例名开头的参数，将f1,f2开头的参数改为以fstd开头的参数

例如

将
f1.__pga_aggregate_target=11140071424
f2.__pga_aggregate_target=11140071424
f1.__sga_target=16710107136
f2.__sga_target=16710107136
f1.__shared_io_pool_size=0
f2.__shared_io_pool_size=0

改为
zfstd.__pga_aggregate_target=11140071424
zfstd.__sga_target=16710107136
zfstd.__shared_io_pool_size=0
zfstd.__shared_pool_size=2013265920

重复的参数可以去掉，UNDO 表空间只保留一个即可     

备库参数示例

fstd.__db_cache_size=13488881664

fstd.__java_pool_size=402653184

fstd.__large_pool_size=671088640
fstd.__oracle_base='/u01/app/grid'
#ORACLE_BASE set from environment

fstd.__pga_aggregate_target=11140071424

fstd.__sga_target=16710107136

fstd.__shared_io_pool_size=0

fstd.__shared_pool_size=2013265920

fstd.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/fstd/adump'
*.audit_trail='db'
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.control_files='+DATA01/fstd/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_file_name_convert='+DATA01/f/datafile','+DATA01/fstd/datafile','+DATA01/f/tempfile','+DATA01/fstd/tempfile'
*.db_name='f'
*.db_recovery_file_dest_size=10485760000
*.db_recovery_file_dest='+FRA'
*.db_unique_name='fstd'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=fstdstdXDB)'
*.fal_server='f'
fstd.instance_number=1
*.log_archive_config='dg_config=(f,fstd)'
*.log_archive_dest_1='location=+FRA  valid_for=(all_logfiles,all_roles) db_unique_name=fstd'
*.log_archive_dest_2='service=f valid_for=(online_logfiles,primary_role) db_unique_name=f'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='log%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA01/f','+DATA01/fstd'
*.memory_target=27810332672
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


13.添加Standby Redolog

执行规则为 GROUP+1

如：实例1 即（thread 1） 有2组（group1,group2）redolog  ,则他的standby redolog  为2+1组，即2组

主库
 
SQL> select group#,thread#,sequence#,bytes,blocksize,members from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS
---------- ---------- ---------- ---------- ---------- ----------
         1          1        157  524288000        512          1
         2          1        158  524288000        512          1
         3          2        131  524288000        512          1
         4          2        130  524288000        512          1
         

备库执行


SQL>alter database add standby logfile group 5  '+DATA01/fstd/onlinelog/STANDBYRD01_1.LOG' size 524288000;
SQL>alter database add standby logfile group 6  '+DATA01/fstd/onlinelog/STANDBYRD02_1.LOG' size 524288000;
SQL>alter database add standby logfile group 7  '+DATA01/fstd/onlinelog/STANDBYRD03_1.LOG' size 524288000;
SQL>alter database add standby logfile group 8  '+DATA01/fstd/onlinelog/STANDBYRD04_1.LOG' size 524288000;
SQL>alter database add standby logfile group 9  '+DATA01/fstd/onlinelog/STANDBYRD05_1.LOG' size 524288000;
SQL>alter database add standby logfile group 10 '+DATA01/fstd/onlinelog/STANDBYRD06_1.LOG' size 524288000;     



14.开启实时应用

备库执行

SQL>alter database recover managed standby database using current logfile disconnect from session;

查看备库警告日志，成功应用日志

RFS[4]: Assigned to RFS process 17543
RFS[4]: Opened log for thread 1 sequence 23 dbid -1858276529 branch 870886866
Archived Log entry 8 added for thread 1 sequence 23 rlc 870886866 ID 0x913cc74e dest 2:
Thu Feb 05 23:25:04 2015
alter database add standby logfile group 5  '+DATA01/zfstd/onlinelog/STANDBYRD01_1.LOG' size 524288000
Completed: alter database add standby logfile group 5  '+DATA01/zfstd/onlinelog/STANDBYRD01_1.LOG' size 524288000
alter database add standby logfile group 6  '+DATA01/zfstd/onlinelog/STANDBYRD02_1.LOG' size 524288000
Completed: alter database add standby logfile group 6  '+DATA01/zfstd/onlinelog/STANDBYRD02_1.LOG' size 524288000
alter database add standby logfile group 7  '+DATA01/zfstd/onlinelog/STANDBYRD03_1.LOG' size 524288000
Thu Feb 05 23:25:19 2015
Completed: alter database add standby logfile group 7  '+DATA01/zfstd/onlinelog/STANDBYRD03_1.LOG' size 524288000
alter database add standby logfile group 8  '+DATA01/zfstd/onlinelog/STANDBYRD04_1.LOG' size 524288000
Completed: alter database add standby logfile group 8  '+DATA01/zfstd/onlinelog/STANDBYRD04_1.LOG' size 524288000
alter database add standby logfile group 9  '+DATA01/zfstd/onlinelog/STANDBYRD05_1.LOG' size 524288000
Completed: alter database add standby logfile group 9  '+DATA01/zfstd/onlinelog/STANDBYRD05_1.LOG' size 524288000
alter database add standby logfile group 10 '+DATA01/zfstd/onlinelog/STANDBYRD06_1.LOG' size 524288000