【DG】之创建Physical Standby Database

2 设置Primary Database
2.1 启用Forced Logging

SYS@BEIJING> col FORCE_LOGGING for a15
SYS@BEIJING> select FORCE_LOGGING from v$database;
FORCE_LOGGING
---------------
NO

启用Forced Logging

SYS@BEIJING> alter database force logging;
Database altered.

2.2 检查密码文件

[oracle@secdb1 dbs]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/
hc_BEIJING.dat  init.ora      lkBEIJING     spfileBEIJING.ora
initdw.ora   initBEIJING.ora  orapwBEIJING  sqlnet.log

保证所有节点的sys密码必须一致
2.3  配置Standby Redo Log
查看现有日日志组

SYS@BEIJING> select * from v$logfile;
GROUP# STATUS  TYPE    MEMBER            IS_
---------- ------- ------- -------------------------------------
1 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo03.log NO

3 rows selected.

增加standby redo log日志组

SYS@BEIJING> alter database add standby logfile group 4
('/u01/app/oracle/oradata/BEIJING/disk1/stredo04_01.log',
'/u01/app/oracle/oradata/BEIJING/disk2/stredo04_02.log') size 100m;
Database altered.

2.4  配置参数文件
生成pfile

SYS@BEIJING> create pfile from spfile;
File created.

在参数文件中增加如下部分

DB_UNIQUE_NAME=BEIJING
LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'
CONTROL_FILES='/u01/app/oracle/oradata/BEIJING/disk1/control01.ctl',
'/u01/app/oracle/oradata/BEIJING/disk2/control02.ctl',
'/u01/app/oracle/oradata/BEIJING/disk3/control03.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/arch/BEIJING/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=BEIJING'
LOG_ARCHIVE_DEST_2=
'SERVICE=SHANGHAI LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SHANGHAI'
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=30a
FAL_SERVER=SHANGHAI
FAL_CLIENT=BEIJING
DB_FILE_NAME_CONVERT='SHANGHAI','BEIJING'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/arch/SHANGHAI/','/u01/app/oracle/arch/BEIJING/'
STANDBY_FILE_MANAGEMENT=AUTO

关闭数据库以创建spfile

SYS@BEIJING> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@BEIJING> create spfile from pfile;

File created.

2.5  启用归档

SYS@BEIJING> startup mount
ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              75498896 bytes
Database Buffers          234881024 bytes
Redo Buffers                2973696 bytes
Database mounted.

SYS@BEIJING> alter database archivelog;

Database altered.

SYS@BEIJING> alter database open;

Database altered.

SYS@BEIJING> archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch/BEIJING/
Oldest online log sequence     2
Next log sequence to archive   5
Current log sequence           5

3 设置Standby Database
3.1 创建Primary Database的数据文件备份
备份中不要包括日志文件

SYS@BEIJING> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

备份数据文件：

[oracle@secdb1 oradata]$ tar -zcvf BEIJING.tar *

3.2 创建Standby Database的控制文件

SYS@BEIJING> startup mount;

ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              75498896 bytes
Database Buffers          234881024 bytes
Redo Buffers                2973696 bytes
Database mounted.

SYS@BEIJING> alter database create standby controlfile as '/home/oracle/control01.ctl';

Database altered.

SYS@BEIJING> alter database open;

Database altered.

3.3 创建Standby Database的参数文件

SYS@BEIJING> create pfile from spfile;

File created.

编辑参数文件

DB_NAME=BEIJING
DB_UNIQUE_NAME=SHANGHAI
LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'
CONTROL_FILES='/u01/app/oracle/oradata/SHANGHAI/disk1/control01.ctl','/u01/app/oracle/oradata/SHANGHAI/disk2/control02.ctl','/u01/app/oracle/oradata/SHANGHAI/disk3/control03.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/arch/SHANGHAI/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SHANGHAI'
LOG_ARCHIVE_DEST_2=
'SERVICE=BEIJING LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=BEIJING'
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=30a
FAL_SERVER=SHANGHAI
FAL_CLIENT=BEIJING
DB_FILE_NAME_CONVERT='BEIJING','SHANGHAI'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/disk1/',
'/u01/app/oracle/oradata/BEIJING/disk2/','/u01/app/oracle/oradata/SHANGHAI/disk2/',
'/u01/app/oracle/oradata/BEIJING/disk3/','/u01/app/oracle/oradata/SHANGHAI/disk3/'
STANDBY_FILE_MANAGEMENT=AUTO
user_dump_dest='/u01/app/oracle/SHANGHAI/udump'
core_dump_dest='/u01/app/oracle/SHANGHAI/cdump'
background_dump_dest='/u01/app/oracle/SHANGHAI/bdump'
audit_file_dest='/u01/app/oracle/SHANGHAI/adump'

