rac 配置dg完成版

时间:2023-03-08 16:57:36
rac 配置dg完成版

********************************************************************************************
集群环境下的data guard  主库是rac 备库是单实例 单节点

***************************
主库   rac1   rac2

su - oracle

检查各节点登录情况  crs_stat -t

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ crs_stat -t

[oracle@rac1 ~]$ crsctl  start  crs     启动 crs

[oracle@rac1 ~]$ crsctl  stop   crs     关闭 crs

[oracle@rac1 ~]$ crsctl  check  crs

CSS appears healthy
CRS appears healthy
EVM appears healthy

[oracle@rac1 ~]$ srvctl start asm  -n rac1/rac2   启动 asm  服务

[oracle@rac1 ~]$ srvctl start instance -d  prod  -i prod1

[oracle@rac1 ~]$ srvctl start  instance   -d  prod  -i prod2

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 20 05:35:19 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SYS@ prod1 >select  status from  v$instance ;      检查实例状态

STATUS
------------
OPEN

SYS@ prod1 >show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DG1/prod/controlfile/current.
                                                 260.810115699

SYS@ prod1 > create pfile='/home/oracle/initprod.ora' from spfile;    事先备份spfile 集群环境需要指定路径  不然会放于 asm磁盘组

SYS@ prod1 >shutdown immediate;

***************************
把两个节点 全部关闭 然后启动一个节点到 mount状态

[root@rac2 ~]# srvctl stop instance -d prod -i prod2   关闭节点2

rac1

SYS@ prod1 > shutdown immediate;

[oracle@rac1 dbs]$ ls

initdw.ora     orapw+ASM1      spfileprod1.ora
  ab_+ASM1.dat             hc_+ASM1.dat             init.ora            spfiletemp.ora
  hc_prod1.dat             initprod1.ora  orapwprod1
  init+ASM1.ora            inittmp.ora    snapcf_prod1.f

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 20 05:41:11 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

@  >conn  /as sysdba

Connected to an idle instance.

SYS@ prod1 >startup  pfile='?/dbs/inittmp.ora';

SYS@ prod1 >show  parameter  name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /u01/app/oracle/oradata/orcl,
                                                 +DG1/prod/datafile, /u01/app/o
                                                 racle/oradata/orcl, +DG1/prod/
                                                 tempfile
db_name                              string      prod
db_unique_name                       string      prod
global_names                         boolean     FALSE
instance_name                        string      prod1
lock_name_space                      string
log_file_name_convert                string      /u01/app/oracle/oradata/orcl,
                                                 +DG1/prod/onlinelog, /u01/app/

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
                                                 oracle/oradata/orcl, +RECOVERY
                                                 /prod/onlinelog
service_names                        string      prod

[oracle@rac1 ~]$ mkdir /u01/rman_bak

SYS@ prod1 >alter database create standby controlfile as '/home/oracle/stddb_controlfile.ctl' ;

Database altered.;                          生成备库 控制文件

***************************
rac1   rman备份数据库

[oracle@rac1 ~]$ mkdir -p /u01/rman_bak

[oracle@rac1 ~]$ rman target /

RMAN> run {
     backup  database format '/u01/rman_bak/%d_%s.bak';
          }

***************************
rac1 修改初始化参数文件

修改  归档日志存放路径

主库端(RAC)修改spfile:

添加以下内容:

db_unique_name        不需要  删掉此项
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,racdb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/arch1
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=prod'
LOG_ARCHIVE_DEST_2=
 'SERVICE=racdb LGWR ASYNC   最大性能模式 选择  async
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=racdb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=10
FAL_SERVER=racdb
FAL_CLIENT=prod
prod1.FAL_CLIENT=prod1
prod2.FAL_CLIENT=prod2
*.STANDBY_ARCHIVE_DEST='/u01/arch1'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/racdb','+DG1/prod/datafile','/u01/app/oracle/oradata/racdb','+DG1/prod/tempfile','/u01/app/oracle/oradata/racdb','+DG2/prod/datafile'
LOG_FILE_NAME_CONVERT='/disk1/oradata/racdb','+DG1/prod/onlinelog','/disk2/oradata/racdb','+RECOVERY/prod/onlinelog'
STANDBY_FILE_MANAGEMENT=AUTO

