客户将上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
主库切几次日志,检查日志是否传到备库并应用