Normal 0 7.8 磅 0 2 false false false Linux 下创建Oracle 10g DG和Broker配置

最近有空，参考了Oracle文档和三思同学的文档，用虚拟机在RHEL4上面做了一下ORACLE 10G 的DG和Broker，将做实验步骤记录下来，以备后查。

环境和基本配置如下：

配置项	主库	备库
服务器	虚拟机1	虚拟机2
操作系统	Red Hat Enterprise 4	Red Hat Enterprise 4
Oracle软件	Oracle 10.2.0.1	Oracle 10.2.0.1
IP	192.168.100.21	192.168.100.22
主机名	plindb	slindb
数据存储	文件系统	文件系统
db_name	ordb	ordb
sid	ordb	ordb
db_unique_name	pordb	sordb
Service_name	pordb	sordb

实验内容：

1.物理StandBy创建

2.DG几种运行模式切换

3.主备库切换

4.BROKER配置及应用测试

物理StandBy创建

一．在主库上操作

1.将数据库修改为强行记录日志

SQL> ALTER DATABASE FORCE LOGGING;

2.如果数据没有运行在归档模式下，需将数据切换到归档模式

SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archp/';

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog;

SQL>alter databse open;

3.创建pfile

SQL>create pfile=’/oracle/backup/ppfile.ora’;

修改ppfile为：

ordb.__db_cache_size=83886080

ordb.__java_pool_size=4194304

ordb.__large_pool_size=4194304

ordb.__shared_pool_size=71303168

ordb.__streams_pool_size=0

*.audit_file_dest='/oracle/admin/ordb/adump'

*.background_dump_dest='/oracle/admin/ordb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oracle/oradata/ORDB/controlfile/o1_mf_7fc3k0fr_.ctl'

*.core_dump_dest='/oracle/admin/ordb/cdump'

*.db_block_size=8192

*.db_create_file_dest='/oracle/oradata'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/oracle/oradata/sordb','/oracle/oradata/pordb'

*.db_name='ordb'

*.db_recovery_file_dest='/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='pordb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ordbXDB)'

*.fal_server='sordb'

*.fal_client='pordb'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(pordb,sordb)'

*.log_archive_dest_1='LOCATION=/oracle/archp/'

*.log_archive_dest_2='SERVICE=sordb lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=sordb'

log_archive_dest_state_1=ENABLE

log_archive_dest_state_2=ENABLE

*.log_file_name_convert='/oracle/oradata/sordb','/oracle/oradata/pordb'

*.open_cursors=150

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.standby_file_management=AUTO

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/admin/ordb/udump'

4.使用新的pfile 启动数据库并创建新的spfile,使用新的spfile启动数据库

SQL> startup nomount pfile='/oracle/backup/ppfile.ora';

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 79694068 bytes

Database Buffers 83886080 bytes

Redo Buffers 2973696 bytes

SQL> create spfile from pfile='/oracle/backup/ppfile.ora';

File created.

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 79694068 bytes

Database Buffers 83886080 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

5.创建监听和网络服务名

使用netca创建监听

lsnrctl status|start|stop

[oracle@plindb admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/product/10.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 = /oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC=

(SID_NAME=ordb)

(GLOBAL_DBNAME=pordb_DGMGRL)

(ORACLE_HOME=/oracle/product/10.2.0/db_1))

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = plindb)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

使用netmgr创建网络服务名，服务名为pordb

Tnsping pordb

[oracle@plindb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PORDB=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=plindb)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=pordb)

)

SORDB=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=slindb)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=sordb)

)

6.修改数据的/etc/hosts文件的

cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost plindb

192.168.100.21 plindb

192.168.100.22 slindb

7.使用rman备份控制文件，数据文件和归档日志文件

备份控制文件：

