oracle 10g dataguard 搭建

1、主库归档模式：

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

如果非归档，修改为归档模式

SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2、允许强制日志

SQL> alter database force logging;

3、修改主库参数

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      test
SQL> alter system set log_archive_config='dg_config=(test,std)';
SQL> show parameter log_archive_dest_1
SQL> alter system set log_archive_dest_1='location=/usr/oracle/oradata/test/archive/ valid_for=(all_logfiles,all_roles) db_unique_name=test';
SQL> show parameter log_archive_dest_1
SQL> alter system set log_archive_dest_2='service=std lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=std';
SQL> show parameter log_archive_dest_state_1
SQL> show parameter log_archive_dest_state_2  --enable
SQL> alter system set fal_server=std;
SQL> alter system set fal_client=test; 

SQL> alter system set DB_FILE_NAME_CONVERT='/usr/oracle/oradata/test/','/usr/oracle/oradata/std/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/usr/oracle/oradata/test/','/usr/oracle/oradata/std/' scope=spfile;

--两个库路径不同，需要这样定义来转换

备库需要修改initstd.ora文件

DB_FILE_NAME_CONVERT='/usr/oracle/oradata/test/','/usr/oracle/oradata/std/'
LOG_FILE_NAME_CONVERT='/usr/oracle/oradata/test/','/usr/oracle/oradata/std/'

4、创建pfile拷贝到备库使用

create pfile from spfile;

5、主库备份

mkdir /usr/oracle/oracle_bak
RMAN> backup format '/usr/oracle/oracle_bak/full_%U' database plus archivelog;
--全备   
RMAN> backup format '/usr/oracle/oracle_bak/con_%U' current controlfile for standby;
--注意控制文件要加一个for standby，因为主库两边是不同的

将备份文件拷贝到备库

复制密码文件：
scp oracle@192.168.137.81:/usr/oracle/product/10.2.0/dbs/orapwtest /usr/oracle/product/10.2.0/dbs/orapwstd

复制参数文件： 
scp oracle@192.168.137.81:/usr/oracle/product/10.2.0/dbs/inittest.ora /usr/oracle/product/10.2.0/dbs/initstd.ora

复制备份文件：

scp oracle@192.168.137.81:/usr/oracle/oracle_bak/* /usr/oracle/oracle_bak/

6、备库目录创建：

mkdir -p /usr/oracle/oradata/std/archive
mkdir -p /usr/oracle/admin/std/{a,b,u,dp,c}dump

8、监听、tns

netca

tnsping 192.168.137.81:3173

tnsping 192.168.137.86:3173

相互能够ping通

创建tns（主库备库都增加）

test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.81)(PORT = 3173))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
std =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.86)(PORT = 3173))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = std)
    )
  )

测试备库通过tns能链接到主库

sqlplus sys/sys@test as sysdba

如果修改端口，需要修改静态监听（主库、备库都需要修改）：

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.86)(PORT = 3173))';

9、修改备库参数文件：

std和test互换，

注意：

db_name不要改，和主库一样

增加：db_unique_name='std'

创建spfile

sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 3月 22 06:45:03 2016

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
启动时，有几个参数报错，先从参数文件中将这几个参数去掉

10、连接到主库进行恢复

rman连接备库

[oracle@uoraclestd ~]$ rman target sys/sys@test auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on 星期二 3月 22 06:58:18 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2204597168)
connected to auxiliary database: STD (not mounted)

开始恢复（由于修改了路径，需要建立对应关系）

run {
set newname for datafile 1 to '/usr/oracle/oradata/std/system01.dbf';
set newname for datafile 2 to '/usr/oracle/oradata/std/undotbs01.dbf';
set newname for datafile 3 to '/usr/oracle/oradata/std/sysaux01.dbf';
set newname for datafile 4 to '/usr/oracle/oradata/std/users01.dbf';
set newname for tempfile 1 to '/usr/oracle/oradata/std/temp01.dbf';
duplicate target database for standby;
}

配置对应参数之后，可以直接

duplicate target database for standby;

创建redo

SQL> alter system set standby_file_management=auto;
SQL> alter database add standby logfile group 4 '/usr/oracle/oradata/std/standbyredo4.log' size 50m;
SQL> alter database add standby logfile group 5 '/usr/oracle/oradata/std/standbyredo5.log' size 50m;
SQL> alter database add standby logfile group 6 '/usr/oracle/oradata/std/standbyredo6.log' size 50m;

为了以后主库切换为备库，主库也同样创建，但需要注意，创建之前需要修改

standby_file_management=MANUAL

11、应用日志

sqlplus / as sysdba
SQL> recover managed standby database disconnect from session;
Media recovery complete.

12、验证日志传输

主库执行：
SQL> alter system switch logfile;
System altered.
备库执行：
SQL> select name from v$archived_log;
检查新归档的重做日志文件已经应用(备库）：
SQL> select sequence#,applied from v$archived_log order by sequence#;

13、问题排查

1、检查归档目录

column DEST_NAME format a20
column status format a20
column error format a20
select DEST_NAME,status,error from v$archive_dest;

2、rman 连接实例

rman连接哪个实例，根据参数文件中的db_name连接，dataguard配置时，db_name都写的一样

14、测试

取消应用日志

recover managed standby database cancel;