3.4 拷贝数据库文件，控制文件，参数文件到Standby Database

[oracle@secdb1 ~]$ scp initBEIJING.ora BEIJING.tar tnsnames.ora listener.ora 
control01.ctl oracle@secdb2:/home/oracle

The authenticity of host 'secdb2 (192.168.80.102)' can't be established.
RSA key fingerprint is 90:77:13:80:91:dd:8c:42:c0:24:ee:f7:06:36:1c:7f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'secdb2,192.168.80.102' (RSA) to the list of known hosts.
oracle@secdb2's password:
initBEIJING.ora                                        100% 1765     1.7KB/s   00:00
BEIJING.tar                                            100%  156MB   4.2MB/s   00:37
tnsnames.ora                                        100%  593     0.6KB/s   00:00
listener.ora                                        100%  569     0.6KB/s   00:00
control01.ctl                                       100% 6896KB   3.4MB/s   00:02

[oracle@secdb2 oradata]$ tar -zxvf BEIJING.tar

[oracle@secdb2 disk2]$ mv ../disk1/control01.ctl .

[oracle@secdb2 ~]$ mv initBEIJING.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/dbs/initSHANGHAI.ora

要保证Primary Database的目录和Standby Database的目录一致，Standby Database中不存在的目录要提前创建，包括oradata，admin，flash_recovery_area，arch
3.5 创建密码文件

[oracle@secdb2 dbs]$ orapwd file=orapwSHANGHAI password=oracle entries=30

保证密码和主库一致
3.6 编辑监听文件

[oracle@secdb2 ~]$ mv listener.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin
[oracle@secdb2 ~]$ mv tnsnames.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin
[oracle@secdb2 ~]$ cd /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin/
[oracle@secdb2 admin]$ vi listener.ora
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=SHANGHAI)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=SHANGHAI))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM=extproc)))

[oracle@secdb2 admin]$ vi tnsnames.ora
BEIJING=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=BEIJING)))
SHANGHAI=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=SHANGHAI)))
BEIJING_S=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1526)))
    (CONNECT_DATA=
      (SERVER=shared)
      (SERVICE_NAME=BEIJING)))

3.7 启动监听器

[oracle@secdb2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - production on 19-JAN-2013 21:40:16
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))

STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - production
Start Date                19-JAN-2013 21:40:17
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Services Summary...
Service "SHANGHAI" has 1 instance(s).
Instance "SHANGHAI", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

3.8 启动Standby Database
3.8.1 启动Standby Database到mount阶段

[oracle@secdb2 admin]$ sqlplus sys/oracle@SHANGHAI as sysdba
SQL*Plus: Release 10.2.0.1.0 - production on Sat Jan 19 21:58:27 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup mount;

ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  1219184 bytes
Variable Size              96470416 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes

Database mounted.

3.8.2 启动Redo Apply

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

Database altered.

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

3.8.3 测试Standby Database的归档操作
在主库中切换日志组

SYS@BEIJING> alter system switch logfile;

System altered.

在备库中查看归档日志是否正常

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

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
4 17-JAN-13 19-JAN-13
5 19-JAN-13 19-JAN-13
6 19-JAN-13 19-JAN-13
7 19-JAN-13 19-JAN-13
8 19-JAN-13 19-JAN-13
9 19-JAN-13 19-JAN-13
10 19-JAN-13 19-JAN-13
11 19-JAN-13 19-JAN-13

8 rows selected.

在备库中查看归档日志是否以应用

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

SEQUENCE# APP
---------- ---
4 NO
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
11 YES

8 rows selected.

4 主备机切换
4.1 切换原主库为备库

SYS@BEIJING> alter database commit to switchover to physical standby;

Database altered.

4.2 检查原备库状态

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

4.3 切换原备库为主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> alter database open;

Database altered.

4.4 检查新备库中的归档日志应用

SYS@BEIJING> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APP
---------- ---
4 NO
4 NO
5 NO
5 NO
6 NO
6 NO
7 NO
7 NO
8 NO
8 NO
9 NO
9 NO
10 NO
10 NO
11 YES
11 NO
12 NO
12 NO
13 YES
13 YES
14 YES
14 NO
15 NO
16 NO
17 NO

25 rows selected.

SYS@BEIJING>

发现归档日志没有应用
4.5 启动Redo Apply

SYS@BEIJING> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

4.6 再次检查归档日志是否应用
可以看到redo已经应用

SYS@BEIJING> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APP
---------- ---
4 NO
4 YES
5 YES
5 NO
6 YES
6 NO
7 YES
7 NO
8 NO
8 YES
9 NO
9 YES
10 NO
10 YES
11 YES
11 YES
12 YES
12 NO
13 YES
13 YES
14 YES
14 NO
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES