[转]Oracle Dataguard的配置（logical standby）
Oracle Dataguard（logical standby）的配置方法
##################################################################################
### primary的设置
##################################################################################
条件： 1、主库的用户必须有LOGSTDBY_ADMINISTRATOR、SELECT_CATALOG_ROLE角色
2、必须使用spfile
################################
### 需要配置的初始化参数：
################################
archile_lag_target=1200
standby_archive_dest='/usr2/u01/app/oracle/oracle9201/oradata/scpdb/standby_archive'
standby_file_management=auto
log_archive_dest_1='LOCATION=/usr2/u01/app/oracle/oracle9201/oradata/scpdb/archive'
log_archive_dest_3='SERVICE=SCPDB.STANDBY LGWR SYNC AFFIRM'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_3='ENABLE'
log_archive_start=true
control_file_record_keep_time
################################
startup mount pfile=/usr2/u01/app/oracle/oracle9201/admin/scpdb/pfile/initscpdb.ora
create spfile from pfile='/usr2/u01/app/oracle/oracle9201/admin/scpdb/pfile/initscpdb.ora'
create pfile='/usr2/u01/app/oracle/oracle9201/admin/scpdb/pfile/initscpdb.ora' from spfile;
#################################
1、设置主库为force logging模式（备库也会有）
SQL> ALTER DATABASE FORCE LOGGING;
2、验证主库为archive log模式，并且自动archive是开启的（备库也会有）
SQL> ARCHIVE LOG LIST;
SQL> SHUTDOWN NORMAL;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ARCHIVE LOG LIST;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/usr2/u01/app/oracle/oracle9201/oradata/scpdb/archive' SCOPE=BOTH;
3、验证LOG_PARALLELISM为1（备库的值不一样）
SQL> SHOW PARAMETER LOG_PARALLELISM;
SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
4、确定支持的数据类型和表
SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
ORDER BY OWNER,TABLE_NAME;
SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';
5、确定表行被唯一标识
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
6、确定Supplemental Logging（备库也会有）
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
7、生成可替换的表空间（只需执行一次，备库也会有standby_file_management=auto）
SQL> CREATE TABLESPACE logmnrts DATAFILE '/usr2/u01/app/oracle/oracle9201/oradata/scpdb/logmnrts.dbf'
SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
##################################################################################
#### standby的设置
##################################################################################
1. 识别数据库文件和日志文件
SQL> SELECT NAME FROM V$DATAFILE;
SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;
2. 拷贝主数据库
2.1 关闭主库
SQL> SHUTDOWN IMMEDIATE;
2.2 拷贝数据文件到临时目录
cp *.dbf /in/orabak/
rcp 192.168.2.209:/in/orabak/* .
2.3 mount主库
SQL> STARTUP MOUNT;
2.4 生成控制文件的备份
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/in/orabak/control01.ctl';
2.5 使主库处于restricted session模式
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
2.6 打开主库并建立standby数据库的数据字典（此步操作备库没有）
### Build过程将使得logMiner的词典信息保存到redo log中，便于在备库应用。
SQL> ALTER DATABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
2.7 diaable restricted session模式
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
2.8 确定最近的归档重做日志
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT NAME FROM V$ARCHIVED_LOG
WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST= 'NO'));
cp /usr2/u01/app/oracle/oracle9201/oradata/scpdb/archive/1_45.dbf /in/orabak/
3. 生成pfile
CREATE PFILE='/in/orabak/initscpdb.ora' FROM SPFILE;
4. 拷贝文件
### rcp 192.168.2.209:/in/orabak/* .
rcp 192.168.2.209:/in/orabak/control01.ctl .
rcp 192.168.2.209:/in/orabak/initscpdb.ora .
rcp 192.168.2.209:/in/orabak/1_45.dbf ./archive
cp control01.ctl control02.ctl
cp control02.ctl control03.ctl
5. 设置standby初始化参数
standby_archive_dest='/usr2/u01/app/oracle/oracle9201/oradata/scpdb/standby_archive'
parallel_max_servers=9
6. windows平台设置服务（unix不须）
7.listener.ora
8.设置sqlnet.ora
### 同时在主备设置，以便将来切换时使用
SQLNET.EXPIRE_TIME=60
9.tnsnames.ora
10. mount standby数据库
startup mount pfile=/usr2/u01/app/oracle/oracle9201/admin/scpdb/pfile/initscpdb.ora
###########################
### 设为最大可用模式
###########################
### primary
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SCPDB.STANDBY LGWR SYNC AFFIRM' SCOPE=BOTH ;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
### standby
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
11. rename datafile
12. rename redo log file
13. turn on database guard
# alter database guard 语句控制用户对standby数据库的访问
SQL> ALTER DATABASE GUARD NONE; # 允许对standby数据库做更新操作
SQL> ALTER DATABASE GUARD ALL; # 不允许对standby数据库做更新操作
SQL> ALTER DATABASE OPEN RESETLOGS;
14. reset db name
15. change db name in parameter file
16. 生成temp表空间
SQL> SELECT * FROM V$TEMPFILE;
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE
CONTENTS ='TEMPORARY';
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE
'/usr2/u01/app/oracle/oracle9201/oradata/scpdb/temp01.dbf'
SIZE 100M REUSE;
17. 注册Archived Redo Log，开始 SQL Apply Operations
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE
'/usr2/u01/app/oracle/oracle9201/oradata/scpdb/archive/1_45.dbf';
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
### SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
### SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
18. 使主数据库archive到standby数据库
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SCPDB.STANDBY LGWR SYNC AFFIRM' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
###########################
### 设为最大可用模式
###########################
### primary
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=SCPDB.STANDBY LGWR SYNC AFFIRM' SCOPE=BOTH ;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;
### standby（必须在mount模式下）
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
