两台Exadata搭建RAC+DG

客户将上Exadata，在两台Exadata RAC上建了十几个库，分别做为主备库。已由其他同事搭建（使用EM 12C），目前em 12c不让用了，没有办法。上周使用sqlplus搭建最后一套DG.

两台Exadata上RAC已完成，并在主库上建好库，配置DG步骤如下（Oracle Linux + ORACLE 11.2.0.4）：

1.主库开启归档、强制写日志

[grid@dm02db01 ~]$ srvctl stop database -d DDCPRD
[grid@dm02db01 ~]$ srvctl start instance -d DDCPRD -i DDCPRD1 -o MOUNT
SQL> alter database archivelog;
SQL> alter database force logging;
SQL> shutdown immediate
[grid@dm02db01 ~]$ srvctl start database -d DDCPRD

2.配置tnsnames，主备库都添加

备库：
DDCPRD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm02-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DDCPRD)
    )
  )
主库：
DDCPRD_STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DDCPRD)                                          #可以自己定，比如DDCPRD_SATANDBY等对应参数SERVER_NAMES
    )
  )

3.配置主库参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=DDCPRD' sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DDCPRD_STANDBY LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE NET_TIMEOUT=30 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DDCPRD_STANDBY' sid='*';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DDCPRD,DDCPRD_STANDBY)' sid='*';
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO sid='*';
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','+DATA' scope=spfile sid='*';
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA','+DATA','+RECO','+RECO' scope=spfile sid='*';
说明：最后两个参数在备库配置，用于以后的主备切换

4.主库添加standby redo log（最大性能模式可以不添加，但oracle建议添加）,组数比online redo log多1

SQL> alter database add standby logfile thread 1 group 5 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 1 group 6 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 1 group 7 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 8 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 9 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 10 ('+DATA','+RECO') size 500M;

5.将主库的pfile、密码及备份文件传至备库

6.备库创建目录

节点1：
[oracle@dm01db01 ~]$ mkdir -p /u01/app/oracle/admin/DDCPRD_STANDBY/adump
[oracle@dm01db01 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/ddcprd_standby/DDCPRD1/trace
[oracle@dm01db01 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/ddcprd_standby/DDCPRD1/cdump
节点2：
[oracle@dm01db02 ~]$ mkdir -p /u01/app/oracle/admin/DDCPRD_STANDBY/adump
[oracle@dm01db02 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/ddcprd_standby/DDCPRD2/trace
[oracle@dm01db02 ~]$ mkdir -p /u01/app/oracle/diag/rdbms/ddcprd_standby/DDCPRD2/cdump

7.在备库的一个节点上，修改参数文件，并启动数据库到nomount

[oracle@dm01db01 ~]$ vi /home/oracle/initDDCPROD1.ora
DDCPRD2.__db_cache_size=33285996544
DDCPRD1.__db_cache_size=33285996544
DDCPRD2.__java_pool_size=1610612736
DDCPRD1.__java_pool_size=1610612736
DDCPRD2.__large_pool_size=1073741824
DDCPRD1.__large_pool_size=1073741824
DDCPRD2.__pga_aggregate_target=29527900160
DDCPRD1.__pga_aggregate_target=29527900160
DDCPRD2.__sga_target=44291850240
DDCPRD1.__sga_target=44291850240
DDCPRD2.__shared_io_pool_size=0
DDCPRD1.__shared_io_pool_size=0
DDCPRD2.__shared_pool_size=7784628224
DDCPRD1.__shared_pool_size=7784628224
DDCPRD2.__streams_pool_size=0
DDCPRD1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DDCPRD_STANDBY/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/ddcprd_standby/controlfile/current01.ctl','+RECO/ddcprd_standby/controlfile/current02.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='+RECO'
*.db_domain=''
*.db_name='DDCPRD'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=1073741824000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DDCPRDXDB)'
DDCPRD1.instance_number=1
DDCPRD2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=73665609728
*.open_cursors=500
*.processes=2000
*.remote_listener='dm01-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=2205
DDCPRD1.thread=1
DDCPRD2.thread=2
DDCPRD2.undo_tablespace='UNDOTBS1'
DDCPRD1.undo_tablespace='UNDOTBS2'

#DATAGUARD
DB_UNIQUE_NAME=DDCPRD_STANDBY
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=DDCPRD_STANDBY'
LOG_ARCHIVE_DEST_2='SERVICE=DDCPRD LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE NET_TIMEOUT=30 VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DDCPRD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_CONFIG='DG_CONFIG=(DDCPRD_STANDBY,DDCPRD)'
LOG_ARCHIVE_MAX_PROCESSES=4
STANDBY_FILE_MANAGEMENT=AUTO
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
DB_FILE_NAME_CONVERT='+DATA','+DATA'
LOG_FILE_NAME_CONVERT='+DATA','+DATA','+RECO','+RECO'
FAL_SERVER=DDCPRD
SERVICE_NAMES='DDCPRD_STANDBY,DDCPRD'

[oracle@dm01db01 ~]$ export ORACLE_SID=DDCPRD1
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/initDDCPRD1'

8.还原控制文件，mount数据库

[oracle@dm01db01 ~]$ export ORACLE_SID=DDCPRD1
[oracle@dm01db01 ~]$ rman target /
RMAN> restore standby controlfile from '/u01/backup/control.bak';
RMAN> mount database;
RMAN> restore database;

9,创建spfile、pfile

SQL> create spfile='+DATA/ddcprd_standby/spfileddcprd.ora’ from pfile='/home/oracle/initDDCPRD1.ora';
节点1：
[oracle@dm01db01 ~]$ vi $ORACLE_HOME/dbs/initDDCPRD1.ora
spfile='+DATA/ddcprd_standby/spfileddcprd.ora'
节点2：
[oracle@dm01db01 ~]$ vi $ORACLE_HOME/dbs/initDDCPRD2.ora
spfile='+DATA/ddcprd_standby/spfileddcprd.ora'

10.添加数据库及实例到群集

<pre name="code" class="sql">[grid@dm01db01 ~]$ srvctl add database -d DDCPRD_STANDBY -o /u01/app/oracle/product/11.2.0/dbhome_1 -n DDCPRD
[grid@dm01db01 ~]$ srvctl add instance -d DDCPRD_STANDBY -i DDCPRD1 -n dm01db01
[grid@dm01db01 ~]$ srvctl add instance -d DDCPRD_STANDBY -i DDCPRD2 -n dm01db02

11.重启数据库，并在节点2上启动

[oracle@dm01db01 ~]$ export ORACLE_SID=DDCPRD1
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL> shutdown immediate
[grid@dm01db01 ~]$ srvctl start database -d DDCPRD

12.备库添加standby redo log

SQL> alter database add standby logfile thread 1 group 5 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 1 group 6 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 1 group 7 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 8 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 9 ('+DATA','+RECO') size 500M;
SQL> alter database add standby logfile thread 2 group 10 ('+DATA','+RECO') size 500M;
说明：如添加失败，可执行alter database recover managed standby database cancel;或alter system set standby_file_managemant=manual;后，再添加

13.开启实时应用日志，查看是否成功配置

SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select name,open_mode,database_role from gv$database;
NAME       OPEN_MODE               DATABASE_ROLE
------     --------------------    ----------------------------
DDCPRD1    READ ONLY WITH APPLY    PHYSICAL STANDBY
DCCPRD2    READ ONLY WITH APPLY    PHYSICAL STANDBY

主库切几次日志，检查日志是否传到备库并应用