Oracle dataguard配置Step by Step

一、准备工作

1.1 设置主库的force logging模式
SQL> alter database force logging;
Database altered.

1.2 创建主库密码文件
$ orapwd file=orapw$ORACLE_SID password=thunis entries=4

1.3 配置主库standby redo log，并验证结果
SQL> alter database add standby logfile group 4('/u01/app/oradata/guard1/redo04.log') size 50m;
Database altered.

SQL> select group#, thread#, sequence#, archived, status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          0          0 YES UNASSIGNED

1.4 配置主库初始化参数文件
    DB_NAME=guard1
    DB_UNIQUE_NAME=guard1
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(guard1,guard2)'
    LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/flash_recovery_area/GUARD1/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=guard1'
    LOG_ARCHIVE_DEST_2='SERVICE=guard2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=guard2'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_MAX_PROCESSES=30

    /*以下部分为主机切换为备库使用*/

    FAL_SERVER=guard2
    FAL_CLIENT=guard1
    DB_FILE_NAME_CONVERT='guard2','guard1'
    LOG_FILE_NAME_CONVERT='/u01/app/oradata/guard2','/u01/app/oradata/guard1'
    STANDBY_FILE_MANAGEMENT=AUTO

1.5 确认主库的归档模式
SQL> startup mount
ORACLE instance started.

Total System Global Area  104857600 bytes
Fixed Size                  1217980 bytes
Variable Size              96471620 bytes
Database Buffers            4194304 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25

二、创建主库相关文件的备份

2.1 创建主库数据文件的物理备份（这里采用tar方式）
$ cd /u01/app/oradata
$ tar -cvf guard1.tar guard1
guard1/
guard1/temp01.dbf
guard1/control01.ctl
guard1/system01.dbf
guard1/sysaux01.dbf
guard1/control02.ctl
guard1/undotbs01.dbf
guard1/redo03.log
guard1/redo01.log
guard1/redo02.log
guard1/control03.ctl
guard1/example01.dbf
guard1/users01.dbf
guard1/redo04.log
[oracle@test01 oradata]$ ll *tar
-rw-r--r--  1 oracle dba 1133783040 Oct 28 16:48 guard1.tar

2.2 为备库创建控制文件
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';

2.3 为备库创建初始化参数文件
SQL> CREATE PFILE='/tmp/initguard1.ora' FROM SPFILE;

2.4 将tar包以及主库控制文件和参数文件ftp到备库
# ftp 192.168.0.88
Connected to 192.168.0.88.
220 (vsFTPd 2.0.1)
530 Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (192.168.0.88:root): oracle
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> cd /u01/app/oradata
250 Directory successfully changed.
ftp> pwd
257 "/u01/app/oradata"
ftp> lcd /home/oracle
Local directory now /home/oracle
ftp> get guard1.tar
local: guard1.tar remote: guard1.tar
227 Entering Passive Mode (192,168,0,88,240,125)
150 Opening BINARY mode data connection for guard1.tar (1133783040 bytes).

226 File send OK.
1133783040 bytes received in 2.6e+02 seconds (4.3e+03 Kbytes/s)

ftp> cd /tmp
250 Directory successfully changed.
ftp> ls
227 Entering Passive Mode (192,168,0,88,31,48)
150 Here comes the directory listing.
-rw-------    1 500      500             0 Oct 28 08:07 6GXyHcjG4Y
-rw-rw-rw-    1 500      500             0 Oct 28 08:05 EM_TARGET_INSTALLER.lk
-rw-r-----    1 500      500            20 Oct 28 07:55 LinuxVendor_output.txt
-rw-r-----    1 500      500       6832128 Oct 28 08:52 control.ctl
-rw-r-----    1 500      500           444 Oct 28 07:55 cpuinfo.txt
-rw-------    1 500      500             0 Oct 28 08:07 cyzsdX1KWR
-rw-r-----    1 500      500            18 Oct 28 07:55 glibc.txt
-rw-r--r--    1 500      500           881 Oct 28 08:57 initguard1.ora
drwx------    2 0        0            4096 Sep 27 01:52 keyring-606Cr9
srwxr-xr-x    1 0        0               0 Oct 28 07:52 mapping-root
-rw-r-----    1 500      500         15998 Oct 28 07:55 pkginfo.txt
-rw-r-----    1 500      500           100 Oct 28 07:55 swapinfo.txt
-rw-r-----    1 500      500            11 Oct 28 07:55 tmpFileKernelParms.txt
226 Directory send OK.
ftp> mget *ctl
mget control.ctl? y
227 Entering Passive Mode (192,168,0,88,220,219)
150 Opening BINARY mode data connection for control.ctl (6832128 bytes).
226 File send OK.
6832128 bytes received in 0.83 seconds (8e+03 Kbytes/s)
ftp> mget *ora
mget initguard1.ora? y
227 Entering Passive Mode (192,168,0,88,99,183)
150 Opening BINARY mode data connection for initguard1.ora (881 bytes).
226 File send OK.
881 bytes received in 0.0042 seconds (2.1e+02 Kbytes/s)
ftp> bye
221 Goodbye.

三、设置备库

3.1 解压主库数据文件到备库相应位置
$ cd $ORACLE_BASE/oradata
$ tar -xvf guard1.tar
guard1/
guard1/temp01.dbf
guard1/control01.ctl
guard1/system01.dbf
guard1/sysaux01.dbf
guard1/control02.ctl
guard1/undotbs01.dbf
guard1/redo03.log
guard1/redo01.log
guard1/redo02.log
guard1/control03.ctl
guard1/example01.dbf
guard1/users01.dbf
guard1/redo04.log

