Oracle Data Guard (二) Physical Standby
1. 检查Primary数据库是否是archivelog模式

SQL> select log_mode from v$database;

如果结果不是"ARCHIVELOG"，执行以下命令：

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

2. 检查Primary数据库是否是force logging模式

SQL> select force_logging from v$database;

如果结果不是"YES",执行以下命令：

SQL> alter database force logging;

3. 在Primary数据库上创建Standby logfile

SQL> select group#,thread#,bytes/1024/1024 from v$log;

根据得到的group数目和log大小，为Standby实例创建对应的logfile，默认情况下是三个Group，且每个大小为50M。

SQL> alter database add standby logfile ‘/home/oracle/app/oracle/oradata/sungoin/stby_redo01.log’ size 50m;

SQL> alter database add standby logfile ‘/home/oracle/app/oracle/oradata/sungoin/stby_redo02.log’ size 50m;

SQL> alter database add standby logfile ‘/home/oracle/app/oracle/oradata/sungoin/stby_redo03.log’ size 50m;

4. 在Primary数据库上配置Standby相关系统参数

SQL> alter system set log_archive_config=’DG_CONFIG=(test,standby)’;

SQL> alter system set log_archive_dest_1=’LOCATION=/home/oracle/app/oracle/flash_recovery_area/test/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test’;

SQL> alter system set log_archive_dest_2=’service=standby ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=standby’;

SQL> alter system set fal_server=test;

SQL> alter system set fal_client=standby;

SQL> alter system set standby_file_management=auto;

SQL> alter system set remote_login_passwordfile=exclusive;

5. 在Primary服务器上配置监听器和TNS变量

在Primary服务器的监听器中静态注册test实例,添加下面行到文件 /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora中:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
)

)

在Primary服务器的TNS中注册test和standby,添加下面行到文件 /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora中:

TEST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)

)

STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

6. 在Standby服务器上创建目录结构

在Oracle用户环境下执行下列命令:

$ mkdir -p /home/oracle/app/oracle/oradata/test/

$ mkdir -p /home/oracle/app/oracle/admin/test/adump

$ mkdir -p /home/oracle/app/oracle/admin/test/dpdump

$ mkdir -p /home/oracle/app/oracle/admin/test/pfile

$ mkdir -p /home/oracle/app/oracle/flash_recovery_area/test/archivelog/

7. 在Standby服务器上配置监听器和TNS变量

在Standby服务器的监听器中静态注册test实例,添加下面行到文件 /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora中:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
)

)

在Standby服务器的TNS中注册sungoin和standby,添加下面行到文件 /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora中:

TEST=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)

)

STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

8. 在Standby服务器上创建启动文件和密码文件创建文件/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initsungoin.ora,文件内容如下:
DB_NAME=test

DB_UNIQUE_NAME=standby

拷贝Primary服务上的密码文件到standby服务器上，命令如下:

scp oracle@192.168.1.101:/home/oracle/app/racle/product/11.2.0/dbhome_1/dbs/orapwtest /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/

9. 在Standby服务器上启动standby实例

$ sqlplus / as sysdba

SQL> startup nomount;

10. 在Primary服务器上创建和执行RMAN脚本文件

创建脚本文件dupstby.cmd, 文件内容如下：

run {
allocate channel pri1 type disk;
allocate channel pri2 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby
from active database
dorecover
spfile
set db_unique_name='standby'
set control_files='/home/oracle/app/oracle/oradata/test/control01.ctl', '/home/oracle/app/oracle/flash_recovery_area/test/control02.ctl'
set fal_client='test'
set fal_server='standby'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(test,standby)'
set log_archive_dest_1= 'LOCATION=/home/oracle/app/oracle/flash_recovery_area/test/archivelog VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
set log_archive_dest_2='service=test ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=test'
nofilenamecheck;

}

进入rman环境，执行此脚本

$ rman target /

RMAN> connect auxiliary sys/oracle@standby

RMAN> @dupstby.cmd

11. 在Standby数据库上启动Standby执行进程

$ sqlplus / as sysdba

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

SQL> exit;

12. 验证配置是否成功

在Primary服务器执行:

SQL> archive log list；

SQL> <任何数据库改动>

SQL> alter system switch logfile;

SQL> archive log list;

在standby服务器上执行:

SQL> archive log list;

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

比对两端的Current log sequence是否一样，正确情况下应该是一样的。

另外检查primary和standby两个实例的角色和状态，在两个实例上分别执行下面命令:

SQL> select open_mode,switchover_status,database_role from v$database;

Primary服务器上应该得到结果：
OPEN_MODE SWITCHOVER_STATUSDATABASE_ROLE
-------------------- --------------------------------------------------

READ WRITE TO STANDBYPRIMARY

Standby服务器上应该得到结果：

OPEN_MODE SWITCHOVER_STATUSDATABASE_ROLE
-------------------- ---------------------------------------------------

READ ONLY WITH APPLYNOT ALLOWEDPHYSICAL STANDBY

如果检查结果和上述不一样，可能是哪个步骤出了错误，检查Log查看详细情况。