Oracle Data Guard的配置

[oracle@Master orcl]$ cat /etc/issue
Oracle Linux Server release 6.6
Kernel \r on an \m
 
[oracle@oracle orcl]$ sqlplus -v
 
SQL*Plus: Release 11.2.0.1.0 Production 

SQL> select name,log_mode from v$database;
 
NAME      LOG_MODE
--------- ------------
ORCL      ARCHIVELOG

SQL> select destination from v$archive_dest where destination is not null;

DESTINATION
——————————————————————————–
/u01/ARCHLOG/

 
 
SQL> alter database force logging;
 
数据库已更改。

SQL> select FORCE_LOGGING from v$database;

FOR
—
YES

 
SQL> select database_role from v$database;
 
DATABASE_ROLE
----------------
PRIMARY 

alter database add standby logfile
group 11 ('/u01/orcl/onlinelog/redo11.log') size 50M,
group 12 ('/u01/orcl/onlinelog/redo12.log') size 50M,
group 13 ('/u01/orcl/onlinelog/redo13.log') size 50M,
group 14 ('/u01/orcl/onlinelog/redo14.log') size 50M;

--Add below item when DB acts as primary role  
alter system set db_unique_name='orcl' scope=spfile;  
alter system set log_archive_config='DG_CONFIG=(orcl,standby)';  
alter system set log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)';  
alter system set log_archive_dest_2='SERVICE=standby ASYNC db_unique_name=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)';  
alter system set log_archive_dest_state_1=enable;  
alter system set log_archive_dest_state_2=enable;  
alter system set log_archive_max_processes=4;  
alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;  
  
--Add below item when DB turn to standby role  
alter system set db_file_name_convert='standby','orcl' scope=spfile;  
alter system set log_file_name_convert='standby','orcl' scope=spfile;  
alter system set standby_file_management='AUTO';  
alter system set fal_server='standby';  
alter system set fal_client='orcl';

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from spfile;

orcl.__db_cache_size=201326592
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=281018368
orcl.__sga_target=528482304
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=306184192
orcl.__streams_pool_size=0
*._allow_resetlogs_corruption=TRUE
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='standby','orcl'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl'
*.fal_server='standby'
*.log_archive_config='DG_CONFIG=(orcl,standby)'
*.log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=orcl valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='SERVICE=standby ASYNC db_unique_name=standby valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_3=' '
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_file_name_convert='standby','orcl'
*.memory_target=806354944
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1' 

SQL> alter database create standby controlfile as '/u03/backup/control01.ctl';

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.81)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    (PROGRAM = extproc)
  )
  (SID_DESC =
    (GLOBAL_DBNAME= orcl)
    (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
    (SID_NAME=orcl)
  )
)
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Master)(PORT = 1521))
    )
  ) 

SQL> shutdown immediate;

[oracle@Master dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl oracle@192.168.0.82:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstandby
[oracle@Master dbs]$ scp /u03/backup/control01.ctl oracle@192.168.0.82:/u01/app/oracle/oradata/standby/

[oracle@Master dbs]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora oracle@192.168.0.82:/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora

[oracle@Master orcl]$ scp /u01/app/oracle/oradata/orcl/*dbf oracle@192.168.0.82:/u01/app/oracle/oradata/standby/
[oracle@Master orcl]$ scp /u01/orcl/onlinelog/redo* oracle@192.168.0.82:/u01/standby/onlinelog/
[oracle@Master orcl]$ scp /u02/orcl/onlinelog/redo* oracle@192.168.0.82:/u02/standby/onlinelog/
[oracle@Master orcl]$ scp /u03/orcl/onlinelog/redo* oracle@192.168.0.82:/u03/standby/onlinelog/

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=standby
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
#export LANG="zh_CN.UTF-8"
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
#export NLS_LANG="american_america.AL32UTF8"
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

[oracle@Salve ~]$ mkdir -p /u01/app/oracle/admin/standby/adump
[oracle@Salve ~]$mkdir -p /u01/app/oracle/flash_recovery_area/standby

standby.__large_pool_size=4194304
standby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
standby.__pga_aggregate_target=281018368
standby.__sga_target=528482304
standby.__shared_io_pool_size=0
standby.__shared_pool_size=306184192
standby.__streams_pool_size=0
*._allow_resetlogs_corruption=TRUE
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/flash_recovery_area/standby/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='orcl','standby'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='standby'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='standby'
*.fal_server='orcl'
*.log_archive_config='DG_CONFIG=(orcl,standby)'
*.log_archive_dest_1='LOCATION=/u01/ARCHLOG db_unique_name=standby valid_for=(ALL_LOGFILES,ALL_ROLES)'
*.log_archive_dest_2='SERVICE=orcl ASYNC db_unique_name=orcl valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'
*.log_archive_dest_3=' '
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_file_name_convert='orcl','standby'
*.memory_target=806354944
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

orcl =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.81)(PORT = 1521))
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
    )
 
standby =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.82)(PORT = 1521))
        (CONNECT_DATA =
           (SERVICE_NAME = standby)
        )
    )
 
EXTPROC_CONNECTION_DATA=
    (DESCRIPTION =
        (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL =TPC)(KEY = EXTPROCO))
        )
        (CONNECT_DATA =
                (SID = PLSExtProc)
                (PRESENTATION = RO)
        )
    )

SID_LIST_LISTENER=
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (PROGRAM = extproc)
       )
    (SID_DESC =
        (GLOBAL_DBNAME= standby)
        (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME=standby)
    )
)

LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = Salve)(PORT = 1521))
     )
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@Salve ~]$ lsnrctl stop
[oracle@Salve ~]$ lsnrctl start

[oracle@Master admin]$ sqlplus sys/oracle@standby as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1 月 4 22:05:49 2017
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
已连接到空闲例程。SQL> exit
已断开连接
[oracle@Master admin]$ sqlplus sys/oracle@orcl as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1 月 4 22:05:57 2017
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
已连接到空闲例程。

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstandby.ora' 
ORACLE 例程已经启动。Total System Global Area  805875712 bytes
Fixed Size            2217672 bytes
Variable Size          595593528 bytes
Database Buffers      201326592 bytes
Redo Buffers            6737920 bytes
数据库装载完毕。

SQL>  create spfile from pfile; 
 
文件已创建。SQL> show parameter instance_name 
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
instance_name                 string     standby

SQL> alter database recover managed standby database disconnect from session; 
 
数据库已更改。

SQL> alter system switch logfile;
 
系统已更改。SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
        59
 
SQL>  select switchover_status from v$database;    ### 查看主库 DG 状态 
 
SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>  select sequence#,applied from v$archived_log where applied='YES' order by sequence#;
 
 SEQUENCE# APPLIED
---------- ---------
     59 YES

SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
        59

SQL> conn scott/tiger
已连接。SQL> create table standby(test int);
 
表已创建。SQL> insert into standby values(1);
 
已创建 1 行。SQL> commit;
 
提交完成。SQL>  conn / as sysdba;
已连接。SQL> alter system switch logfile;
 
系统已更改。

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
数据库已更改。SQL> alter database open read only;
 
数据库已更改。SQL> conn scott/tiger
已连接。SQL> select * from standby;
 
      TEST
----------
     1