$ mv guard1 guard2
$ cp /tmp/control01.ctl $ORACLE_BASE/oradata/guard1
$ cp /tmp/initguard2.ora $ORACLE_HOME/dbs

3.2 修改参数文件
--参照前面主库参数文件的配置，值得一提的是db_name参数不能修改，保持和主库一致

3.3 创建相关目录
$ cd $ORACLE_BASE/admin
$ mkdir guard2
$ cd guard2
$ mkdir adump bdump cdump udump

3.4 创建备库密码文件
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=thunis entries=4

四、创建主备库监听及tnsnames文件

4.1 监听文件
$ cat listener.ora

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

4.2 tnsnames.ora文件
$ cat tnsnames.ora

GUARD2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.99)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = guard2)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

4.3 分别启动主备库监听

五、测试主备库之间的网络连通性

5.1 tnsping测试
$ hostname
test02

$ tnsping guard1
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 28-OCT-2008 21:14:18
Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.88)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = guard1)))
OK (30 msec)

$ hostname
test01

$ tnsping guard2
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 28-OCT-2008 21:14:18
Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.89)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = guard1)))
OK (30 msec)

5.2 sqlplus登录测试
[oracle@test02 guard2]$ sqlplus 'sys/thunis@guard1' as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 22:24:59 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

[oracle@test01 guard1]$ sqlplus 'sys/thunis@guard2' as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 22:24:59 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

六、启动备用数据库

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes

SQL> alter database mount standby database;
Database altered.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

--至此，一个完整的physical standby database就配置完成了。

七、验证归档日志应用情况

7.1 通过数据字典视图验证
--主库执行

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> select sequence#, first_time, next_time, applied, fal from v$archived_log order by sequence#;

 SEQUENCE# FIRST_TIM NEXT_TIME APP FAL
---------- --------- --------- --- ---
......
         6 28-OCT-08 28-OCT-08 NO  NO
         6 28-OCT-08 28-OCT-08 YES YES
         7 28-OCT-08 28-OCT-08 NO  NO

 SEQUENCE# FIRST_TIM NEXT_TIME APP FAL
---------- --------- --------- --- ---
         7 28-OCT-08 28-OCT-08 YES YES
         8 28-OCT-08 28-OCT-08 YES YES
         8 28-OCT-08 28-OCT-08 NO  NO
         9 28-OCT-08 28-OCT-08 NO  NO
         9 28-OCT-08 28-OCT-08 YES YES

16 rows selected.

7.2 查看告警日志文件
--备库上有类似的信息
Tue Oct 28 22:50:22 2008
Attempt to start background Managed Standby Recovery process (guard2)
MRP0 started with pid=10, OS id=7262
Tue Oct 28 22:50:22 2008
MRP0: Background Managed Standby Recovery process started (guard2)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Tue Oct 28 22:50:28 2008
Completed: alter database recover managed standby database disconnect from session
Tue Oct 28 22:51:22 2008
idle dispatcher 'D000' terminated, pid = (13, 1)
Tue Oct 28 22:52:08 2008
RFS[2]: Possible network disconnect with primary database
Tue Oct 28 22:52:12 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 7268
RFS[3]: Identified database type as 'physical standby'
RFS[3]: Successfully opened standby log 4: '/u01/app/oradata/guard2/redo04.log'
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 7270
RFS[4]: Identified database type as 'physical standby'
RFS[4]: Successfully opened standby log 4: '/u01/app/oradata/guard2/redo04.log'
Tue Oct 28 22:52:17 2008
Media Recovery Log /u01/app/flash_recovery_area/GUARD2/arch/1_13_669311857.dbf

八、主备库切换

8.1 将主库切换到备用状态
SQL> alter database commit to switchover to physical standby;
Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes

SQL> alter database mount standby database;
Database altered.

SQL> select name,open_mode,protection_mode,database_role from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
--------- ---------- -------------------- ----------------
GUARD1    MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

8.2 将备库转换成主库模式
SQL> alter database commit to switchover to primary;
Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

8.3 查看主备库告警日志文件

九、数据验证

最后，我们在主库上创建一个表并插入一些数据，然后模拟日志切换，最后在备库上验证数据的传输情况。

9.1 主库上建测试表
SQL> create table randy (id number);
Table created.

SQL> insert into randy values(1);
1 row created.

SQL> /
1 row created.

SQL> /
1 row created.

SQL> commit;
Commit complete.

SQL> select * from randy;

        ID
----------
         1
         1
         1

9.2 主库上模拟日志切换
SQL> alter system archive log current;

System altered.

9.3 查看备库告警日志文件
Wed Oct 29 14:56:09 2008
Fetching gap sequence in thread 1, gap sequence 27-27
Wed Oct 29 14:56:31 2008
RFS[3]: Archived Log: '/u01/app/flash_recovery_area/GUARD2/arch/1_27_669311857.dbf'
Wed Oct 29 14:56:39 2008
Media Recovery Log /u01/app/flash_recovery_area/GUARD2/arch/1_27_669311857.dbf
Media Recovery Waiting for thread 1 sequence 28 (in transit)

--可以发现主库的数据已经传送过来。

9.4 以只读模式打开备库，验证数据
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from randy;

        ID
----------
         1
         1
         1

9.5 恢复备库的standby状态
SQL> alter database recover managed standby database disconnect from session;
Database altered.


--End--