两台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 MOUNTSQL> 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.oraDDCPRD2.__db_cache_size=33285996544DDCPRD1.__db_cache_size=33285996544DDCPRD2.__java_pool_size=1610612736DDCPRD1.__java_pool_size=1610612736DDCPRD2.__large_pool_size=1073741824DDCPRD1.__large_pool_size=1073741824DDCPRD2.__pga_aggregate_target=29527900160DDCPRD1.__pga_aggregate_target=29527900160DDCPRD2.__sga_target=44291850240DDCPRD1.__sga_target=44291850240DDCPRD2.__shared_io_pool_size=0DDCPRD1.__shared_io_pool_size=0DDCPRD2.__shared_pool_size=7784628224DDCPRD1.__shared_pool_size=7784628224DDCPRD2.__streams_pool_size=0DDCPRD1.__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=1DDCPRD2.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=2205DDCPRD1.thread=1DDCPRD2.thread=2DDCPRD2.undo_tablespace='UNDOTBS1'DDCPRD1.undo_tablespace='UNDOTBS2'#DATAGUARDDB_UNIQUE_NAME=DDCPRD_STANDBYLOG_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=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_CONFIG='DG_CONFIG=(DDCPRD_STANDBY,DDCPRD)'LOG_ARCHIVE_MAX_PROCESSES=4STANDBY_FILE_MANAGEMENT=AUTOREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVEDB_FILE_NAME_CONVERT='+DATA','+DATA'LOG_FILE_NAME_CONVERT='+DATA','+DATA','+RECO','+RECO'FAL_SERVER=DDCPRDSERVICE_NAMES='DDCPRD_STANDBY,DDCPRD'

[oracle@dm01db01 ~]$ export ORACLE_SID=DDCPRD1[oracle@dm01db01 ~]$ sqlplus / as sysdbaSQL> 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.oraspfile='+DATA/ddcprd_standby/spfileddcprd.ora'节点2：[oracle@dm01db01 ~]$ vi $ORACLE_HOME/dbs/initDDCPRD2.oraspfile='+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 sysdbaSQL> 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 STANDBYDCCPRD2    READ ONLY WITH APPLY    PHYSICAL STANDBY

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