Linux系统下安装oracle _11g_R2 dataguard-physical standby

来源：互联网发布：女生发型知乎编辑：程序博客网时间：2024/05/01 22:12

Linux系统下安装oracle _11g_R2 dataguard

1 安装前准备工作

1.1 primary状态

主库：安装数据库软件并创建数据库

备库：只安装数据库软件不创建数据库

注：安装oracle软件时oracle相关的环境变量及目录一定与下面的一致。否则需要在自己理解的基础上进行相应改动。

1.2 primary配置信息

primary配置列表：

IP:10.0.7.248(依据现场灵活改动)

oracle_sid:bocop

db_name:bocodb

db_unique_name:bocop

.bash_profile信息：

export ORACLE_BASE=/opt/oracle/app

export ORACLE_HOME=$ORACLE_BASE/product/11g

export ORACLE_SID=bocop

export PATH=$PATH:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:ORACLE_HOME/lib

export NLS_LANG=american_america.AL32UTF8

export ORA_NLS10=$ORACLE_HOME/nls/data

standby配置列表：

IP:10.0.7.249(依据现场灵活改动)

oracle_sid:bocos

db_name:bocodb

db_unique_name:bocos

.bash_profile信息：

export ORACLE_BASE=/opt/oracle/app

export ORACLE_HOME=$ORACLE_BASE/product/11g

export ORACLE_SID=bocos

export PATH=$PATH:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:ORACLE_HOME/lib

export NLS_LANG=american_america.AL32UTF8

export ORA_NLS10=$ORACLE_HOME/nls/data

1.3 创建相关目录

primary和standby都要创建

[oracle@db02 dbs]$mkdir –p /opt/oracle/app/admin/bocodb/adump

[oracle@db02 dbs]$mkdir –p /opt/oracle/app/flash_recovery_area

[oracle@db02 dbs]$mkdir –p /opt/oradata4/archivelog

2 Primary数据库配置相关操作

2.1 确认primary数据库为归档模式

1. 执行archivelog list命令查看

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/oracle/app/oradata/bocodb

Oldest online log sequence 43

Next log sequence to archive 45

Current log sequence 45

如果为非归档则改为归档模式（操作如下：）

1.1关闭数据库

SQL>Shutdown immediate;

1.2将数据库启动到mount状态

SQL>Startup mount;

1.3更改归档模式

SQL>Alter database archive log;

1.4打开数据库

SQL>alter database open;

2.2 将primary置于force_logging模式

SQL>Alter database force logging;

2.3 配置primary数据库的初始化参数

3.1通过spfile生成pfile

SQL>create pfile from spfile;

3.2修改pfile文件

[oracle@db01 oracle]$ cd $ORACLE_HOME

[oracle@db01 dbhome_1]$ cd dbs

[oracle@db01 dbs]$ vi initbocop.ora

添加或修改如下参数：

*.db_name='bocodb'

*.db_unique_name='bocop'

*.fal_client='bocop'

*.fal_server='bocos'

*.instance_name='bocop'

*.log_archive_config='DG_CONFIG=(bocop,bocos)'

*.log_archive_dest_1='LOCATION=/opt/oradata4/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=bocop'

*.log_archive_dest_2='SERVICE=bocos lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=bocos'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='arch_%r_%t_%s.arc'

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

3.3通过pfile重建spfile

SQL>shutdown immediate;

SQL>create spfile from pfile;

2.4 创建standby数据库控制文件

1、启动数据库

SQL>startup;

2、创建控制文件

SQL>alter database create standby controlfile as ‘/opt/standbycontrolfile.ctl’

2.5 创建standby logfile

SQL>alter database add standby logfile group 4 '/opt/oracle/app/oradata/bocodb/standby_redo04.log' size 50M;

SQL>alter database add standby logfile group 5 '/opt/oracle/app/oradata/bocodb/standby_redo05.log' size 50M;

SQL>alter database add standby logfile group 6 '/opt/oracle/app/oradata/bocodb/standby_redo06.log' size 50M;

SQL>alter database add standby logfile group 7 '/opt/oracle/app/oradata/bocodb/standby_redo07.log' size 50M;

2.6 创建口令文件（如果已经存在可以删除后重建）

[oracle@db01 dbs] orapwd file=’ /opt/oracle/app/product/11g/dbs/orapwbocop’password=boco entries=30

2.7 复制相关文件到standby

Stranby上没有的目录自己创建

包括数据文件、standby控制文件、日志文件、参数文件、口令文件

在primary数据库操作

1、 Shutdown immediate;