[oracle@plindb backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 7 03:14:15 2011

connected to target database: ORDB (DBID=588555936)

RMAN> backup format '/oracle/backup/bk_%U' current controlfile for standby;

Starting backup at 07-DEC-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=141 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including standby control file in backupset

channel ORA_DISK_1: starting piece 1 at 07-DEC-11

channel ORA_DISK_1: finished piece 1 at 07-DEC-11

piece handle=/oracle/backup/bk_0hmtj20r_1_1 tag=TAG20111207T031435 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 07-DEC-11

备份数据文件和归档日志文件：

RMAN> backup format '/oracle/backup/backup_%U' database plus archivelog;

Starting backup at 07-DEC-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=3 recid=2 stamp=768702894

…

input archive log thread=1 sequence=42 recid=40 stamp=769230736

input archive log thread=1 sequence=43 recid=41 stamp=769230886

channel ORA_DISK_1: starting piece 1 at 07-DEC-11

channel ORA_DISK_1: finished piece 1 at 07-DEC-11

piece handle=/oracle/backup/backup_0imtj216_1_1 tag=TAG20111207T031446 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17

Finished backup at 07-DEC-11

Starting backup at 07-DEC-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oracle/oradata/ORDB/datafile/o1_mf_system_7fc3j2qj_.dbf

input datafile fno=00003 name=/oracle/oradata/ORDB/datafile/o1_mf_sysaux_7fc3j2r4_.dbf

input datafile fno=00002 name=/oracle/oradata/ORDB/datafile/o1_mf_undotbs1_7fc3j2v5_.dbf

input datafile fno=00004 name=/oracle/oradata/ORDB/datafile/o1_mf_users_7fc3j2vs_.dbf

channel ORA_DISK_1: starting piece 1 at 07-DEC-11

channel ORA_DISK_1: finished piece 1 at 07-DEC-11

piece handle=/oracle/backup/backup_0jmtj21n_1_1 tag=TAG20111207T031503 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 07-DEC-11

channel ORA_DISK_1: finished piece 1 at 07-DEC-11

piece handle=/oracle/backup/backup_0kmtj22q_1_1 tag=TAG20111207T031503 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 07-DEC-11

Starting backup at 07-DEC-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=44 recid=42 stamp=769230941

channel ORA_DISK_1: starting piece 1 at 07-DEC-11

channel ORA_DISK_1: finished piece 1 at 07-DEC-11

piece handle=/oracle/backup/backup_0lmtj22t_1_1 tag=TAG20111207T031541 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 07-DEC-11

8．将备份文件，pfile,密码文件拷贝到备库的机器上,目录路径要与主库上一致

[oracle@plindb backup]$ scp /oracle/backup/* slindb:/oracle/backup/

oracle@slindb's password:

backup_0imtj216_1_1 100% 195MB 8.9MB/s 00:22

backup_0jmtj21n_1_1 100% 549MB 10.2MB/s 00:54

backup_0kmtj22q_1_1 100% 7040KB 6.9MB/s 00:01

backup_0lmtj22t_1_1 100% 11KB 11.0KB/s 00:00

bk_0hmtj20r_1_1 100% 7008KB 6.8MB/s 00:01

ppfile.ora 100% 1427 1.4KB/s 00:00

scp $ORACLE_HOME/dbs/orapwordb slindb:/oracle/product/10.2.0.1/db_1/dbs

oracle@slindb's password:

orapwordb 100% 1536 8.9MB/s 00:00

二．备库上操作

1. 备库上只安装Oracle软件

注：这里测试是直接复制的虚拟机修改的

2. 创建监听和网络服务名

(与主库步骤相同,不同的是机名和GLOBAL_DBNAME)

使用netca创建监听

lsnrctl status|start|stop

[oracle@plindb admin]$ cat listener.ora

# listener.ora Network Configuration File: /oracle/product/10.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 = /oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC=

(SID_NAME=ordb)

(GLOBAL_DBNAME=sordb_DGMGRL)

(ORACLE_HOME=/oracle/product/10.2.0/db_1))

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = slindb)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

使用netmgr创建网络服务名，服务名为sordb

Tnsping pordb

Tnsping sordb

[oracle@plindb admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PORDB=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=plindb)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=pordb)

)

SORDB=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=slindb)(PORT=1521))

)

(CONNECT_DATA=

(SERVICE_NAME=sordb)

)

3. 修改数据的/etc/hosts文件的

cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost plindb

192.168.100.21 plindb

192.168.100.22 slindb

4.重命名从主库机器上拷贝过来的pfile.ora为spfile.ora,修改为

ordb.__db_cache_size=83886080

ordb.__java_pool_size=4194304

ordb.__large_pool_size=4194304

ordb.__shared_pool_size=71303168

ordb.__streams_pool_size=0

*.audit_file_dest='/oracle/admin/sordb/adump'

*.background_dump_dest='/oracle/admin/sordb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oracle/oradata/SORDB/controlfile/o1_mf_7fwtzvqw_.ctl'

*.core_dump_dest='/oracle/admin/sordb/cdump'

*.db_block_size=8192

*.db_create_file_dest='/oracle/oradata'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='/oracle/oradata/pordb','/oracle/oradata/sordb'

*.db_name='ordb'

*.db_recovery_file_dest='/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.db_unique_name='sordb'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ordbXDB)'

*.fal_server='pordb'

*.fal_client='sordb'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(pordb,sordb)'

*.log_archive_dest_1='LOCATION=/oracle/archp'

*.log_archive_dest_2='SERVICE=pordb lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=pordb'

log_archive_dest_state_1=ENABLE

log_archive_dest_state_2=ENABLE

*.log_file_name_convert='/oracle/oradata/pordb','/oracle/oradata/sordb'

*.open_cursors=150

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=167772160

*.standby_file_management=AUTO

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/admin/sordb/udump'

5.根据pfile中的参数来创建需要的目录

mkdir -p /oracle/admin/sordb/adump

mkdir -p /oracle/admin/sordb/bdump

mkdir -p /oracle/admin/sordb/cdump

mkdir -p /oracle/admin/sordb/udump

mkdir -p /oracle/admin/sordb/pfile

mkdir -p /oracle/admin/sordb/script

mkdir -p /oracle/archp

mkdir -p /oradata/backup

mkdir -p /oracle/flash_recovery_area

6.使用pfile将数据库启动到nomount状态

SQL> startup nomount pfile='/oracle/backup/spfile.ora';

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

7.确认主库和备库间的网络是否正常,在主库和备库上都做如下检查

查看监听是否启动，如果没启动就运行命令启动

$ lsnrctl status

启动命令

$ l snrctl start

查看网络服务名能否连通

$ tnsping pordb

$ tnsping sordb

测试从备库上能否通过网络连接到主库的数据库

sqlplus /nolog

connect sys/oracle@pordb as sysdba

8.使用 rman duplicate 创建备库

[oracle@slindb bin]$ ./rman target sys/oracle@pordb auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 7 03:23:04 2011

connected to target database: ORDB (DBID=588555936)

connected to auxiliary database: ORDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 07-DEC-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script.:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 07-DEC-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /oracle/backup/bk_0hmtj20r_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/oracle/backup/bk_0hmtj20r_1_1 tag=TAG20111207T031435

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output filename=/oracle/oradata/SORDB/controlfile/o1_mf_7fwtzvqw_.ctl

Finished restore at 07-DEC-11

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script.:

{

set newname for tempfile 1 to

"/oracle/oradata/ORDB/datafile/o1_mf_temp_7fc3kjhr_.tmp";

switch clone tempfile all;

set newname for datafile 1 to

"/oracle/oradata/ORDB/datafile/o1_mf_system_7fc3j2qj_.dbf";

set newname for datafile 2 to

"/oracle/oradata/ORDB/datafile/o1_mf_undotbs1_7fc3j2v5_.dbf";

set newname for datafile 3 to

"/oracle/oradata/ORDB/datafile/o1_mf_sysaux_7fc3j2r4_.dbf";

set newname for datafile 4 to

"/oracle/oradata/ORDB/datafile/o1_mf_users_7fc3j2vs_.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /oracle/oradata/ORDB/datafile/o1_mf_temp_7fc3kjhr_.tmp in control file

executing command: SET NEWNAME

Starting restore at 07-DEC-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /oracle/oradata/ORDB/datafile/o1_mf_system_7fc3j2qj_.dbf

restoring datafile 00002 to /oracle/oradata/ORDB/datafile/o1_mf_undotbs1_7fc3j2v5_.dbf

restoring datafile 00003 to /oracle/oradata/ORDB/datafile/o1_mf_sysaux_7fc3j2r4_.dbf

restoring datafile 00004 to /oracle/oradata/ORDB/datafile/o1_mf_users_7fc3j2vs_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /oracle/backup/backup_0jmtj21n_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/oracle/backup/backup_0jmtj21n_1_1 tag=TAG20111207T031503

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 07-DEC-11

contents of Memory Script.:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=8 stamp=769231527 filename=/oracle/oradata/SORDB/datafile/o1_mf_system_7fwv04cx_.dbf

datafile 2 switched to datafile copy

input datafile copy recid=9 stamp=769231527 filename=/oracle/oradata/SORDB/datafile/o1_mf_undotbs1_7fwv04fq_.dbf

datafile 3 switched to datafile copy

input datafile copy recid=10 stamp=769231527 filename=/oracle/oradata/SORDB/datafile/o1_mf_sysaux_7fwv04do_.dbf

datafile 4 switched to datafile copy

input datafile copy recid=11 stamp=769231527 filename=/oracle/oradata/SORDB/datafile/o1_mf_users_7fwv04go_.dbf

Finished Duplicate Db at 07-DEC-11

从输出信息可以看到，备库实际已经启动到mount状态。

注：此处有一个问题，如果你的数据库文件是使用OMF管理的，那么使用rman创建的备库的数据文件名已经与主库的文件名不一样了，此时需将参数文件里的控制文件的路径和名称修改为备库上的实际路径和名称，否则启动数据库会报错。

9.查看和应用归档日志

此时可以查看得到主库的归档日志已经通过rfs进程传输到备库:

ls –al /oracle/archp/

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

RFS

应用从主库传输过来的归档日志:

SQL> recover managed standby database disconnect from session;

Media recovery complete.

此时查看归档日志应用状态，已经变为已应用：

SQL> select process from v$managed_standby;

PROCESS

---------

ARCH

MRP0

RFS

是不是发现多了一个进程：MRP0 这个就是归档日志恢复进程

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 07-DEC-11 07-DEC-11 YES

…

48 07-DEC-11 07-DEC-11 YES

49 07-DEC-11 07-DEC-11 YES

取消归档日志应用：

SQL> recover managed standby database cancel;

Media recovery complete.

10.验证测试

在主库上插入数据，并切换日志

SQL> conn scott/tiger

Connected.

SQL> select * from test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 chengjiu

1008 zhengwu

1009 chengcheng

9 rows selected.

SQL> insert into test values (1010,' chengcheng ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 chengjiu

1008 zhengwu

1009 chengcheng

1010 zhengheng

10rows selected.

SQL> conn / as sysdba

查看当前日志数据：

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 27-OCT-11 02-NOV-11 YES

…

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

52 13-NOV-11 13-NOV-11 NO

50 rows selected.

SQL> alter system switch logfile;

System altered.

切换日志后，再次查看当前日志数据：

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 27-OCT-11 02-NOV-11 YES

…

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

52 13-NOV-11 13-NOV-11 NO

53 13-NOV-11 13-NOV-11 NO

在备库上查看日志应用：

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

16 03-NOV-11 04-NOV-11 NO

…

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 NO

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 NO

52 13-NOV-11 13-NOV-11 YES

53 13-NOV-11 13-NOV-11 YES

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from scott.test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 weiqi

1006 chengjiu

1008 zhengwu

1009 chengcheng

1010 zhengheng

102 rows selected.

能查询到刚刚插入的数据，在备库上被成功应用。

DG模式切换

Oracle DG支持运行三种模式：

最大保护 maximize protection

最大可用 maximize availability

最佳性能 maximize performance

当physical standby建立后，默认为最佳性能模式：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

如果需要切换到最大可用或者最大保护模式，需要做以下改动：

1.需要对一些参数进行修改

2.需要确定standby logfile是否添加，如没有需添加standby logfile,添加的数量为：（redo log数量+1）X instance数量,最佳性能模式下使用lgwr同步传送日志的话，也必须使用standby logfile,如果使用arch传送日志就不需要。

添加standby logfile

主库：

SQL>alter database add standby logfile thread 1 size 50m;

备库：

SQL>alter database add standby logfile thread 1 size 50m;

查看添加的standby logfile：

SQL>select * from v$logfile;

SQL>select * from v$standby_log;

删除standby logfile：

SQL>alter database drop standby logfile group 8;

切换命令：

SQL>alter database set standby database to maximize protection;

SQL>alter database set standby database to maximize availability;

SQL>alter database set standby database to maximize performance;

从最佳性能模式切换到最在可用：

查看当前主库和备库的保护模式和保护级别

主库：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

备库：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

将主库启动到mount状，然后修改主库相关参数：

SQL>Shutdown immediate

SQL>Startup mount

SQL>Alter system set log_archive_dest_2='SERVICE=sordb lgwr sync affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) db_unique_name=sordb'

SQL>alter database set standby database to maximize availability;

SQL>alter database open;

再次查看主库和备库的保护模式和保护级别：

主库：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

备库：

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

切换成功：可以看到保护模式和保护级别都是变成最大可用模式了。

主库和备库切换(Switchover)

1.在主库上操作

查看数据库角色和切换状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY TO STANDBY

如果状态为：to standby 表示主库当前支持切换为standby,使用下面语句进行切换：

alter database commit to switchover to physical standby

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

如果状态为：session active表示当前有活动的会话，需先将活动的会话kill掉或者使用下面语句切换：

alter database commit to switchover to physical standby with session shutdown;

重启数据库到moumt

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 109053520 bytes

Database Buffers 171966464 bytes

Redo Buffers 2973696 bytes

Database mounted.

再次查看切换状态，状态变为to primary：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

2.备库上操作

查看切换状态：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

正常情况为to primary,如果状态为swithover pending：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

SWITCHOVER PENDING

则需要先进行一下恢复再切换：

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

Database altered

再次查看切换状态，状态变为to primary：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

查看数据库角色和切换状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY TO PRIMARY

将备库切换成主库：

Database altered.

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 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

再次查看数据库角色和切换状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

数据库角色和切换状态都发生改变，分别为：primary,sessions active

3.在新的库（原主库）应用恢复归档日志

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

Database altered

4.验证

在新的主库上插入数据，并切换日志

SQL> conn scott/tiger

Connected.

SQL> select * from test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 weiqi

1006 chengjiu

1008 zhengwu

1009 chengcheng

1010 zhengheng

1011 zhangrang

11 rows selected.

SQL> insert into test values (1012,'wangfie');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 weiqi

1006 chengjiu

1008 zhengwu

1009 chengcheng

1010 zhengheng

1011 zhangrang

ID NAME

---------- ------------------------------

1012 wangfie

12 rows selected.

SQL> conn / as sysdba

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 27-OCT-11 02-NOV-11 YES

….

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

52 13-NOV-11 13-NOV-11 NO

50 rows selected.

SQL> alter system switch logfile;

System altered.

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

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 27-OCT-11 02-NOV-11 YES

…

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

52 13-NOV-11 13-NOV-11 NO

53 13-NOV-11 13-NOV-11 NO

在新的备库上查看日志应用：

SEQUENCE# FIRST_TIM NEXT_TIME APP

---------- --------- --------- ---

3 03-NOV-11 04-NOV-11 NO

…

49 13-NOV-11 13-NOV-11 YES

50 13-NOV-11 13-NOV-11 NO

50 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 YES

51 13-NOV-11 13-NOV-11 NO

52 13-NOV-11 13-NOV-11 YES

53 13-NOV-11 13-NOV-11 YES

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from scott.test;

ID NAME

---------- ------------------------------

1001 zhangyang

1002 zhangshan

1003 lishi

1004 wangwu

1005 zhaoliu

1006 weiqi

1006 chengjiu

1008 zhengwu

1009 chengcheng

1010 zhengheng

1011 zhangrang

1012 wangfieqi

12 rows selected.

能查询到刚刚插入的数据，主备库之间的switchover方式切换成功。

DG broker配置及应用测试

一．配置broker的先决条件

1.数据库版本：主库和备库版本必须10g r2 或者更高，企业版

2.参数文件：必须使用spfile

SQL> show parameter spfile

NAME TYPE VALUE

------------ ----------- ---------------

spfile string /oracle/product/10.2.0/db_1/db s/spfileordb.ora

3.确定主备库的compatibe值相同

4.主备库dg_broker_start的值必须为：true

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> show parameter dg_broker

NAME TYPE VALUE

--------------------------- -------- ------------------------------

dg_broker_config_file1 string /oracle/product/10.2.0/db_1/db s/dr1pordb.dat

dg_broker_config_file2 string /oracle/product/10.2.0/db_1/db s/dr2pordb.dat

dg_broker_start boolean TRUE

5.必须在监听里设置global_name的值

主库：

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=ordb)

(GLOBAL_DBNAME=pordb_DGMGRL)

(ORACLE_HOME=/oracle/product/10.2.0/db_1)))

备库：

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=ordb)

(GLOBAL_DBNAME=sordb_DGMGRL)

(ORACLE_HOME=/oracle/product/10.2.0/db_1)))

6.在主备库开启闪回数据库功能

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE FLASHBACK ON;

SQL>ALTER DATABASE OPEN;

注：如果是rac模式，需要在每个节点上设置dg_broker_config_filen参数

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DG/DIRECTORY/DR1.DAT' SCOPE=BOTH;

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+DG/DIRECTORY/DR2.DAT' SCOPE=BOTH;

如果使用了非默认端口(1521),必须设置local_lisener以便所有成员都能访问

二．broker配置

在从库上配置

1.运行命令, 连接到主库：

dgmgrl

connect sys/oracle@pordb

2.创建broker配置

[oracle@slindb archp]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@pordb

Connected.

DGMGRL> create configuration 'pordbsoulution' as primary database is 'pordb' connect identifier is 'pordb';

Configuration "pordbsoulution" created with primary database "pordb"

DGMGRL> show configuration

Configuration

Name: pordbsoulution

Enabled: NO

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

pordb - Primary database

Current status for "pordbsoulution":

DISABLED

添加备库到broker配置:

DGMGRL> add database 'sordb' as connect identifier is sordb maintained as physical;

Database "sordb" added

DGMGRL> show configuration

Configuration

Name: pordbsoulution

Enabled: NO

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

pordb - Primary database

sordb - Physical standby database

Current status for "pordbsoulution":

DISABLED

3.使broker配置生效

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration

Name: pordbsoulution

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

pordb - Primary database

sordb - Physical standby database

Current status for "pordbsoulution":

SUCCESS

可以看到当前状态改变了

设置数据库状态模式:

设定 FastStartFailoverTarget 值.

DGMGRL> edit database 'pordb' set property 'logxptmode'='sync';

Property "logxptmode" updated

DGMGRL> edit database 'sordb' set property 'logxptmode'='sync';

Property "logxptmode" updated

使 Fast-Start Failover 生效

DGMGRL> ENABLE FAST_START FAILOVER;

Enabled.

开启 Observer

DGMGRL> start observer

注：执行开启 Observer后，界面不会自动退出，如果要执行其它dgmgrl命令需重新开一个窗口

重新开一个窗口，进入dgmgrl,查看fast-start failover 配置

DGMGRL> show configuration;

DGMGRL> show configuration verbose;

4.验证fast-start failover 配置

主库：

SQL> col FS_FAILOVER_OBSERVER_HOST for a30

SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold

from v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD

------- ------------------------------ ---------------------

YES slindb 30

备库：

SQL> col FS_FAILOVER_OBSERVER_HOST for a30

SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold

from v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD

------- ------------------------------ ---------------------

YES slindb 30

修改fsfailoverthreshold值为什么120，默认为30

DGMGRL> edit configuration set property faststartfailoverthreshold=120;

Property "faststartfailoverthreshold" updated

测试broker

查看主库和备库配置信息和状态:

DGMGRL> show database verbose pordb;

Database

Name: pordb

Role: PRIMARY

Enabled: YES

Intended State: ONLINE

Instance(s):

ordb

Properties:

InitialConnectIdentifier = 'pordb'

LogXptMode = 'sync'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '2'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/oracle/oradata/sordb, /oracle/oradata/pordb'

LogFileNameConvert = '/oracle/oradata/sordb, /oracle/oradata/pordb'

FastStartFailoverTarget = 'sordb'

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'plindb'

SidName = 'ordb'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=plindb)(PORT=1521))'

StandbyArchiveLocation = '/oracle/archp/'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "pordb":

SUCCESS

DGMGRL> show database verbose sordb;

Database

Name: sordb

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

ordb

Properties:

InitialConnectIdentifier = 'sordb'

LogXptMode = 'sync'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '2'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/oracle/oradata/pordb, /oracle/oradata/sordb'

LogFileNameConvert = '/oracle/oradata/pordb, /oracle/oradata/sordb'

FastStartFailoverTarget = 'pordb'

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'slindb'

SidName = 'ordb'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=slindb)(PORT=1521))'

StandbyArchiveLocation = '/oracle/archp/'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "sordb":

SUCCESS

三．验证和测试Broker

1.主备切换测试：switchover

查看主库和备库状态和角色

主库：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

备库：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY SESSIONS ACTIVE

[oracle@slindb ~]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.1.0 - Production

Welcome to DGMGRL, type "help" for information.

DGMGRL>

DGMGRL> connect sys/oracle@pordb

Connected.

DGMGRL> show configuration

Configuration

Name: pordbsoulution

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: ENABLED

Databases:

pordb - Primary database

sordb - Physical standby database

- Fast-Start Failover target

Current status for "pordbsoulution":

SUCCESS

将主库切换到sordb上，切换时同时观察主库，务库，dgmgrl的告警日志信息：

tail -f /oracle/admin/pordb/bdump/alter*.log

tail -f /oracle/admin/sordb/bdump/alter*.log

tail -f /oracle/admin/sordb/bdump/dr*.log

形如切换：

DGMGRL> switchover to sordb;

Performing switchover NOW, please wait...

Operation requires shutdown of instance "ordb" on database "pordb"

Shutting down instance "ordb"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "ordb" on database "sordb"

Shutting down instance "ordb"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "ordb" on database "pordb"

Starting instance "ordb"...

ORACLE instance started.

Database mounted.

Operation requires startup of instance "ordb" on database "sordb"

Starting instance "ordb"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "sordb"

切换成功后，查看新的主库和备库的状态和角色：

SQL> select database_role,switchover_status from v$database;

SQL> select status from v$instance;

DGMGRL> show configuration;

Configuration

Name: pordbsoulution

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: ENABLED

Databases:

pordb - Primary database

sordb - Physical standby database

- Fast-Start Failover target

Current status for "pordbsoulution":

SUCCESS

2.failover测试

手动测试：

DGMGRL> failover to sordb;

Performing failover NOW. Please wait...

Operation requires shutdown of instance "sordb " on database

"sordb".

Shutting down instance "sordb"...

database not mounted

ORACLE instance shut down.

Operation requires startup of instance "pdb" on database "sordb".

Starting instance "sordb"...

ORACLE instance started.

Database mounted.

Failover succeeded. New primary is "sordb"

切换成功后，主库自动变为sordb

自动测试：

将主库shutdown abort

SQL> shutdown abort

ORACLE instance shut down.

此时在observer控制台上看到：

DGMGRL> start observer

Observer started

22:26:38.10 Friday, December 09, 2011

Initiating fast-start failover to database "sordb"...

Performing failover NOW, please wait...

Failover succeeded, new primary is "sordb"

22:26:52.27 Friday, December 09, 2011

表示在主库pordb出现故障时，自动切换，新的主库为sordb

查看failover后的新主库sordb角色和状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

------------

OPEN

切换成功。

如果此时再将pordb启动，启动后会自动转化为备库：

此时在observer控制台上看到：

22:31:04.46 Friday, December 09, 2011

Initiating reinstatement for database "pordb"...

Reinstating database "pordb", please wait...

Operation requires shutdown of instance "ordb" on database "pordb"

Shutting down instance "ordb"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "ordb" on database "pordb"

Starting instance "ordb"...

ORACLE instance started.

Database mounted.

Continuing to reinstate database "pordb" ...

Reinstatement of database "pordb" succeeded

22:31:59.61 Friday, December 09, 2011

查看pordb角色和状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

------------

MOUNTED

查看sordb角色和状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

SQL> select status from v$instance;

STATUS

------------

OPEN

查看主库和备库归档日志及应用信息：

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

总结：在实验中还是遇到一些问题，不过最后都一一的顺利解决，最容易遇到的以下问题：

1. ORA-01031: insufficient privileges

PING[ARC0]: Heartbeat failed to connect to standby 'sordb'. Error is 1031.

这各情况有两个可能，一个是密码文件没有拷到备库上，另一个原因是TNS设置不正确或者监听有问题，重新设置这两个地方就可以解决。

2.备库接收不到备库的日志

这个主要还是需要看告警日志，主要原因还是网络和权限的问题，当然也有可能是由其它原因导致网络和权限问题，比如备库的环境变量设置，我就在实验时不小心备库sid设置的问题引起权限问题，导致备库接收不到日志

3. 数据库文件是使用OMF管理的，那么使用rman创建的备库的数据文件名已经与主库的文件名不一样了，此时需将参数文件里的控制文件的路径和名称修改为备库上的实际路径和名称，否则启动数据库会报错。

ITPub博客

Linux 下创建Oracle 10g DG和Broker配置

最新文章