***************************

SYS@ prod1 >select *  from v$log;  查看当前日志组

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         16   52428800          1 NO  CURRENT                 742243 23-MAR-13
         2          1         15   52428800          1 YES INACTIVE                737118 23-MAR-13
         3          2         11   52428800          1 NO  CURRENT                 742247 23-MAR-13
         4          2         10   52428800          1 YES INACTIVE                629292 21-MAR-13

rac1 添加standby日志 (比redo日知组多一组)

alter database add standby logfile thread 1                      线程1  组 5
('+dg1/prod/onlinelog/std_redo01a.log' ,'+recovery/prod/onlinelog/std_redo05.log')   size 50m;

alter database add standby logfile thread 1                      线程1  组 6
('+dg1/prod/onlinelog/std_redo02a.log' ,'+recovery/prod/onlinelog/std_redo06.log')   size 50m;

alter database add standby logfile thread 1                      线程1  组 7
('+dg1/prod/onlinelog/std_redo03a.log' ,'+recovery/prod/onlinelog/std_redo07.log')   size 50m;

alter database add standby logfile thread 2                      线程2  组 8
('+dg1/prod/onlinelog/std_redo04a.log' ,'+recovery/prod/onlinelog/std_redo08.log')   size 50m;
alter database add standby logfile thread 2                      线程2  组 9
('+dg1/prod/onlinelog/std_redo04a.log' ,'+recovery/prod/onlinelog/std_redo09.log')   size 50m;
alter database add standby logfile thread 2                      线程2  组 10
('+dg1/prod/onlinelog/std_redo04a.log' ,'+recovery/prod/onlinelog/std_redo10.log')   size 50m;

alter database add standby logfile thread 2                      线程2  4组
('+dg1','+recovery')  size 50m ;

两个节点 故添加  线程1  线程2

查看添加的standby 日志

SYS@ prod1 >select  group#,thread#,status from  v$standby_log;

GROUP#    THREAD# STATUS
---------- ---------- ----------
         7          1 UNASSIGNED
         8          1 UNASSIGNED
         9          1 UNASSIGNED
        10          1 UNASSIGNED
        11          2 UNASSIGNED
        12          2 UNASSIGNED
        13          2 UNASSIGNED
        14          2 UNASSIGNED

数据库物理结构发生变化  故  重建 standby 控制文件

SYS@ prod1 >alter database  backup controlfile  to trace;

SYS@ prod1 >create spfile='/home/oracle/spfileprod1.ora'  from pfile;

集群环境下的spfile  只有 一句话而已

***************************
rac  1  启动实例

SYS@ prod1 >alter database  create  standby controlfile as '/home/oracle/std_control02.ctl' ;

Database altered.            创建新的控制文件

SYS@ prod1 > startup force  nomount pfile='/home/oracle/initprod.ora';

SYS@ prod1 > create  spfile='+DG1/PROD/spfile.ora'  from  pfile='?/dbs/inittmp.ora';

其实创建spfile  pfile  不用启动数据库 只要进入sqlplus  环境就可以了   呼呼

*********************************************************************************
备库环境修改

[oracle@cuug dbs]$ mkdir /u01/rman_bak

[oracle@cuug dbs]$ mkdir -p /u01/app/oracle/oradata/orcl/

**************************************
拷贝备份文件(修改host文件)

rac1

[oracle@cuug dbs]$ scp  /u01/rman_backup/prod_7.bak    stddb:/u01/rman_backup   数据文件
[oracle@cuug dbs]$ scp  std_control.ctl  stddb: /home/oracle     拷贝控制文件
[oracle@cuug dbs]$ scp  initprod.ora  stddb:$ORACLE_HOME/dbs     初始化参数文件

备库 启动listener

[oracle@orcl ~]$ lsnrctl   start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-MAR-2013 02:14:57

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                23-MAR-2013 02:14:58
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

stddb  修改初始化参数文件

备库端(pfile 文件)

添加以下内容:
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.background_dump_dest='/u01/app/oracle/admin/racdb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/racdb/std_control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/racdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.job_queue_processes=10
*.log_archive_format='arch_%t_%s_%r.log'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=285212672
thread=1
*.undo_management='AUTO'
undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/racdb/udump'
db_unique_name=orcl                 不是集群环境  故添加 db_unique_name  
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,racdb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/disk1/arch_racdb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=racdb'
LOG_ARCHIVE_DEST_2=
 'SERVICE=prod LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=prod'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=5
FAL_SERVER=prod
FAL_CLIENT=racdb
*.STANDBY_ARCHIVE_DEST='/disk1/arch_std'
DB_FILE_NAME_CONVERT='+DG1/prod/datafile','/u01/app/oracle/oradata/racdb','+DG1/prod/tempfile','/u01/app/oracle/oradata/racdb','+DG2/racdb/datafile','/u01/app/oracle/oradata/racdb'
LOG_FILE_NAME_CONVERT='+DG1/prod/onlinelog','/disk1/oradata/racdb','+RECOVERY/prod/onlinelog','/disk2/oradata/racdb'
STANDBY_FILE_MANAGEMENT=AUTO

******************************************************
生成备库 口令文件

[oracle@orcl dbs]$ orapwd file=orapwracdb password=oracle entries=3

[oracle@orcl dbs]$  mkdir  -p /u01/app/oracle/admin/racdb
[oracle@orcl dbs]$  mkdir  bdump  cdump  udump adump

SYS @ orcl > show parameter arch;

SYS @ orcl > show parameter name;

******************************************************
stddb  启动实例

[oracle@orcl dbs]$ export  ORACLE_SID=orcl
[oracle@orcl dbs]$ sqlplus  /  as  sysdba
[oracle@orcl dbs]$ startup nomount;

SYS @ orcl >show parameter   name;        检查  db_name   instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DG1/prod/datafile, /u01/app/o
                                                 racle/oradata/orcl/, +DG1/prod
                                                 /tempfile, /u01/app/oracle/ora
                                                 data/orcl
db_name                              string      prod
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string      +DG1/prod/onlinelog/, /u01/app
                                                 /oracle/oradata/orcl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl

******************************************************

主库修改tnsnames

添加内容如下:

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.170)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =orcl )
    (ur=A) )                                   需要通过rman连接备库 故添加此项
  ))

备库修改tnsnames    
     添加内容如下:
orcl=
 (DESCRIPTION=
  (ADDRESS= (PROTOCOL=tcp)(HOST=192.168.8.170)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=orcl)))

************************************************************************************************************

rman recover

主库

[oracle@rac1 admin]$ rman target /

RMAN> connect auxiliary sys/oracle@orcl   as sysdba;     连接辅助库   备库

RMAN> run {
            duplicate  target database for  standby;
           }

Starting Duplicate Db at 23-MAR-13
using channel ORA_AUX_DISK_1

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

Starting Duplicate Db at 23-MAR-13
using channel ORA_AUX_DISK_1

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

Starting restore at 23-MAR-13

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input filename=/home/oracle/std_control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control01.ctl
Finished restore at 23-MAR-13

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/orcl/temp.263.810625605";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/orcl/system.256.810625497";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/orcl/undotbs1.258.810625499";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/orcl/sysaux.257.810625497";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/orcl/users.259.810625499";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/orcl/example.264.810625615";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/orcl/undotbs2.265.810625709";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/orcl/temp.263.810625605 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-MAR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs1.258.810625499
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux.257.810625497
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example.264.810625615
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman_bak/db_0ho52gtb_1_1_810632107.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman_bak/db_0ho52gtb_1_1_810632107.bak tag=TAG20130321T073506
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
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 /u01/app/oracle/oradata/orcl/system.256.810625497
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users.259.810625499
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/undotbs2.265.810625709
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman_bak/db_0go52gtb_1_1_810632107.bak
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/rman_bak/db_0go52gtb_1_1_810632107.bak tag=TAG20130321T073506
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 23-MAR-13

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

datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/system.256.810625497
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/undotbs1.258.810625499
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/sysaux.257.810625497
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/users.259.810625499
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/example.264.810625615
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=810786701 filename=/u01/app/oracle/oradata/orcl/undotbs2.265.810625709
Finished Duplicate Db at 23-MAR-13

******************************************************
备库 开始接受日志

alter database recover managed standby database disconnect  from session;   这是一个media  recover 的过程