2、 Scp /opt/oracle/app/oradata/bocodb/*.log 10.0.7.249:/opt/oracle/app/oradata/bocodb/

3、 Scp /opt/oracle/app/oradata/bocodb/*.dbf 10.0.7.249:/opt/oracle/app/oradata/bocodb/

4、 scp /opt/standbycontrolfile.ctl 10.0.7.249:/opt/oracle/app/oradata/bocodb/control01.ctl

5、 scp /opt/standbycontrolfile.ctl 10.0.7.249:/opt/oracle/app/flash_recovery_area/bocodb/control02.ctl

6、 scp /opt/oracle/app/product/11g/dbs/orapwbocop 10.0.7.249:/opt/oracle/app/product/11g/dbs/orapwbocos

7、 scp /opt/oracle/app/product/11g/dbs/initbocop.ora 10.0.7.249:/opt/oracle/app/product/11g/dbs/initbocos.ora

2.8 配置监听程序及tnsnames.ora

[oracle@db01 dbs]$ cd /opt/oracle/app/product/11g/network/admin/

[oracle@db01 admin]$ vi listener.ora

SID_LIST_LISTENER

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = bocodb)

(ORACLE_HOME = /opt/oracle/app/product/11g)

(SID_NAME = bocodb)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /opt/oracle/app

[oracle@db01 admin]$ vi tnsnames.ora

BOCOS =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = bocos)

(SERVICE = DEDICATED)

)

BOCOP =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = bocop)

(SERVICE = DEDICATED)

)

3 standby数据库配置相关操作

3.1 修改参数文件

[oracle@db02 dbs]$cd /opt/oracle/app/product/11g/dbs

[oracle@db02 dbs]$vi initbocos.ora

在文件中添加或修改如下内容：

*.db_name='bocodb'

*.db_unique_name='bocos'

*.fal_client='bocos'

*.fal_server='bocop'

*.instance_name='bocos'

*.log_archive_config='DG_CONFIG=(bocop,bocos)'

*.log_archive_dest_1='LOCATION=/opt/oradata4/archivelog valid_for=(all_logfiles,all_roles)db_unique_name=bocos'

*.log_archive_dest_2='SERVICE=bocop lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=bocop'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='arch_%r_%t_%s.arc'

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

3.2 通过pfile创建spfile

SQL>create spfile from pfile;

3.3 修改listener.ora和tnsnames.ora文件

[oracle@db02 admin]$ cd /opt/oracle/app/product/11g/network/admin

[oracle@db02 admin]$vi listener.ora

SID_LIST_LISTENER

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = bocodb)

(ORACLE_HOME = /opt/oracle/app/product/11g)

(SID_NAME = bocodb)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /opt/oracle/app

[oracle@db02 admin]$vi tnsnames.ora

BOCOS =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = bocos)

(SERVICE = DEDICATED)

)

BOCOP =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = bocop)

(SERVICE = DEDICATED)

)

3.4 启动standby数据库到mount状态并置于standby状态

SQL>startup mount;

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

4 验证DATAGUARD是否配置成功

4.1 在primary端监控alert文件

SQL> show parameter background_dump_dest

NAME TYPE VALUE

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

background_dump_dest string /opt/oracle/app/diag/rdbms/boc

op/bocop/trace

[oracle@db01]$cd /opt/oracle/app/diag/rdbms/bocop/bocop/trace

[oracle@db01 trace]$ tail -f alert_bocop.log

4.2 新开一个session进行日志切换

SQL>alter system switch logfile;

观察alert中是否有日志同步（没有报错基本就没有问题）

4.3 验证日志同步状态

1、在primary端查询：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

2、在standby端执行同样的查询：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

证明同步已生效。

5 启停standby操作

5.1 停standby操作

1、在primary端修改参数

SQL>alter system set log_archive_dest_state_2=defer;

2、在standby端停止standby状态

SQL> alter database recover managed standby database cancel;

3、至此可以打开standby数据库

SQL>alter database open;

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

5.2 启standby操作

1、在standby端开启standby状态

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

Database altered.

2、在primary端修改参数

SQL>alter system set log_archive_dest_state_2=enable;

6 Switchover操作（主备切换）

6.1 Primary上操作

1、检查是否支持switchover

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

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE TO STANDBY PRIMARY

显示为to_standby为正常状态，可以进行切换。如果显示为session active证明primary库上还有其他session链接，此时可以查看v$session视图看看有哪些session在线，手工杀掉session，杀掉之后可以进行切换。

2、进行switchover操作

首先找出连在primary数据库的session

SQL> select SID, PROCESS, PROGRAM from v$session where type = 'USER' and SID <> (select DISTINCT SID from v$mystat);

将相应的进程kill掉

kill -9 process

执行switchover命令

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

3、关闭数据库

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

3、重新启动到mount状态

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size 2213936 bytes

Variable Size 3556771792 bytes

Database Buffers 3087007744 bytes

Redo Buffers 34922496 bytes

Database mounted.

4、验证数据库状态

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

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED TO PRIMARY PHYSICAL STANDBY

6.2 原standby上操作

1、验证可否切换

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

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED TO PRIMARY PHYSICAL STANDBY

2、首先找出连在primary数据库的session

SQL> select SID, PROCESS, PROGRAM from v$session where type = 'USER' and SID <> (select DISTINCT SID from v$mystat);

将相应的进程kill掉

kill -9 process

执行switchover命令

SQL>alter database commit to switchover to primary;

3、启动数据库

SQL> alter database open;

Database altered.

6.3 在原primary数据库上操作

1、启动redo应用

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

0 0