[转]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

转载于:https://www.cnblogs.com/rootq/articles/1230465.html